Can this be done in Access? Creating new table out of two existing tables?

[kestefon]

I have a table of "National" data, and second table with percentages for each state. (i.e. x% for New York, y% for Delaware, etc.) I want to take each individual National record and multiply it by each state percentage, creating a new (and much larger) table. For example, if I had 10 National records, my final table would be 10*50, or 500 records.

I've attached a snapshot that should give a clear idea of what I'm trying to accomplish. I know how to do this in Excel, but I'm trying to see whether it's easier in Access. Any thoughts?

Thanks!

[ItsMe]

.... Any thoughts?.....

Why are you storing percentages in a table? Usually, calculations are displayed in reports or forms. I prefer to store only values that are relative to data entry in tables. Then, when I need to do a calculation, I query the data at that point in time. Calculations change as the data gets updated through data entry.

[kestefon]

I'm an Excel user. I'm trying to see whether this can be done in Access to see whether it's a better option for this project. The real data would exceed a million rows, so having one Access database would be better than multiple Excel sheets.

[ItsMe]

I don't disagree that Access will manage many rows of data better than Excel. There are times it is beneficial to use a spreadsheet for complex calculations.

I understand your question. Yes, Access can do what you are describing. I don't believe that what you are describing is the proper way to go about it.

In order for Access to benefit you, you must first import your data into a normalized data structure. If you do not do this, Access will not be of any benefit.

[kestefon]

Thanks. What general steps would be involved to turn that national data into the final product I'm looking for?

[ItsMe]

To give you an appropriate answer I would need to know more about the data and not be posting from my phone.

I can say for certain that you will need research some terms such as

Normalized
Primary Key
Foreign Key
Relationships
Relations

The data within your spreadsheets need to be broken down into groups and types of activities/events that will cause the need to update records

[Dal Jeanis]

If you just want to do that once, then you can use a "cross join" query. You don't have to store the result, though, you can have a query that will calculate it whenever you need it, rather than kludging up your database with data that is basically meaningless anyway.

If you had this table layout, for example,

tblData
   DataLocation  Text 
   DataDate      Date   (April being stored as 04/01/2013)
   DataCode      Text
   DataAmount    Number


tblStatePct
   StateName     Text
   StatePct      Decimal or Currency

Then this query will provide you the data you are looking for, just as if it was all stored in a table.

QueryStateData
 SELECT
    TS.StateName As DataLocation,
    TD.DataDate,
    TD.DataCode,
    Round(TS.StatePct * TD.DataAmount,0) AS DataAmount
 FROM
    tblData as TD,
    tblStatePct AS TS
 WHERE 
    TD.DataLocation = "National";

and you can treat QueryStateData as if it were an actual table itself in any report of query.

So, it's not just easier in Access, it's trivial. You "virtually" do it, rather than really doing it. That's what ItsMe was getting at with his advice.

[kestefon]

Thanks!That's exactly what I was hoping for.

[Dal Jeanis]

If your state percentages ever change, then your join will be a little more complex, and the statetable will need a start/end date for when it's applicable. But the concept is the same.

If you're seriously considering using Access for this, pop over to Roger Calrson's site at http://rogersaccesslibrary.com and review his quick tutorials on database design. A couple of hours up front will save you dozens of hours of tearing your hair out later.

Normalization is a yardstick for keeping yourself from over-complicating your life at the design stage. If you are properly normalized, tehn your queries will be pretty easy to code, like that one. The less normalized your design, the more complicated your queries get.

Coming from the Excel side, you are naturally going to be thinking of the rows in the table as related like the rows in a spreadsheet. They are not related. Rows in a table don't even have an intrinsic order, unless you put a unique key on them. that is an advantage, when you get around to coding the queries. But it's a different way of thinking about how to get your result.

Best Wishes with your design!

[kestefon]

Thanks again to everyone. I have an additional layer of complexity I'd like to add to this. In the query, I'd like to multiply by a different percentage based on certain criteria.

For instance, based on Dal Jeanis' example, what if I have an a different set of percentages for each month of the year, and for each "product type" (e.g. data for automotive products in October would be multiplied by X, in November by Y, etc.). In terms of format, it would be the same as what Dal Jeanis' previously did, but with two additional fields--and the data of course would be multiplied by the appropriate percentage.

Dal Jeanis' original example...

tblData
   DataLocation  Text 
   DataDate      Date   (April being stored as 04/01/2013)
   DataCode      Text
   DataAmount    Number

tblStatePct
   StateName     Text
   StatePct      Decimal or Currency

And the changes:

tblData
   DataLocation  Text 
   DataDate      Date   (April being stored as 04/01/2013)
   ProductType      Text
   DataCode      Text
   DataAmount    Number

tblStatePct
   StateName     Text
   DataDate      Date
   ProductType      Text
   StatePct      Decimal or Currency

[Dal Jeanis]

If you can be sure you will have a StatePct record for each month, then you could use this:

QueryStateData2
 SELECT
    TS.StateName As DataLocation,
    TD.DataDate,
    TD.ProductType,
    TD.DataCode,
    Round(TS.StatePct * TD.DataAmount,0) AS DataAmount
 FROM
    tblData as TD,
    tblStatePct AS TS
 WHERE 
    TD.DataLocation = "National"
AND TD.ProductType = TS.ProductType;
AND TD.DataDate = TS.DataDate;

If you can't be sure you will have a StatePct record for each month, then you would use something like this:

QueryStateData3
 SELECT
    TS.StateName As DataLocation,
    TD.DataDate,
    TD.ProductType,
    TD.DataCode,
    Round(TS.StatePct * TD.DataAmount,0) AS DataAmount
 FROM
    tblData as TD,
    tblStatePct AS TS
 WHERE 
    TD.DataLocation = "National"
AND TD.ProductType = TS.ProductType
AND TS.DataDate = 
    (SELECT MAX (TS2.DataDate) 
     FROM tblStatePct as TS2
     WHERE TS2.DataDate <= TD.DataDate
     AND TS2.ProductType = TD.ProductType);

I can't be positive about the last one, but I think I got it right. You want to multiply each TD record by the latest TS record for the same product that is no earlier than the TD record. The subselect determines the date desired for that TS record, and then the SELECT handles the rest.