Solved

MS Access -Multiple records to one record multiple fields

Posted on 2013-05-31
7
508 Views
Last Modified: 2013-05-31
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
Comment
Question by:IDontMeanToWAG
  • 4
  • 2
7 Comments
 
LVL 92

Expert Comment

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

Author Comment

by:IDontMeanToWAG
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

by:Rey Obrero (Capricorn1)
ID: 39211515
upload a db with the table.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 92

Expert Comment

by:Patrick Matthews
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

by:
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;

Open in new window


Q-28144229.mdb
0
 

Author Closing Comment

by:IDontMeanToWAG
ID: 39211853
Thank you so much!  You are a life saver!!
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39211906
Glad to help :)
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Normalization of a table 19 74
User Level Security 6 38
Microsoft Access Write errors seem to be caused by bit fields 4 36
SQL Group on First occurrence 9 25
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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question