Database Design for data input (1 Viewer)

[Nevsky78]

Hi All,

I've been trying every way I can think of to construct a database that holds forecast sales data for my company. However, I have reached a bit of a brick wall.

I decided to go back to the 2 fundamental tables that will be used. tblCustomers holds my customer specific data, including a discount rate, and tblItems holds all the product data. The number of records in both these tables are fixed and will not change.

The next step that I need to construct is the ability to have a user manually input data for forecast monthly sales i.e. 12 fields headed Jan through to December against each item for each customer.

For example, if there were 3 customers and 3 items, in another table?/query? there would be 9 rows with 14 fields (the fields being Customer, Item, Jan, Feb, Mar etc.).

I may be overcomplicating things with the way I have been doing things so can anyone think of the best way to do this from a modelling point of view?

I've attached the database.

Thanks,
Nick

 

[jzwp22]

12 fields headed Jan through to December against each item for each customer.

You would not have 12 fields in a table one for each month because that would be considered a repeating group and would violate normalization rules (for an overview of normalization check out this site).

Also, it is not a good idea to have spaces or special characters in your table or field names.

First you need a table to related the items to their customers

tblCustomerItems
-pkCustomerItemID primary key, autonumber
-fkCustomerID foreign key to tblCustomers
-fkItemID foreign key to tblItems

Now you need to associate the sales data to each customer/item combination. Each month's sales data would be a record in this table.

tblCustomerItemSales
-pkCustomerItemSalesID primary key, autonumber
-fkCustomerItemID foreign key to tblCustomerItems
-MonthEndingDate
-SalesAmt

 

[Nevsky78]

Hi jzwp22,

I've made the changes as you've advised - many thanks for that. I can't attach the database now as it's too big!

I suppose my next issue is that the users will only want to see a datasheet where they can filter on a Customer field (to find their customer) and see a row for each Item with fields for Jan through December. Can you suggest how I might go about constructing that?

Thank you!
Nick

 

[jzwp22]

Did you run the compact and repair utility on the database? If not, that should help reduce the size. You can also zip it after compacting it to see if that would help. Alternatively, you can make a copy of the database and in the copy remove all the forms, compact and repair and try again. The forms you created previously will not work with the new structure anyway.

 

[Nevsky78]

Hiya,

Yup, I ran the compact and repair and it still comes in at about 24mb. I've tried to compress it but we've got a freebie compression programme and it's playing silly buggers.

There are still no forms in the database.

I've tried to understand more about repeating groups from the article but I still haven't got my head round it.

I guess I don't want [tblCustomerItemSales].[MonthEndingDate] to be a field that a user can input. I need each [tblCustomerItems].[pkCustomerItemID] to already have a record for 31/01/2012 (Jan), 29/02/2011 (Feb) etc.

Nick

Nick

 

[jzwp22]

If you don't want your users to input the dates then you will have to do it with an append query.

Do you just have tables in the database? If so, how many?

 

[Nevsky78]

I just have the tables now;

tblItems
tblCustomers
tblCustomerItems
tblCustomerItemSales

How would I create an Append Query to add each of the 12 dates to each Customer and Item matrix?

Nick

 

[jzwp22]

The most efficient way would be to embed the append query within a loop, but of course that can only be done in Visual Basic for Application (VBA) code. Are you familiar with VBA?

Out of curiosity, is someone going to be entering the sales data manually or are you going to be importing it via a spreadsheet?

 

[Nevsky78]

I am familiar with VBA to the extent that I can find some coding online to do what I want and then amend it - coding from scratch beyond basic Do.Cmds is beyond me!

Well, you asking that question has actually piqued my interest. I was going to have it so that they would enter it manually in Access, but if I could work out a way to import it from a normalised Excel sheet, or sheets, that might be prefereable....

 

[jzwp22]

If you are going to import it then you can add the date during the import and would not need the separate routine to add the dates.

If you wanted to pursue the addition of the dates, you would basically open a recordset based on the tblCustomerItems and loop through each record and then you would have an inner loop that adds the 12 records (assuming that you will add only 1 year's worth at a time) to the tblCustomerItemSales table. I can draft the code if you want to pursue this option versus the import.

 

[Nevsky78]

I think the best way would be to pursue the addition of the dates with a loop - it would maintain better data entry integrity as quite frankly the users would probably manage to break any data validation I used in Excel.

So if you could draft a code that would be fantastic! Thank you very much for your help!

Nick

 

[Nevsky78]

And sorry, yes, it would just be a year at a time.

However, the sales data is amended throughout the year. For example, Customer A, Item 1 starts out with Oct quantity as 100 and Nov quantity as 200. AT some point during the year they would want to change the Oct quantity to 650 and Nov quantity to 100.

 

[jzwp22]

I've attached an example database with the code.

 

[emorris1000]

So, what it sounds like is that your dates were already cross-tabbed. This is something that you will want as an end result of your queries/forms/manipulations, not something you want as a base table.

If you want to *reverse cross-tab* it from an excel sheet there is a way to do that. I can't remember all the details but it involves using the Excel 2003 pivot chart tool then....I can't remember, something with the totals column.

This will give you a result that looks like

Customer, Item, Month/Year, Quantity

George, Golf Clubs, March, 2011, 10
George, Golf Clubs, Feb, 2011, 3
....

Although really George and Golf Club should be represented by an ID# of some sort.

Which you can then run a cross-tab query on and set the months as fields which will give you back the data in the form you have now, which a lot of people find more appealing. The query wizard should walk you through it.

The hard part is doing the *reverse cross-tab* and I would not suggest trying to do that programmatically if you don't have much experience, it would involve some array looping and transposes that could be pretty rough for a novice, and a single slipped index would completely thrash your data.

If someone does write something for you like was mentioned above, you should go ahead and delete any records that have zero as a quantity, you can assume that a null is equivalent. Should save you quite a bit of space.

 

[Nevsky78]

Ah, I'm with you on that...I can see already that will help things out.

I may keep the Excel data input, with a user friendly front sheet, and a normalising tab that puts the data in a table format like you suggest. I can then run a mutiple spreadsheet transfer to get all the records in a single table.

That might work much better, thank you!

 

[emorris1000]

Found the link on doing the reverse cross-tab.

http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/

If you are working with Excel 2007 (and maybe 2010?) you have to bring up the old (2003) pivot table system, which you can do by pressing Alt-D then P, I believe, with your data highlighted.

Should do the trick.

 

[Nevsky78]

Thanks emorris!

I've worked out how to do that with the product code down the y axis and month across the x axis.

I guess the next thing I do is create a linked table in Access?

Also, there are multiple customers who will only want to see an item list against their Customer Name. I guess I'll best create the Excel workbook as a shared document and educate them in using filters?

Nick