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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Fyed! I'm going to test and get back with you.
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.
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.
ASKER
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?
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
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
ASKER
Thank you Fyed! You have been very helpful!
glad to help.
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.