?
Solved

Help with Flat Table Query

Posted on 2013-11-07
8
Medium Priority
?
298 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 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 85
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Expert Comment

by:Dale Fye
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
 

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 48

Expert Comment

by:Dale Fye
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 48

Expert Comment

by:Dale Fye
ID: 39630839
glad to help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

764 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