New records not recognised

[aronw]

(Hope this is the right forum)

I have a database which I created with about 1000 records. (Records imported from Excel). These records are all in one table, I have another couple of tables with no data waiting to be filled via a form.

My problem: Now that I have added more records to my main table approx. 40,000 (imported from Excel) and my form still only recognises the 1000.

I have spent a lot of time creating forms based on a main query that connects my 3 tables. My fear now is that the problem is a relational one (is it?). If so, does this mean that I have to create a whole new set of forms based on the / a new relationship.

There is no unique data in my tables and so Access has generated an auto number field ID. This field is in each table and is how my database is related. Does anyone know if this is an issue?

Currently I have got round the problem by copy and pasting my ID field from the main table into the other tables. This works but, the same error will occur everytime new data is added. I thought being of type 'auto number' it should do this for me. A long term solution anyone?

Help would be greatly appreciated,

TIA

Aron

 

[jjturner]

Just a thought, but have you tried linking your Excel data into the db and then setting up an Append query that will write the added data to all 3 tables at once? I would think this should be able to carry your auto-number field over to the remaining tables, just like you're doing with your query as the form recordsource...

Regards,
John

 

[aronw]

thanks

Aron

 

[Pat Hartman]

When you have relationships between tables and the relationship is optional, for example A customer may not have any orders. you need to use a left join when joining the two tables. The left join will tell the query to return rows from tableA even when there are no matching rows in tableB.