wizard doesn't ask for table relationship

[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 misunderstandin g 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.