Database Design (Editing Queries)

[Scott J.]

I am creating an Access database that uses information that is imported
through text files. I have 4 tables:
- tblLateData - 9 fields including ID (PrimaryKey), Order #, and ServiceCode
(empty)
- tblShipData - ID (PrimaryKey), Order #, and Initials of service agent
- tblCSR - service agent initials (PK) and name
- tblServiceCodes - Description and Code (PK)

The tblShipData has many duplicate records (constraint of my imported data),
so I created a query to show only the unique records (qryNoDups). I then link
that query with tblLateData so the service agent can find their orders and
fill in the ServiceCode.

The problem is how do I join (and create appropriate primary keys) the
qryNoDups and tblLateData (by Order #) so that the query is editable? The
imported data is limiting my choices. Any suggestions?

Thanks for your help! Scott J.

 

[tina]

you can dump the "no dups" records into a temporary table, and use that in
your query - or you can consider the imported data as the temporary data,
and dump the "no dups" records into a permanent table. you'll have to think
it through to determine which is the appropriate solution for your specific
needs - or perhaps neither.

hth

 

[J. Goddard]

Hi -

Try this: Import your shipping data into a temporary table, complete
with dups, then base qrynodups on the temporary table, and make it an
append query to append to tblShipData. This eliminates the duplicate
problem.

Depending on where the ID field of tblShipData comes from (is it in the
imported data?) and how it is used (is it a FK in another table?), it
might not be quite that simple.

John

 

[Scott J.]

Thanks for the suggestions Goddard and tina! I got it to work by appending
the query.

Scott J.