Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

• Help others & share knowledge
• Earn cash & points
Solved

MS Access -Multiple records to one record multiple fields

Posted on 2013-05-31
513 Views
I have a table that lists multiple records for the same store, with varying values for Code1 and Code2.

Store Nbr                 Count      Code1      Code2
15291            3      COB7
15291            93      COB7      COB7
15293            1      CO172
15293            3      CO172      CO172
15293            2      CO172      OA18
15293            2      COB7
15293            1      COB7      CO97
15293            23      COB7      COB7
15294            13      COB7      COB7
15296            3      CO172      OA18
15307            1            COB7
15307            122      COB7      COB7
15307            1      COB7      OA18

I am wanting to take this information and make a separate table that lists each store only once, with each unique code as a field on that record and the count totalled.

How do I do that?
0
Question by:IDontMeanToWAG
• 4
• 2

LVL 92

Expert Comment

ID: 39211241
Based on your sample above, what result would you expect?
0

Author Comment

ID: 39211420
I am hoping for results like this.

Store      Total            Code1         Code2         Code3       Code4      Code5       Code6
15291      96               COB7
15293      32               CO172       OA18           COB7        CO97
15294      13               COB7
15296       3                COB7         OA18
15307      124             COB7         OA18

I would never expect one store to have more than 8 codes.
0

LVL 120

Expert Comment

ID: 39211515
upload a db with the table.
0

LVL 92

Expert Comment

ID: 39211541
Would having a single column for the codes concatenated together be OK?  I.e.:

Store      Total            Codes
15291      96               COB7
15293      32               CO172, OA18, COB7, CO97
15294      13               COB7
15296       3                COB7, OA18
15307      124             COB7, OA18
0

LVL 92

Accepted Solution

Patrick Matthews earned 500 total points
ID: 39211558
Ugly, but do-able:

``````TRANSFORM First(z.Code) AS FirstOfCode
SELECT z.StoreNbr, z.Total
FROM (
SELECT x.StoreNbr, x.Code, DSum("[Count]", "[SomeTable]", "[StoreNbr] = " & x.StoreNbr) AS Total, Count(y.Code) AS Ordinal
FROM
(SELECT StoreNbr, Code1 AS Code
FROM SomeTable
WHERE Code1 Is Not Null
UNION
SELECT StoreNbr, Code2 AS Code
FROM SomeTable
WHERE Code2 Is Not Null) AS x INNER JOIN
(SELECT StoreNbr, Code1 AS Code
FROM SomeTable
WHERE Code1 Is Not Null
UNION
SELECT StoreNbr, Code2 AS Code
FROM SomeTable
WHERE Code2 Is Not Null) AS y ON x.StoreNbr = y.StoreNbr And x.Code >= y.Code
GROUP BY x.StoreNbr, x.Code
) AS z
GROUP BY z.StoreNbr, z.Total
PIVOT "Code " & z.Ordinal;
``````

Q-28144229.mdb
0

Author Closing Comment

ID: 39211853
Thank you so much!  You are a life saver!!
0

LVL 92

Expert Comment

ID: 39211906
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…