guideline needed to structure table

[PF]

i have data to structure for four quarters of a year

data set 1:
a part number, description and 3 other fields for each product. Those
records are unique

data set 2:
Plus a list of records, each one have
date (dd-mm-yy)
part number
qty ordered
qty receive

i can have multiple records for one part for one day. Each record have the 4
fields

the data must be displayed one line by records
The qty will be displayed in 4 colums depending on the quarter

I will have to display for each record
part,desc, date, qtyorder if quarter1,qtyreceive if quarter1, qtyorder if
quarter2,qtyreceive if quarter2, and so on for the 2 others quarter

What is the best desing in such a case,

should i make
table 2 like four fields

part,date,qty,flag to tell receipt or command

or should i make two tables
one for receive one for command?

or should i make table according to the quarter?

Any clues on how to do that?

regards,

 

[Jeff Boyce]

Access uses tables to hold data, queries to return data sets, and forms
(screens) and reports to display data. Don't confused what you want to see
with what you need to store.

One approach would be to have a table something like:

trelTransaction
TransactionID
TransactionDate (make this a Date/Time value, since the same part can
have more than one per day)
PartNumber (a foreign key from your tblPartNumber, which you described
first)
TransactionType (receipt or order)
TransactionQty

Each row of this table records a single transaction.

You can then create a query that sums transactions, grouped by "Receipt" and
by "Order".

You can use a crosstab query to sum by part number, by quarter.