Solved

Help with Flat Table Query

Posted on 2013-11-07
8
286 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

757 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

18 Experts available now in Live!

Get 1:1 Help Now