Hello,

Please view attached excel file.
I don't know how I would create a table for something like this.

As you can see, each part number has different quantities for the same dates.

Please help me out

Thank you

I'd do it like this:

A table describing all the different parts, one row for each different item, with descriptions, etc, and a unique identifier for each part (called a primary key - an autonumber is good for this)

A table describing each receipt, each row containing:
Date
PartID (a copy of the part's primary key ID - this is called a foreign key - the field should just be a number, not an autonumber here)
Quantity received
etc

So for each quantity of each item received on each date, a row gets added to the second table - each time a new part is introduced to the range of items, a row gets added to the first table, describing it.

Queries can be run linking the transaction data in the second table to the descriptive data in the first - and a crosstab query run on both tables will produce results very much like your spreadsheet, if you still need it that way.

The key strength of doing it this way is that unlike the spreadsheet, you don't need to add new columns when a new product comes on the scene.

Thanks for the reply!

But another question. These excel files come from another source and I want to import it into access. I feel like the way you suggested would require entering all of the information manually. There are a lot of information, a few years worth actually. The portion on that excel file is just one months information.

Can all of this information be entered into this table format without all of the manual work?

Thanks again!

It can be done, but it's not a simple job. What you have is data in a very similar format to what you might get if you created a crosstab query on tables like the ones I described above, using date as a row header and item code as a column header.

Getting data back out of a crosstab-style grid and back into normalized tables involves reading all the values one by one and dealing with them appropriately - either manually (time consuming) or as an automated process.

I did write an 'uncrosstab' code example a long time ago - you'll find it here:
http://www.access-programmers.co.uk/forums/showthread.php?t=28110

It might be possible to modify this to extract your sales data into normalized tables.

Thank you!

I'll look into this.