Help with Flat Table Query

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?
gogetsomeAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
gogetsomeAuthor Commented:
Thank you Fyed! I'm going to test and get back with you.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Dale FyeCommented:
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
 
gogetsomeAuthor Commented:
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
 
Dale FyeCommented:
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
 
gogetsomeAuthor Commented:
Thank you Fyed! You have been very helpful!
0
 
Dale FyeCommented:
glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.