We help IT Professionals succeed at work.

Help with Flat Table Query

gogetsome
gogetsome asked
on
322 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?
Comment
Watch Question

Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.

Author

Commented:
Thank you Fyed! I'm going to test and get back with you.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.

Author

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?
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
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

Author

Commented:
Thank you Fyed! You have been very helpful!
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
glad to help.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.