By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,824 Members | 2,410 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,824 IT Pros & Developers. It's quick & easy.

wizard doesn't ask for table relationship

P: 78
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
Aug 29 '08 #1
Share this Question
Share on Google+
64 Replies


NeoPa
Expert Mod 15k+
P: 31,489
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.
Aug 29 '08 #2

Expert Mod 2.5K+
P: 2,545
...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
Aug 29 '08 #3

P: 78
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
Aug 29 '08 #4

NeoPa
Expert Mod 15k+
P: 31,489
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.
Aug 29 '08 #5

P: 78
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
Sep 2 '08 #6

P: 78
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
Sep 2 '08 #7

NeoPa
Expert Mod 15k+
P: 31,489
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.
Sep 2 '08 #8

P: 78
Thanks, NeoPa. It will be nice to know if's not simply paranoia.
Sep 3 '08 #9

NeoPa
Expert Mod 15k+
P: 31,489
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.
Sep 3 '08 #10

NeoPa
Expert Mod 15k+
P: 31,489
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?
Sep 4 '08 #11

P: 78
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.
Sep 8 '08 #12

NeoPa
Expert Mod 15k+
P: 31,489
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?
Sep 8 '08 #13

P: 78
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
Sep 11 '08 #14

NeoPa
Expert Mod 15k+
P: 31,489
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?
Sep 11 '08 #15

P: 78
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
Sep 12 '08 #16

NeoPa
Expert Mod 15k+
P: 31,489
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.
Sep 14 '08 #17

P: 78
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
Sep 19 '08 #18

NeoPa
Expert Mod 15k+
P: 31,489
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 ;)
Sep 19 '08 #19

P: 78
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
Sep 25 '08 #20

NeoPa
Expert Mod 15k+
P: 31,489
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)?
Sep 25 '08 #21

P: 78
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
Sep 30 '08 #22

NeoPa
Expert Mod 15k+
P: 31,489
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.
Sep 30 '08 #23

Expert Mod 2.5K+
P: 2,545
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
Sep 30 '08 #24

P: 78
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
Sep 30 '08 #25

Expert Mod 2.5K+
P: 2,545
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
Sep 30 '08 #26

NeoPa
Expert Mod 15k+
P: 31,489
When you say Page I & Page II, are you referring to tabs (properly called pages) on your form?
Sep 30 '08 #27

P: 78
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
Sep 30 '08 #28

NeoPa
Expert Mod 15k+
P: 31,489
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).
Sep 30 '08 #29

P: 78
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
Oct 1 '08 #30

P: 78
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
Oct 1 '08 #31

NeoPa
Expert Mod 15k+
P: 31,489
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.
Oct 2 '08 #32

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

How should I do it instead?

chuck gregory
Oct 3 '08 #33

puppydogbuddy
Expert 100+
P: 1,923
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
Oct 4 '08 #34

NeoPa
Expert Mod 15k+
P: 31,489
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.
Oct 5 '08 #35

P: 78
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?
Oct 6 '08 #36

NeoPa
Expert Mod 15k+
P: 31,489
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.
Oct 6 '08 #37

FishVal
Expert 2.5K+
P: 2,653
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.
Oct 6 '08 #38

P: 78
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!
Oct 6 '08 #39

NeoPa
Expert Mod 15k+
P: 31,489
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.
Oct 7 '08 #40

NeoPa
Expert Mod 15k+
P: 31,489
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?
Oct 7 '08 #41

P: 78
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
Oct 7 '08 #42

NeoPa
Expert Mod 15k+
P: 31,489
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?
Oct 7 '08 #43

P: 78
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?
Oct 9 '08 #44

NeoPa
Expert Mod 15k+
P: 31,489
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.
Oct 9 '08 #45

NeoPa
Expert Mod 15k+
P: 31,489
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]
Expand|Select|Wrap|Line Numbers
  1. Field  Type        IndexInfo
  2. AID     AutoNumber  PK
  3. AData  String
Table Name=[tblTestB]
Expand|Select|Wrap|Line Numbers
  1. Field  Type                  IndexInfo
  2. BID     Number(Long Integer)  PK
  3. 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.
Expand|Select|Wrap|Line Numbers
  1. SELECT tTA.AID,
  2.        tTB.BID,
  3.        tTA.AData,
  4.        tTB.BData
  5.  
  6. FROM tblTestA AS tTA INNER JOIN
  7.      tblTestB AS tTB
  8.   ON tTA.AID=tTB.BID
Oct 10 '08 #46

NeoPa
Expert Mod 15k+
P: 31,489
Let me know if this tells you all you need to know to get this sorted now.
Oct 10 '08 #47

P: 78
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
Oct 10 '08 #48

NeoPa
Expert Mod 15k+
P: 31,489
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.
Oct 10 '08 #49

P: 78
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]
Expand|Select|Wrap|Line Numbers
  1. Field  Type        IndexInfo
  2. AID    AutoNumber  PK
  3. AData  String
Table Name=[tblTestB]
Expand|Select|Wrap|Line Numbers
  1. Field  Type                  IndexInfo
  2. BID    Number(Long Integer)  PK
  3. 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?

Expand|Select|Wrap|Line Numbers
  1. SELECT tTA.AID, 
  2.        tTB.BID, 
  3.        tTA.AData, 
  4.        tTB.BData 
  5.  
  6. FROM tblTestA AS tTA INNER JOIN 
  7.      tblTestB AS tTB 
  8.   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?
Oct 13 '08 #50

64 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.