wizard doesn't ask for table relationship

[chuck gregory]

NeoPa, in the klutzy old database, there are nine tables, almost all of them linked by auto id fields, and the system works.

With the new tables, I can link two by auto id, and it works. If I add a third table, it doesn't.

When I delete the tables and use wizard to create them, it only asks me if it is a one-to-many or many-to-one; it doesn't allow for a one-to-one.

When I re-create the tables in design view, I understand that the one I click and drag becomes the "one" table and the table to which I drag becomes the "one."

I have tried it in both directions with the third table-- with both of the other tables--, and the result is the same: Connectng the third table results in either a form on the screen that only shows the header, or a form that shows one record only which cannot be data-entered.

chuck gregory

[NeoPa]

Chuck,

Sorry to take so long to respond. I was expecting to have to get into a lot of detail here at this point. As it turns out though, when I read your post more carefully, it seems there is still a confusion to iron out before we go anywhere.

With the new tables, I can link two by auto id, and it works. If I add a third table, it doesn't.

You say you have tables linked by Auto ID. This is after I tried to clarify what can and can't work, so I can only assume you understand what you're saying and you really mean that.

My problem is that this doesn't make sense. It could be that this is actually what you have. If so then this could be the source of the problem (although you claim to have got it to work between two tables).

To try to sort this out then :
Do you have ...
A) Multiple tables, each with fields designed within their table as AutoNumber? So both sides of the join are AutoNumber fields?

Or

B) Multiple tables, where one has an AutoNumber field and where all the others link to this field, but from fields designed as numbers (NOT AutoNumber)?

If A then you have a design problem.

[chuck gregory]

Sorry to take so long to get back to you, NeoPa; busy, busy. . .

In response to your question, I do indeed have: Multiple tables, each with fields designed within their table as AutoNumber, so both sides of the join are AutoNumber fields.

Now, here's something else I just found out today by making two new tiny tables joined by Autonumber fields and trying them in the existing form: The form will accept data entry, but the entered data is not shown when the form is re-opened.

I then used the wizard to make a whole new form, and everything works the way it's supposed to. I can enter the data, close the form and then re-open it to view and work with the entered data.

Does this provide you with any hints?

chuck g

[NeoPa]

Chuck,

I suspect that the system fits together ok at the start, but as time goes on, and the next AutoNumber assigned for one table no longer matches the next assigned for another, they will point to (between) records with no logical connection.

Each separate table maintains its own idea of where the next number is coming from. Even the fact that new numbers are usually sequential in Jet should never be taken for granted. It is not guaranteed. Assuming that two records, from two separate tables, will create matching (and linkable) numbers in the key is highly unreliable (although I can see, in the circumstances, that it may appear to work in many scenarios).

If you think the process through you will probably see where I'm coming from.

PS. No problem with delays. I have them myself and we have plenty to keep us busy when threads lay idle for a while. Think of it more as a respite ;)

[chuck gregory]

Chuck,

I suspect that the system fits together ok at the start, but as time goes on, and the next AutoNumber assigned for one table no longer matches the next assigned for another, they will point to (between) records with no logical connection.

Each separate table maintains its own idea of where the next number is coming from. Even the fact that new numbers are usually sequential in Jet should never be taken for granted. It is not guaranteed. Assuming that two records, from two separate tables, will create matching (and linkable) numbers in the key is highly unreliable (although I can see, in the circumstances, that it may appear to work in many scenarios).

. . .

NeoPa,

As I mentioned before, I created two tables, a query and a form which worked properly, showing all data previously entered. Once I had that working, I added mroe fields to the tables, the query and the form, expecting that the system would still work, overcoming the characteristics you describe above.

It doesn't. Somewhere along the way, even with all new data, etc., the form just opens up blank. I can add data, and it will appear when I view the tables, but when I open the form, it is blank and shows form 1 of 1 down at the bottom of the screen. What do you think?

chuck

[NeoPa]

