Autonumber question

[Steve56]

Can anyone help?

I have 2 seperate tables and I would like to use Autonumber in a field which appears in both tables.

I can use the autonumber in the first table, but when I use it n the second table the field continues with the series of autonumbers from the first table.

Is there any way I can reset this so that it starts at 1 in each table?

Thanks for the help

Steve

 

[neileg]

Are you wanting to use the autonumber as a primary key in the first table and a foreign key in the second table? By this I mean that you link the two tables in a relationship using the autonumber as the link?

The easiest way to do this is at the input stage. Create a form to collect the data for the first table and add a subform to collect the data for the second table. The Access subform wizard will link the tables using the autonumber to determine the link. This way, the field is only an autonumber in the first table. The second table inherits the same value by virtue of the link in the form.

 

[Ceejay64]

Mind if I piggyback a question?
I have a similar situation, but am having some difficulty with it. I was asked to create a db for a form we have been filling out by hand here at work. There are a ton of boxes, so I have broken it up into 3 sections - 3 separate tables that come from a form with 2 subforms. So far, so good...
The first table (and master section of the entry form) holds relative info and has an autonumber ID (the key). The other areas have ID's with numbers that get the same ID as the autonumber when the top section of the form gets an entry.
The problem: If no data is actually entered in either of the subforms, they, in essence, fail to exist.
I need all of the information, whether there is an entry or not, to show up so that the electronic form prints out looking like the old paper forms.
How do I make the sections that are blank show up? I've tried a default in one of the boxes, but that didn't work. Is there code that I can add to the add record button that instructs it to add the subform data (or lack thereof) to their respective tables?
Any help would be wildly appreciated!!
Thanks in advance!

 

[elsandalos]

Hi,
this is a problem i had the other day. I had query which took values from two tables and i wanted to display all records. However, only the first table was full of data, and the result was that the query only returned the records where there was data in both tables.

You need to edit the join properties in the query schematic to include all records from the complete table. The link changes to show an arrow pointing away from the 'master' table.

hope this helps.

El.

 

[Ceejay64]

Sounds like just what I needed. Almost makes me wish I was still at work to take a crack at it...almost.
Now, I have to ask a newbie kind of question, though - how exactly do I go about changing that?
Thanks!

 

[Ceejay64]

Open the query in QBE view.
Right click on the join line.
Choose the option that says to show all rows of tableX and show related entries in tableY.

Yes! I got it, and it works slick!
Thanks a lot!