Is this "Normal" Table structure

Batman

New member
Local time
Today, 12:21
Joined
Jul 26, 2012
Messages
2
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
 

Attachments

  • Access-1.jpg
    Access-1.jpg
    83.6 KB · Views: 146
Robin

You need to understand relationships a little better.

Tables usually have a Primary Key of type Autonumber. This key is linked to the Foreign Key (type Long Integer) of the other table/s.

You should be able to download many examples of this.

The other tihing I would recommend is that you have a look at your Naming Conventions. Spaces and special characters are not recommended.

Also words like Date are Reservered words. Date will return the current Date, Now will return the current Date and Time. It is better to use things like StartDate, EndDate, DOB etc.

In my signature there is a link to my Sky drive. I wrote a paper on naming conventions which you can download and read. Or do a Google. There are many examples available. All of which are recommendations, not rules.

If you do a little tidy up and repost we could give more advice.
 
You should find this useful also:
http://msaccesshintsandtips.ning.com/profiles/blogs/excel-in-access-part-1

Includes a free tool to help you transfer your data into the correct normalised form. There are instructions on how to use this tool (an access form) there are instructions written down and there are instructions in video format.

The instructions also explain very clearly the reasons why you should seriously consider normalising your data.

The main reason being although it's a little bit more difficult in the beginning it's a hell of a lot easier later!
 
Right having read a bit more, I think I may now have a better understanding of tables and Normalization. I have also done the relationship links and feel that I am now on the right track.
Sorted the naming conventions.
As Departments will differ from store to store they are in a table of their own.
The financial criteria may be added to hence a table for them.
Weeks need to be added to and numbered from 1 to 52 each year
Specials can be added to therefore own table.
All links are from a PK to a FK.
Thanks to all for the guidance so far.
Any comments.
 

Attachments

  • Access-2.jpg
    Access-2.jpg
    80.7 KB · Views: 124

Users who are viewing this thread

Back
Top Bottom