wizard doesn't ask for table relationship

[chuck gregory]

I'm working with the 2002 (10.6771.6839) SP3 Access program. I originally (earn while I learn) developed a 250-field table.

To streamline it, I saved the table under six different names, then deleted from each of the six different sections of fields. This gave me six tables which included all the fields of the original elephant and allowed me to add in a few extra fields where needed.

I then created new queries, using the six new tables. However, unlike previous creation, the wizard did not ask what the relationship was between the tables.

I used two existing forms, and have no trouble entering data, but although the entered data can be found by opening up the tables, when I open the forms, previously entered data is not there.

Am I correct in suspecting that there's a relationship between the lack of the wizard's non-question and the problem? And of course, how can I fix it?

cg

[NeoPa]

Hard to follow exactly what you're describing Chuck, but a few things you should check :

1. Are all the tables sharing the same Primary Key (They should be)?
2. Have you defined the logical connection between all the tables in the Relationship window?
3. In truth, although it's not strictly necessary, I would consider treating one of the sub-tables as the principal table, such that it links to all the others, rather than all linking to all individually.

[Stewart Ross]

...I used two existing forms, and have no trouble entering data, but although the entered data can be found by opening up the tables, when I open the forms, previously entered data is not there. ...

Are the two forms set to Allow Additions but not Allow Edits, by any chance? Check in the form's properties, and if you open the forms from a command button check that they are not being opened in Add mode, i.e.

DoCmd.OpenForm "Name of Form", , , acFormAdd

instead of

DoCmd.OpenForm "Name of Form", , , acFormEdit

If there were problems with relationships I doubt you would be able to add new data at all.

-Stewart

[chuck gregory]

Stewart, the forms allow for both addition and edit.

NeoPa, I have all the tables linked by auto id to just one table. Is there some other relationship that they should have?

cg

[NeoPa]

No. If you mean what I think you mean (One table has an Autonumber field and all the others have numeric fields that are Foreign keys to that value in the principal table. They would also be PKs in their own tables of course, but not Autonumbers) then that's pretty well as I would recommend.

[chuck gregory]

It gets a little stranger, NeoPa--

I generated two new tables, all new titles, no relation to the tables I'm having the problem with, and then used the wizard to create a query (using fields from both of the tables).

Again, the wizard didn't ask if there was any relationship between the two tables.

Again, when I enter data, the data show up in the tables, but not on the forms when I open them.

Any insights?

cg

[chuck gregory]

NeoPa, here's an update--

I've found out that if I make a whole new form and new tables using the wizard, it will ask me about the relationship between tables, but not if I use imported forms or pieces from imported tables.. If you hve any insight as to how this would contribute to the problem of entered data failing to be retrieved when the form is opened up, I'd appreciate it.

Meanwhile, I'm solving the problem by generating all new forms...

cg

[NeoPa]

Not an area of great expertise for me I'm afraid Chuck (Form Wizard - I do them all manually now).

I will have a bit of a dig and see if I can notice something though.

[chuck gregory]

Thanks, NeoPa. It will be nice to know if's not simply paranoia.

[NeoPa]

