wizard doesn't ask for table relationship

[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]

[I]Field  Type        IndexInfo[/I]
AID     AutoNumber  PK
AData  String

Table Name=[tblTestB]

[I]Field  Type                  IndexInfo[/I]
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]

[I]Field  Type        IndexInfo[/I]
AID    AutoNumber  PK
AData  String

Table Name=[tblTestB]

[I]Field  Type                  IndexInfo[/I]
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?

[NeoPa]

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?

Yes. Dragging the field across is what does it. At that point a small form pops up giving you options to :

• Enforce Referential Integrity.
• Cascade Update Related Fields.
• Cascade Delete Related Records.

[NeoPa]

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

[I]Field  Type        IndexInfo[/I]
AID    AutoNumber  PK
AData  String

Table Name=[tblTestB]

[I]Field  Type                  IndexInfo[/I]
BID    Number(Long Integer)  PK
BData  String

This is just a way to display table meta-data as clearly as I can on this forum.
Essentially it lists the fields in the table, and describes what type they are (String; Number; AutoNumber; etc), as well as how they are indexed.

[NeoPa]

And what does BID mean?

The "B" indicates that the field is from tblTestB. The "ID" simply says it's the Identifier. [ID] is a common fieldname for such items. This version is just helpfully indicating which table it's from. The fields [AID] & [BID] are actually matching fields from the two tables. Only [AID] is an AutoNumber though.

[NeoPa]

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?

This sounds like you may have Table A and Table B round the wrong way.

Table A must ALWAYS exist. Even if that means creating a table where there are no fields other than the [ID] field.

PS. The name of the fields [AID] & [BID] are only used for illustrative purposes. The fieldnames you use CAN be [AID] etc, but you use what makes sense to you.

[NeoPa]

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?

FROM tblTestA AS tTA

This is SQL code and the AS keyword indicates an alias. Aliases are useful in SQL to represent references to long names with much shorter, more convenient alternatives. The alternative used doesn't matter to the code.

I often use initials as an abbreviation. In this case [tbl Test A] (spaces for illustration only), t for tbl, T for Test & A for A. The result - tTA.

The rest of your questions are a little bit vague for me. Do you know what SQL is? And where / how it fits into Access queries?

[chuck gregory]

NeoPa,

I wnet into Relationships, deleted the existing one, then re-recreated it and got that box. When I tried to "enforce relational integrity, I got a message saying it couldn't be done, "for example, there may be records relating to an employee in the related table, but no record for the employee in the primary table."

Right now, the only link between them occurs when data is entered in two adjoining fields which happen to come from different tables. Once that is done, then everything works the way it's supposed to. But Access provides no help in saying how to set up a link between the two tables otherwise. Considering that my Table A has over 180 flelds and Table B has 150, I would like to think that there's some connection between them that Access would perceive. It seems the only way I can satisfy Access is to have, say, the subject's name as a field in both tables. Would that work?

I'll ask about SQL later.

chuck

[NeoPa]

I'm sorry Chuck. I'm trying to follow you, but there's so much general and ambiguous terms in your explanation, and so few specific terms and item names, that you've lost me.

Let's try to keep things as simple and basic as we can. Work up from the bottom then we can both keep up.

[NeoPa]

I wnet into Relationships, deleted the existing one, then re-recreated it and got that box. When I tried to "enforce relational integrity, I got a message saying it couldn't be done, "for example, there may be records relating to an employee in the related table, but no record for the employee in the primary table."

Creating relationships should always be done prior to entering data. It CAN be done afterwards, but problems are just made bigger that way.

You can back up the data (Hell, you can back up the whole database with a simple file copy command) into a new table using Copy / Paste.

Relationships are fundamental and should be got exactly right before trying to add data. If the data doesn't add after that, you either have wrong data, or wrongly defined relationships.

[NeoPa]

Right now, the only link between them occurs when data is entered in two adjoining fields which happen to come from different tables. Once that is done, then everything works the way it's supposed to. But Access provides no help in saying how to set up a link between the two tables otherwise.

This confuses me, as I thought we'd already discussed the fields that need to be used to join the tables.

Table A (the one that MUST always exist) has an AutoNumber field (called [AID] in my illustration earlier).
Table B (and all others linked in the same way) has a Long Integer Number field (called [BID] in my illustration earlier).

[AID] (or whatever yours is called) should be dragged over to [BID] (your version) to create the link.

I hope that clarifies things.

[chuck gregory]

Ah! Then I did it right the first time by creating the relationship before entering data and then screwed it up by experimenting with the relationship afterward! THAT I can wrap my mind around.

So now, it seems, to restore the proper relationship between the tables, I could:

1. Delete the data in tables A & B, and delete the relationship.
2. Copy the tables.
3. Do a click and drag in the copied tables' fields (PK being Table A AutoID and FK being Table B AutoID Link).

Which would establish the relationship A and B used to have before I started experimenting with it.

I could then delete the original A & B tables and be back in business entering the data in the copied tables and basing the queries, reports and forms on them.

If you see a flaw with this, let me know.