Similar field to connect tables?

[algioia]

What do you suggest I use as the "common field" to link tables... if there is no particular customer id or unique information? The data will be used to run reports by either state, zip or city.

Thanks in advance,
annette

 

[John Big Booty]

This will depend largely on the nature of the data, but in most cases an Autonumber provides the best ID.

 

[algioia]

Thank you for responding! The data is sales data, to be used for sales tax reporting. We will never use it to look up anything by a customer name or id, we would just use it for state and local jurisdiction sales reporting. If I use the Autonumber, would I be able to link the tables?

 

[John Big Booty]

......... If I use the Autonumber, would I be able to link the tables?

Certainly; the Autonumber will be the PK (Primary Key in one table) and the FK (Foreign Key) in the other, This assumes a one to many relationship between the two tables.

 

[gemma-the-husky]

Thank you for responding! The data is sales data, to be used for sales tax reporting. We will never use it to look up anything by a customer name or id, we would just use it for state and local jurisdiction sales reporting. If I use the Autonumber, would I be able to link the tables?

but if it is sales data, then it must mean something -

what information do you have in this table?

 

[algioia]

The client has their own software for any detailed information needed on their customer sales. We just need to house the invoice/sales data to be able to pull their sales by state/jurisdiction. We need the ship to information to see where the products were shipped so we can report the amount of sales for a given period that was shipped to a certain jurisdiction.

I'm attaching a sample of the data that will be in the table. (I thew some May data in there with June) We will receive the same fields each month from the client and there will be multiple sales to the same customers on the reports, so we wouldn't be able to use the customer id's as a unique identifier.

For business licenses, you need to report your sales in that state/jurisdiction for the year. So my plan is to import each month's data into Access but I'll need to learn how to create the queries and reports needed to get the combined sales for the year, etc.

The only information we'd really need from their data is:
Invoice date (so we can query a from this date to this date for the state)
Ship To City, County, State, Zip
Invoice Amount
Non Taxable Amount
Taxable Amount
Tax Amount

Also, they don't fill in all counties in their data, so I'd probably have to link a zip code table to fill in the missing information.