Hi, I am trying to get my head around setting up tables to analyse a Supermarkets Sales data. I have been doing this in excel but feel I now need to move on to Access as the formula’s in excel are becoming too big to cope with comparing yet another years data (fourth year).
I have three components to consider, Date, Departments (14 different departments) and Sales Data for each Department. The objective is to compare the various Sales Data for each Department against the last year and the previous year’s trade per week in most cases and per day in one instance (Total Store).
The information comes from three reports,
1. Sales (Sales per Department)
2. PLU info (Four different classes of specials that are run in the Store)
3. Financial Data ( Types of transactions that are carried out at the checkouts)
At the moment we only compare data for the above on a weekly basis, which will continue for all except I would also like to collect the Total daily sales per department.
I have now created the following Tables & fields
tblSalesData: Fields: ID, Date(Day), Dept, Sales exc, Sales inc, Keyed Sales, GP%, Shrinkage, Markdown
tblPLUinfo: Fields: ID, Week Ending, Dept, Special 1, Special 1 GP%, Special 2, SP@ GP% etc
tblFinancialdata: Fields: ID, Week Ending, Cancels, Cancel value, Voids, Void Value, Returns, Returns Value etc
tblDepts: Fields: ID, Dept
tblWeekno Fields: ID, Week Ending, Week No
tblWages Fields: ID, Week Ending, Dept
The Dept Field in tblDepts has each of the 14 Department names. Meat, Fruit&Veg, Grocery, etc
I have created the following relationships:
(see attachment)
Is this table and relationship structure correct?
Any comments would be welcome.
Robin
I have three components to consider, Date, Departments (14 different departments) and Sales Data for each Department. The objective is to compare the various Sales Data for each Department against the last year and the previous year’s trade per week in most cases and per day in one instance (Total Store).
The information comes from three reports,
1. Sales (Sales per Department)
2. PLU info (Four different classes of specials that are run in the Store)
3. Financial Data ( Types of transactions that are carried out at the checkouts)
At the moment we only compare data for the above on a weekly basis, which will continue for all except I would also like to collect the Total daily sales per department.
I have now created the following Tables & fields
tblSalesData: Fields: ID, Date(Day), Dept, Sales exc, Sales inc, Keyed Sales, GP%, Shrinkage, Markdown
tblPLUinfo: Fields: ID, Week Ending, Dept, Special 1, Special 1 GP%, Special 2, SP@ GP% etc
tblFinancialdata: Fields: ID, Week Ending, Cancels, Cancel value, Voids, Void Value, Returns, Returns Value etc
tblDepts: Fields: ID, Dept
tblWeekno Fields: ID, Week Ending, Week No
tblWages Fields: ID, Week Ending, Dept
The Dept Field in tblDepts has each of the 14 Department names. Meat, Fruit&Veg, Grocery, etc
I have created the following relationships:
(see attachment)
Is this table and relationship structure correct?
Any comments would be welcome.
Robin