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]

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]

Field  Type        IndexInfo
AID    AutoNumber  PK
AData  String

Table Name=[tblTestB]

Field  Type                  IndexInfo
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.

[NeoPa]

Just a few :D

1. Copy the tables (including the data).
2. Delete the data from the original tables.
3. Set up the relationships etc (Do a click and drag in the copied tables' fields (PK being Table A AutoID and FK being Table B AutoID Link)).
4. Copy / Paste the data back in to the original tables. Table A first.
5. If that complains about not having any B data let me know.

Otherwise that should do you.

[chuck gregory]

IT WORKS!!

There's the "1" at the Table A end of the line and the infinity sign at the Table B end. It's like walking into the living room on Christmas morning! Tonight I go to sleep smarter than when I woke up. And the data is all there. Thank you!

Now, can you steer me to someplace that will explain SQL to a caveman? After I've messed around with that for a few months, I'm sure you'll hear from me again.

Thanks so much for your help!

[NeoPa]

That's great to hear Chuck :)

As far as links are concerned for learning SQL - Remember Google is your friend!

I would possibly recommend W3 Schools - SQL tutorials, and also suggest searching for "Microsoft Jet SQL Reference" in the Access help itself. Jet SQL (used by Access) is not fully compatible with all the standards so I would bear in mind the specifics.

[chuck gregory]

Thanks, NeoPa. May it be a long time before you hear from me again.....

[NeoPa]

I hope not Chuck ;D