Solved

Help with Flat Table Query

Posted on 2013-11-07
8
289 Views
Last Modified: 2013-11-07
Hello, I'm new to Access SQL and have inherited a flat table that looks like this:

ID, Supplier, Date, partnumber1, partname1,  Defect1,Cost1, partnumber2, partname2, Defect2, Cost2, partnumber3, partname3, Defect3, Cost3, Status

I need to create a query for a report that can select by supplier, date range or Status. Supplier or Status may be null depending on the type of report to pull.

The result set of the query needs to look like this:

ID, Supplier, Date, partnumber1, partname1, Defect1, Cost1 --- row 1 in the table
ID, Supplier, Date, partnumber2, partname2, Defect2, Cost2
ID, Supplier, Date, partnumber1, partname1, Defect1, Cost1 -- row 2 in the table
ID, Supplier, Date, partnumber1, partname1, Defect1, Cost1 --- row 3 in the table
ID, Supplier, Date, partnumber2, partname2, Defect2, Cost2
ID, Supplier, Date, partnumber3, partname3, Defect3, Cost3

Any given row may have one or all three parts defects filled in. I do not want to create a row for the report where for example  partnumber2, partname2, Defect2 are null.

I also need a sum of the cost for a total area.


Is this possible in Access SQL? How is the best way to accomplish my task?
0
Comment
Question by:gogetsome
  • 4
  • 3
8 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39630561
You need a Union query, the syntax would look something like:sample union queryPlease forgive the use of the image, my IT guys have done something that prevents me from posting union queries to the web (probably has something to do with SQL injection attacks).
0
 
LVL 84
ID: 39630564
So you just need to query on Supplier, Date and Status?

If so:

SELECT * FROM YourTable WHERE Supplier='YourSupplier'

or

SELECT * FROM YourTable WHERE Date>#Date1# AND Date<#Date2#

Of course, you'd have to supply the values for "YourSupplier" and the Date1 and Date2. Generally to do that, you would create a form, and the user would select values in the form, and you'd "build up" the SQL for your query. For example, if i have a Textbox named txSupplier, I'd do this in the Click event of a button, perhaps:

Dim sql As String

If Nz(Me.txSupplier, "") <> "" Then
  '/ user entered something
  sql = " Supplier='" & Me.txSupplier & "'"
End If

Now open your report with that as the WHERE argument:

DoCmd.OpenReport "YourREportName", , , , sql

Assuming YourReportName is based on that Table, this should open the report and show only those values where the Supplier is = to the Value entered by the user.
0
 

Author Comment

by:gogetsome
ID: 39630661
Thank you Fyed! I'm going to test and get back with you.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39630732
gogetsome,

That query will normalize the data in your table, but you would still need to apply your criteria to that query or insert your criteria into each of the elements of the union query.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:gogetsome
ID: 39630742
Fyed, if I was to need a join on another table to build each row I would that in each union statement with the same results?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39630797
Generally, I would do it something like:

SELECT UQ.*, OT.FieldX, OT.FieldY
FROM UnionQuery as UQ
INNER JOIN OtherTable as OT
ON UQ.SomeField = OT.SomeField

If you don't want to save the UnionQuery as a separate query you could use

SELECT UQ.*, OT.FieldX, OT.FieldY
FROM (
'insert the union query here as a subquery
) as UQ
INNER JOIN OtherTable as OT
ON UQ.SomeField = OT.SomeField
0
 

Author Closing Comment

by:gogetsome
ID: 39630820
Thank you Fyed! You have been very helpful!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39630839
glad to help.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

914 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

16 Experts available now in Live!

Get 1:1 Help Now