How to create this database in access?

[David_Skov]

It's rather simple. I have the following coloums:

StoreID - Q303 - Q403 - Q104 - Q204

StoreID is uniqe, and Q303 is revenue in 3.quarter 2003, Q403 is 4.Quarter 2003 etc.

Now that part is something I can handle myself, but First of all I need to enter data into the database which should be rather easy I presume?. But when Q304 is available I need to enter that into a new coloum. How do I do that? I need to make sure that the Q304 data is entered into the correct cell

 

[Andrew Fergus]

Hi David

If you try to set up the periods as separate fields (or columns) in a table then you are going to need to add more fields to your table to accommodate future periods. In which case, the underlying queries, forms and reports will require continual maintenance. Given time you will find this set-up to be a real nuisance.

The layout you are seeking with the store followed by periods can be achieved using a many-to-many relationship or by a crosstab query / report on a well designed database. The many-to-many relationship may be too much for what you are doing and one method of achieving this (although it is still essentially a many-to-many set-up) may have the quarterly sales data table designed as follows :

Field 1 = store id*, Field 2 = quarterly period*, Field 3 = sales value

* = dual primary key and Field 2 (the quarterly period) may or may not be linked to another table (i.e. a table of quarterly sales periods).

However, if the raw sales data is in a table that includes the store id and the sales date (I think you get the data monthly - I saw this in one of your previous posts), then you may not need to store the quarterly data in a table but instead build the quarterly sales analysis by just using a query instead.

If the monthly sales data is tagged with the last date of the month then you could create a new table which links the monthly periods to the quarterly periods (tblMonths?). Any quarterly sales report / query would then make us of tblMonths and include the store details and 4 columns of sales data based on a quarter selected by the user.

There are a few ways of achieving what you want (I have suggested 2 simple methods) but setting up the quarterly sales periods as columns in a table, as you outlined in your original post, won't work in the long run.

HTH, Andrew.

 

[David_Skov]

So what You say is that I build a base with all data mentioned above. THen make another base or table that makes the link between StoreID and Storename. THen a new table that links months data to each of the 4 quarters. In a Query or Crosstable I retrieve the data I need into a report. Is that correctly understood?

Hi David

If you try to set up the periods as separate fields (or columns) in a table then you are going to need to add more fields to your table to accommodate future periods. In which case, the underlying queries, forms and reports will require continual maintenance. Given time you will find this set-up to be a real nuisance.

The layout you are seeking with the store followed by periods can be achieved using a many-to-many relationship or by a crosstab query / report on a well designed database. The many-to-many relationship may be too much for what you are doing and one method of achieving this (although it is still essentially a many-to-many set-up) may have the quarterly sales data table designed as follows :

Field 1 = store id*, Field 2 = quarterly period*, Field 3 = sales value

* = dual primary key and Field 2 (the quarterly period) may or may not be linked to another table (i.e. a table of quarterly sales periods).

However, if the raw sales data is in a table that includes the store id and the sales date (I think you get the data monthly - I saw this in one of your previous posts), then you may not need to store the quarterly data in a table but instead build the quarterly sales analysis by just using a query instead.

If the monthly sales data is tagged with the last date of the month then you could create a new table which links the monthly periods to the quarterly periods (tblMonths?). Any quarterly sales report / query would then make us of tblMonths and include the store details and 4 columns of sales data based on a quarter selected by the user.

There are a few ways of achieving what you want (I have suggested 2 simple methods) but setting up the quarterly sales periods as columns in a table, as you outlined in your original post, won't work in the long run.

HTH, Andrew.

 

[Andrew Fergus]

Hi David, Not quite. If I re-read my post I wasn't 100% clear - my apologies.

So what You say is that I build a base with all data mentioned above.

Not quite - see below.

THen make another base or table that makes the link between StoreID and Storename.

I didn't recommend that but it is something you will need to do anyway - i.e. have a "master file" of store information in a table within your database (e.g. store id, store name, store address, store owner etc.).

THen a new table that links months data to each of the 4 quarters.

Almost - this is one way of doing to the exclusion of my other recommendation - it wouldn't be linking to the 4 quarters per your original post, it would set up links between the "end of the month" and quarter for all quarters. As you receive more and more sales data in your tables, then you would need to establish new links between the months and quarters. This method would be far simpler than rebuilding your queries, forms and reports every time there was a new quarter per your original post.

In a Query or Crosstable I retrieve the data I need into a report. Is that correctly understood?

Yes.
_________________

I don't think you should have the columns of quarterly data in your table for the reasons outlined above (i.e. don't have a table with the fields : store, q1, q2, q3, q4 etc. - it will be very difficult to maintain)

If you want to have a table of quarterly sales data (which I don't think you need if your raw sales data is already in your database) then you should have a table that looks like this (I have shown the bare minimum of fields, you may want more) :

Field 1 = store id*, Field 2 = quarterly period*, Field 3 = sales value

* = dual primary key and Field 2 (the quarterly period) may or may not be linked to another table (i.e. a table of quarterly sales periods).

In this scenario, the first 5 records in the table would look something like this :

Store 1, Q0303, $10,000
Store 1, Q0403, $12,000
Store 1, Q0104, $14,000
Store 1, Q0204, $11,000
Store 2, Q0303, $15,000


BUT, I don't think that is the best solution. Instead of doing the above (which would be storing the data twice if the raw sales data was already stored in a table in the database), then an alternative approach (to the exclusion of the previous suggestion) would be to do something like this :

If the raw sales data is in a table that includes the store id and the sales date (I think you get the data monthly - I saw this in one of your previous posts), then you may not need to store the quarterly data in a table but instead build the quarterly sales analysis by just using a query instead.

In other words, if your raw sales data (assuming the raw sales data is stored in a table in your database) already has the store id and the date then do you need to make a table of quarterly sales data? I don't think you do - hence my recommendation to consider reporting the quarterly sales data using a query.

The last bit of my recommendation was to help with the concept of building a structure to help with the quarterly sales query - if you set up a link between the dates (in the raw sales data) and the quarters (in their own table - just a list of quarters with the quarter id, quarter description, and the relevant dates), then the sales data that falls into the linked quarter is then reported as being in that quarter.

For example, the table that you would use to link the quarters and months (assuming you have month end data) would look like this :

Q0303, "3rd Quarter 2003", 31/07/03
Q0303, "3rd Quarter 2003", 31/08/03
Q0303, "3rd Quarter 2003", 30/09/03
Q0403, "4th Quarter 2003", 31/10/03
Q0403, "4th Quarter 2003", 30/11/03
Q0403, "4th Quarter 2003", 31/12/03
Q0104, "1st Quarter 2004", 31/01/04

{I realise the description could be set up better but I have it repeated here for the sake of clarity}

Assuming your raw sales data has the month end then you would link the "date" in your raw sales data to the "date" in the above table to assign each month end to a quarter. This would make the queries and reports very easy to set up and maintain.

Sorry if this is not 100% clear. Let me know if you do / don't understand this.

Andrew

 

[David_Skov]

Tx very much for Your help

I don't know if it's clear enough, but then again, I need to start creating it to see if I understand

So I return later when I get a little further

But I will for sure use Your guidelines and tx for Your help sofar