Do I understand that you still have tables linked together by Autonumber fields (on both sides), but you think this is working reliably (except for the problems you're experiencing of course)?

[chuck gregory]

That is correct, NeoPa. I understand from the help section that lilnked fields have to be unique, and autonumbers seem to be the only unique field in each table.

Now, here's what I did for this past week: I created two three-field tables and linked them by autonumber. I created a form using those fields. I filled out four forms and was able to close the form and re-open it to find all four filled forms accessible and useable (I could enter and revise entered data).

I then expanded the tables, adding more fields, revising the query on which the form was based. I was able to continue modifying and creating new data in the form as I went along.

I increased the fields in the form as I enlarged the tables, of course, and eventually had the form I needed, and I was able to use it as fully intended.

Then I deleted from both tables the information from the four forms I'd been working with. I then entered new data in a blank form.

When I closed the program and re-opened it, it failed to show the data that I had entered!

The two tables contain the information I had just entered.

It seems there was something in the trial data that held everything together so it worked. What's your guess? And of course, is there a fix?

chuck gregory

[NeoPa]

That is correct, NeoPa. I understand from the help section that lilnked fields have to be unique, and autonumbers seem to be the only unique field in each table.

Chuck, I'm not sure how I should be saying this, but having AutoNumber fields in both tables that you link together is what I've been trying to warn you against from the start.

From my perspective it is not a clever idea and will lead you into trouble (for the reasons I thought I'd explained clearly in earlier posts).

While it is certainly within your rights to disagree with me on this, I can't be expected to explain how to do things safely and correctly in a scenario I have already stated to be unsafe and a bad design.

If you don't see why I am saying what I am, talk to someone who has some database understanding (This is general database theory rather than simply an Access issue) and see what they tell you. I'm sure they will recognise the sense in what I'm saying.

[Stewart Ross]

Hi Chuck. NeoPa has given you a lot of pointers in his posts about how to do things properly. Database table links don't happen by magic - you have to set them up and understand how they carry through.

One-to-many relations between tables involve links between the primary key of the one-side table and the related secondary key field in the many-side table. An autonumber field as a primary key on the one side of a table cannot link to an autonumber field used as a primary key on a many-side table. It is not may not, should not, or could not - it cannot work that way. The norm, already mentioned by NeoPa in his posts, is that the secondary table has a long integer field in which to store the value of the autonumber primary key from the one-side table.

When you use forms and subforms the parent-child links you specify for the subform automatically take care of linking these fields and inserting the value of the one-side PK into the secondary key field of the many side record.

In any event, it is clear to me from reading your posts that you don't yet understand one-to-many relationships between tables, and how they are implemented. Nothing in an SQL database happens by chance, or at the wave of a hand; the DB does not lose data you entered, if you define relationships properly and implement them using the forms and other facilities provided by Access for that purpose.

You really would benefit from reading our article on Database normalisation and table structures in the HowTo section, and then re-reading the advice NeoPa has already given to you in the posts above.

-Stewart

[chuck gregory]

Thank you, Stewart.

I have a question: If Table I is page one of my form and Table II is page two, what sort of a relationship do I need between them? In Relationships view, how would I create that relationship once the tables are displayed?

I do not do this for a living, so I do not understand how to apply the material in the How To section-- there is nothing in there that corresponds to my page one-page two situation as far as I can see.

chuck gregory

[Stewart Ross]

Hi Chuck. A form divided into pages has nothing to do with tables - unless by pages you mean separate tab controls for displaying different tables (as subforms within the main form). I am cautious here because you can have one form divided into pages, in which case logically you are simply viewing different parts of the same table, or you can have multiple independent tabbed pages shown on a form, in which case you can be showing entirely different tables depending on the purpose of the tab.

The HowTo article is absolutely crucial to understanding how relational databases are designed. Without such an understanding it is difficult, if not impossible, to set up any meaningful set of tables which model a real-world situation.

I recognise that you are not a database specialist, and the best advice I can offer is that to do even the simplest task with a relational database you should read a good introductory book on Access and build some of the sample applications. Otherwise, in all honesty, Access is not going to do a job for you at all.

To give you an analogy, trying to set up a database when you don't know the basics is like trying to set up an accounting package from scratch without having the first idea about sales, budgets, expenditure, turnover, working capital, revenue, sales taxation and so on. Enthusiasm and willpower simply will not overcome a lack of understanding of the basics.

-Stewart

[NeoPa]

When you say Page I & Page II, are you referring to tabs (properly called pages) on your form?

[chuck gregory]

Hello, NeoPa,

The form is designed as 7 1/2" x 20" in Access. When it gets printed, it comes out of the printer as a piece of paper with printing on both sides. Table I is the top half of the form (the front of the page), and Table II is the bottom half (the back).

chuck gregory

[NeoPa]

In that case the two pages are not separate at all. Logically they are both part of the same form.

As Stewart & I have both indicated in earlier posts, ONE of your tables should have an AutoNumber Primary Key and all the others should have Long Integer values that exactly match the related AutoNumber value in the matching record.

With this design, each extra part of the data should be linked in a one-to-one relationship with the principle table.

The form would then be built upon (bound to) a query which links the multiple tables to the principle one with INNER JOINs.

This isn't something I do a lot, so I can't guarantee it will support more than two tables linked and still be updatable. That is something you'll need to play with yourself. Start off with two, then add more until it stops being updatable (if it does).

[chuck gregory]

Well, NeoPa, I am a slow learner, but you're getting through to me!

How do I create a field in the second table which will match the Auto Number in the first table? I don't see how it can be done, aside from reading that number in the first table, and I don't know that first number could be read.

chuck gregory