Question Database Design

[Mpark01]

Hi, I am fairly new to Access and am having a difficulty in getting tailored data from multiple tables with same structure.

I deal with forecast data released weekly for over 300 products and want to consolidate all data to analyze.

For example, week 1 release forecasts for the next 4 moths products volume and week 2 release also comes up with for the next 4 months one.

In Access, I put the week 1 release into one table, week 1 for the name, products for records and the next 16 weeks for fields and do this over for the week 2 release and so on.

As a result, I want to see products in records and each week with foretasted numbers from week 1 and week 2 releases in fields.

I guess I could use queries, I do know a bit, but I do not know where to start.

Pleas help me out.

 

[plog]

from multiple tables with same structure.

Tables shouldn't have the same structure. Instead, their data should be combined and a new field added to the table to differentiate each.
Read up on normalization:

https://en.wikipedia.org/wiki/Database_normalization

 

[Mpark01]

Tables shouldn't have the same structure. Instead, their data should be combined and a new field added to the table to differentiate each.
Read up on normalization:

Hi, plog.

Thanks for the quick reply.

These forecasts release every week.

I used Excel to analyze and the amount of data gets bigger and bigger.

This is why I am giving a try with Access.

The reason why I designed table above is this is easiest way for me to input a new release into Access.

Please let me know if you have another idea.

 

[plog]

Yes I do, its in my first post.

 

[Pat Hartman]

Welcome aboard
Plog has given you the correct advice. One table is the correct solution. You forgot to remove your Excel hat when you opened Access. Relational databases are quite different from spreadsheets even though tables in datasheet view look a lot like data in a spreadsheet. If you spend a little time to get a basic understanding of normalization, you will save yourself loads of future effort. The path you are taking will lead to having to duplicate everything but with a properly normalized schema, you will use a query with a parameter to select the data you want to analyze at any particular time.

 

[ButtonMoon]

It's worth clarifying something that was said here. It's actually not the case that having multiple tables with the same structure is contrary to any principles of normalization. None of the classical normal forms is violated simply by having the same attributes in more than one place in the database schema. There is however a principle called the Principle of Orthogonal Design (POD), which attempts to address that problem - or at least to formalise the problem. That is the database equivalent of what programmers call DRY - Don't Repeat Yourself. It generally isn't a good idea to repeat yourself by creating tables that duplicate or overlap each other in the type of data they store.

That may sound like a rather academic distinction to make. Does it really matter whether we call it a case for "normalization" or something else? Well, if you intend to use normalization as a tool for solving database design problems then it's useful to know which types of problem it can solve for you and which it can't. It's also useful to know about POD which helps you identify problems that might not otherwise be so obvious (duplicating an entire table is a very obvious problem but the cases where tables only partially duplicate each other can be more subtle).

POD is discussed at some length in the book Database Design and Relational Theory by Chris Date and in Date on Database by the same author.