Solved

MS Access 2003 Query

Posted on 2011-03-09
5
257 Views
Last Modified: 2012-05-11
I am trying to create a query based on the fields on the table in the file provided.  I know how to combine the fields Part Name and Part Family into one.  What I need from is in the other fields how to create a query that after i group the records in the first two fields and then count the other records in the other fields based on the first two letters in each field.  As an example the first field that would need to count is which gate broke.  The first column in the query would take Part Name and Part Family and combine them into one column and the rest of the fields in the query would be Assy, DC, FI and MC.  These would contain the count of each from the other fields in the query.
db1.mdb
Book1.xlsx
0
Comment
Question by:AisinAuto
  • 4
5 Comments
 
LVL 10

Expert Comment

by:answer_dude
ID: 35089152
Can you provide a sample of the output that you're looking to create?  I don't understand the relationship between your database table and the spreadsheet...
0
 
LVL 10

Expert Comment

by:answer_dude
ID: 35089202
If I read your question correct this query might work -- however it involves some text manipulation on the "Which Gate Broke" field -- I would recommend creating another distinct field to capture the type (DC, FI, etc.)
TRANSFORM Count([Active Sorts].[Issue Number]) AS [CountOfIssue Number]
SELECT [Part Name] & " - " & [Part Family] AS Product
FROM [Active Sorts]
GROUP BY [Part Name] & " - " & [Part Family]
PIVOT IIf(Left([Which Gate Broke],2)="DC","DC",IIf(Left([Which Gate Broke],2)="FI","FI",IIf(Left([Which Gate Broke],3)="M/C","M/C",IIf(Left([Which Gate Broke],4)="Assy","Assy","Other"))));

Open in new window

0
 
LVL 10

Accepted Solution

by:
answer_dude earned 500 total points
ID: 35089215
Here's your DB back with the query in it.
26875331.mdb
0
 
LVL 1

Author Comment

by:AisinAuto
ID: 35097963
Does your query include the other fields as well, Gate 2, Gate 3 and Last Gate Broke?
0
 
LVL 10

Expert Comment

by:answer_dude
ID: 35337449
Sorry!!  I missed your follow up question.

My query only dealt with the "Which gate broke" field.

How would you want the other three included?  Are they additional breaks?  For example, Issue number 1-0021 has Which Gate Broke = "DC Catch Station" and Gate 2 = "DC hourly Check" and Last Gate To Break = "QC Patrol (D/C)"

So... does that translate to two DC errors and one QC error on part "9404 Upper Valve Body"

If so, then we would have to repeat what I did for the "Which gate broke" field for each of the other fields and then union/group them by part number.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

895 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now