Basic Query !

[Guest]

I am trying to design a databse for my work (1st attempt/Self Taught). I
currently have one main table that contains all the data about each store IE
Town,Turnover,Branch No (Primary) District, Business Type. I then have a
further 5 tables. Each of these 5 tables holds the stockloss information for
each store(there may be more than one entry per store) for each of the last
five years IE Audit Date,Month,£ Sales, £ Loss Loss %. I have a one to many
relationship between the main table and each of the other 5 based on the
branch number.

The column headings of these 5 tables are as follows ( I have just replaced
the 1 with either 2,3,4 or 5)

Branch No 1,Month 1,Audit Date 1,Sales 1, Loss 1.



I need to write a query that will will give a comparison figure IE Sales
over the last three years for a particular store,district,business group etc.

I am able to produce a query that will summarise sales data for a district
over the last three years but the data in the fields sales 2 and 3 are not
correct.




I have tried but am failing badly and would apreciate some basic guidence.
XP 2002

 

[Ken Snell [MVP]]

You don't need 5 children tables, one for each store. Just one table will
do. Add a field for the store number to the table.
TableID (autonumber) primary key
StoreID
BranchNumber
MonthNumber
AuditDate
Sales
Loss

(Note that you could omit the TableID field, and instead use StoreID,
BranchNumber, and MonthNumber fields as a composite primary key -- meaning
that all three fields together comprise the primary key of the table).

Then each record in the table will be a record for a specific store. You can
have as many records as you need.

You then relate the main table to the child table using the StoreID field in
both tables.

This will allow you to write the query that you seek, where you filter the
child table's results based on the store number.