I have been trying to fit this in but time has been tight recently :(

I'm out this evening too, so won't get much Byting time, but I'll see if I can give it some attention later in the week.

[NeoPa]

Seems like just paranoia I'm afraid :D

I think the automatic adding of the JOINs by the query wizard depends on three things (fundamentally) :

1. The Relationships defined (Relationship Pane).
2. The names of the fields.
3. The compatibility of the fields.

If the relationship has already been defined it should default in the query wizard. Otherwise, if the name is the same and the types are either the same or compatible, then it should also add the link in by default.

Do your tables match these criteria?

[chuck gregory]

they meet all three criteria, NeoPa-- they're all with the same title in the field, they're all AUto-id numbers, and they're all long integer.

I've given up with the imported tables and have been making new tables. I've done three-field tables to keep it simple-- auto-id and two 255 character text fields.

It works fine when I link only two tables. When I generate a third table and am asked for the relationship, neither linking the third table as many-to-one with the first or the second results in the form being useable.

Might as well give up on this one! At least I can use the klutzy old one. Thanks for your time and trouble.

[NeoPa]

No worries Chuck. Thank you for your consideration.

Final parting shot, you say many-to-one in your link. I would expect one-to-many to be updateable, but strangely not a many-to-one. If you have the inclination (you're possibly well fed-up by now) you could try it that way around and see.

Ooops, just noticed something else - if they are all AutoNumbers I would expect Access to realise that AutoNumbers should never be linked to AutoNumbers. I don't think that can make sense. Perhaps you just meant they are all Numbers?

[chuck gregory]

NeoPa, in my klutzy old one, almost all the tables are linked by autonumbers, and at least the whole system works. With this new attempt, I've tried linking two text fields together for just two tables, and even that doesn't work.

cg

[NeoPa]

When I say you can't link AutoNumber fields, I mean when BOTH fields are AutoNumbers.

For Text fields to link they should be of the same length. Again, they must also be One-to-One or, in some circumstances, One-to-Many. Many-to-One is not supported.

Does any of this help?

[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

[chuck gregory]

NeoPa!!!

Eureka!

I figured out a fix, although it probably doesn't mean jack as far as understanding relatiionships!

Your comment about the auotnumbers needing to match caused me to look at the autonumbers in the two tables-- and they didn't match. When I generated a few entries to bring them up to par, suddenly all the data appeared in all the previously entered forms.

So I fixed it by moving one field from the table that's always used and putting it in the table that sometimes isn't used, ensuring that both tables will always be used, and therefore the autonumbers will always match.

So, thanks for the prompt! I'm a low slearner, but at least I make progress.

Count this one closed, and go on to your next headache.

chuck gregory

[NeoPa]

I wish I could Chuck.

Let me first warn you that you're treading on very thin ice (as far as Access & AutoNumbers goes - this is not to be confused with a personal warning).

I can see that you have got a better understanding of the problem now, but can you trust me when I tell you that EVER designing with the assumption that two AutoNumber fields (from separate tables) will remain synchronised is entirely unreliable.

I could have helped you earlier synchronise the values in the two tables. I didn't because I would feel that would be very poor service. That is a highly unreliable assumption and WILL lead to further problems if you persist with that design.

[chuck gregory]

Oooh! Thanks for the warning! I'm more than ready to do it a different way.

How should I do it instead?

chuck gregory

[puppydogbuddy]

Hi Neopa,

Maybe this short thread will help Chuck understand that linking two tables by their autonumber keys is not the same thing as linking the two tables based the value of the primary key from one of the tables.

Autonumber Issue

pDog

[NeoPa]

I'm thinking it will pDog ;)

@Chuck, if you have further questions after reading that very concise and helpful post, please come back to us.

[chuck gregory]

So, if I understand this correctly, here';s what I should do:

1. Go into relationships.
2. Delete the auto-id to auto-id link.
3. Drag the auto-id link in the "page 1" (first document half) table to the ID number (which is a long integer) field in "page 2",

OR drag the auto-link id in "page 2" table into the ID field in "page 1, even though the ID field in "page 1" allows for only two digits.

4. Could I reverse the drags and still get the same effect?

[NeoPa]

Before I answer this, can you say if you found you could understand what was being said in that link? How much of it made sense to you?

Your answer will determine how I pitch my answer to you.

[FishVal]

Dear Chuck,

I looked at your prevoiuos threads and .... hmm ... could it be so ... like ... you've been developing database for almost two years without understanding (not even understanding but try to follow) the very basics of relational database design?

I'm talking about
Database Normalization and Table Structures already suggested to you
SQL JOINs
for starters.

If it is so, then ... better late than never.
I promiss, you will have a very different look at it, and, maybe, you will even enjoy it.

Kind regards,
Fish.

[chuck gregory]

NeoPa, what I understood from that was this: You link an autoID field in one table to another long integer field in the other table, and it should work. Only the outa id field has to be the primary key. It doesn't look as though the long integer field even has to have a number in it.

Fish, I knew nothing about Access when aa acquaintance asked me if it might be useful in streamlining some paper flow. Everything I know about it I have learned by trial and error. That section you recommend is Greek to me; the terms used have no explanations as to why they are called that, why they related to one another they way they do, why they have to, or how they apply in less specific or different settings (at least as far as I can tell), and I have difficulty understanding the context in which they are used. NeoPa has been extremely patient in trying to help me deal with this, but the level of education between the two of us is so great, it's almost a Lazarus-and-Dives replay.

I have gone back to the start of this thread to see at what point something could have been asked differently in order to clarify and shorten this discussion, and I don't find such a place! Conclusion: you're both dealing with a caveman!

[NeoPa]

Ug! :D

An AutoID field is something that is linked INTO.

The Long Integer field in the other tables can be managed by Access if defined in the Relationships pane and the wizards are used to create the records. If you understand though, that this is a one-way relationship, then that is an important understanding.

[NeoPa]

So, if I understand this correctly, here';s what I should do:

1. Go into relationships.
2. Delete the auto-id to auto-id link.
3. Drag the auto-id link in the "page 1" (first document half) table to the ID number (which is a long integer) field in "page 2",

OR drag the auto-link id in "page 2" table into the ID field in "page 1, even though the ID field in "page 1" allows for only two digits.

4. Could I reverse the drags and still get the same effect?

Although the reference to the items on the different pages may aid you to understand which is which, the form itself should not come into this process. I wasn't sure if you were confused about this.

I would always drag the PK (AutoID) field across to the FK (Long Integer) field.

even though the ID field in "page 1" allows for only two digits.

Can you explain what you mean by this, and whether this is the PK field or the FK field?

[chuck gregory]

Ah! At last, I think I can provide you with a comprehensible answer:

the primary key in both tables is the AutoID. When I dragged the AutoID in the first table into the long integer field in the second table, I made that long integer field the Foreign key. Or am I wrong? It sounds as though I am, when you talk about dragging in the other direction.

The bit about limiting data in that long integer field is this-- it only allows two keystrokes, then it stops accepting fuirther input. Is that going to invalidate the link? Does the AutoID need to have as much space for input as it has for its own numberal places?

chuck gregory

[NeoPa]

The primary key in both tables is the AutoID. When I dragged the AutoID in the first table into the long integer field in the second table, I made that long integer field the Foreign key. Or am I wrong? It sounds as though I am, when you talk about dragging in the other direction.

No, that's fundamentally right.

You could probably lose the AutoNumber PK field from that second table if you wanted, and make the Long Integer the PK too. But that may be a step too far at this time ;)

The bit about limiting data in that long integer field is this-- it only allows two keystrokes, then it stops accepting fuirther input. Is that going to invalidate the link? Does the AutoID need to have as much space for input as it has for its own numberal places?

With the form created by the wizard, this would not be a problem. This value should then be entered for you automatically. Have you tried setting up a dummy form with the wizard to see how it does that?

[chuck gregory]

NeoPa, since you said "the wizard allows for that," and I didn't use the wizard, rather than risk expecting the wizard to take care of something it had nothing to do with, I have now created in the second table a long integer field called AutoID link, to which I dragged the AutoID field from the first table.

I don't use it for any input, just as the FK field for the first table.

It seems to work. I looked into that field after generating four filled out "forms" and don't see anything the Primary Key had generated in it. Should I be seing something there? Or am I misunderstanding your question?

[NeoPa]

What I was hoping you'd do was to set up another form as a dummy, using the wizard, and see how the wizard created form handles ensuring the non-AutoNumber FK field is always populated with the matching value from the PK of the table with the AutoNumber field in. Does that make more sense now?

I have to stop early tonight as I had a very late one last night & I need to recover. I can revisit tomorrow for a better explanation if it's still necessary then.

[NeoPa]

I have set up a pair of tables (tblTestA & tblTestB), where the PK of tblTestA is an AutoNumber, and the PK of tblTestB is a Long Integer. In the Relationships window I added a 1-to-1 link enabling referential integrity with cascading updates and
deletes.
Table Name=[tblTestA]

Field  Type        IndexInfo
AID     AutoNumber  PK
AData  String

Table Name=[tblTestB]

Field  Type                  IndexInfo
BID     Number(Long Integer)  PK
BData  String

When I created a query to add/manage the data in both tables, the Long Integer BID data was maintained by the system. BID would only be set if some data had been entered into one of the other tblTestB fields though.

SELECT tTA.AID,
       tTB.BID,
       tTA.AData,
       tTB.BData
 
FROM tblTestA AS tTA INNER JOIN
     tblTestB AS tTB
  ON tTA.AID=tTB.BID

[NeoPa]

Let me know if this tells you all you need to know to get this sorted now.

[chuck gregory]

Woosh! A lot of stuff to digest, there! Thanks for going through all the trouble. I won't be able to touch it until Monday. Have a good weekend!

cg

[NeoPa]

You too Chuck :)

I think with this digested you will be most of the way there. It may involve some practical changes around the place, but I suspect you will understand it well enough by then that it won't be too onerous when it comes to it.

[chuck gregory]

I have set up a pair of tables (tblTestA & tblTestB), where the PK of tblTestA is an AutoNumber, and the PK of tblTestB is a Long Integer. In the Relationships window I added a 1-to-1 link enabling referential integrity with cascading updates and deletes.

NeoPa, when you say you "added" the 1-to-1 link, is that when you drag the field from Table A to Table B? And does something special have to be done to get cascading updates and deletes?

Next, where did these lists come from? I've never seen anything printed out like this in my work:
Table Name=[tblTestA]

Field  Type        IndexInfo
AID    AutoNumber  PK
AData  String

Table Name=[tblTestB]

Field  Type                  IndexInfo
BID    Number(Long Integer)  PK
BData  String

And what does BID mean?

When I created a query to add/manage the data in both tables, the Long Integer BID data was maintained by the system. BID would only be set if some data had been entered into one of the other tblTestB fields though.

The way I have it set up now, data has to be entered into at least one of the Table B fields. It is possible, though highly unlikely, that nothing would be entered into Table A. Is data entry into Table B sufficient to generate an ID number in Table A?

SELECT tTA.AID, 
       tTB.BID, 
       tTA.AData, 
       tTB.BData 
 
FROM tblTestA AS tTA INNER JOIN 
     tblTestB AS tTB 
  ON tTA.AID=tTB.BID

And finally, this last bit. What does the lower case t stand for? Is this whole thing something you wrote, or a report from the system as to what now exists? And if you wrote it, what did you write it into, and where can I find out how to do that sort of thing?