Change Data Layout

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am not sure how to go about this, but I think a query is my best bet. I am
trying but have not come up with an answer and any help would be appreciated.
I have a table of values and use a couple queries to manipulate the data
(mainly math stuff) to get it to this point. The fields are Name, Type,
Category, Value1, Value2

Sample data would look like this

John Type1 Category1 4 5
Fred Type1 Category2 8 0
Tom Type2 Category3 6 7

What I need to do is make the data look like that below for reporting
options, if the type is "Type1" then I need to basically make two records,
each with one of the two values, otherwise if it is not "Type1" then I need
it to sum the two values in one record. So the above data would end up
looking like this:

John Type1 Category1 4
John Type1 Category1 5
Fred Type1 Category2 8
Fred Type1 Category2 0
Tom Type2 Category3 13

Like I said, am am trying but the last two days I have come up blank, I am
not sure a query is the right direction to go, but I am not sure what else to
do. Any help would be great.

Thanks
James
 
Create a NEW table, which will become the destination for the rest of what
I'm about to describe.

1. Using an Append query, append all of the Type1 Value1's, then Value2, so
two seperate queries.
2. Then using another append query, append all of the Type2's, with Value1 +
Value2
3. Once it's all in the destination table, use it as the datasource for
queries, forms, or report.
4. (Maybe empty the table before starting, by using a Delete query.)

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting
 
Back
Top