Querying Two tables to get one result

[mshaynerush]

Background:

I have two tables from two different sources. There is no way to combine these two as one is an excel spreadsheet and the other exists as an ODBC on our server.

I could run a query, maybe to combine them into one, but one has maybe 25 rows, the other about 20,000 rows.


I'm not clear on relationships in Access or between tables and how that works yet.

My job involves tracking contractors on various levels, including, failed inspections in the last 30 days for ALL contractors(over 250 of them), total projects submitted, Money paid to each contractor, etc...

most of that I can query from one table, but for one metric, i have a challenge.

In the code below, I can determine which contractors on the Projects Table have had failed inspections in the last 30 days and populate my report with that list of names and how many inspections they failed.

What i need to do is consider another table's data, since this can't be included in my export from the Projects table which is drawn from a program we use for tracking the entire program process for our projects.

so, I have to include inspection results done by a 3rd party, and track those results myself(I use Excel and created a table called VTEAM TRACKING. I want to make one query that will sum all the failed inspections from the Projects table with the failed inspections in the VTEAM table.

On the projects table, i have to find rows that meet the following criteria to determine what a failed inspection is.

Contractor name excluding "TBD" and no Null values + Failed Post-Inspection X 3 or more + On this date >= today -30 days

On my VTEAM table, i have a field that tells me if the inspection failed or not, so in that, i can do a query for

contractor + inspection date >= today - 30 days + inspection failed = "Yes"

My end result needs to be the total failed inspections in the last 30 days for ALL contractors within my tables as one number i can plug into my report ListBox.

Your help is appreciated as I'm still learning this SQL thing.


My Code that works so far:

SELECT DISTINCT [SBL PROJECTS].CONTRACTOR AS [Contractor Name], Count([SBL PROJECTS].PROJTRACKHISTSTATUS) AS [Total Inspections Failed]
FROM [SBL DETAILS] INNER JOIN [SBL PROJECTS] ON [SBL DETAILS].PROJECTNO = [SBL PROJECTS].PROJECTNO
WHERE ((([SBL PROJECTS].ONTHISDATE)>=Date()-30))
GROUP BY [SBL PROJECTS].CONTRACTOR, [SBL PROJECTS].PROJTRACKHISTSTATUS, [SBL DETAILS].PREINSPECTIONDISP, [SBL DETAILS].POSTINSPECTIONDISP
HAVING ((([SBL PROJECTS].CONTRACTOR)<>"TBD" And ([SBL PROJECTS].CONTRACTOR)<>"In-house" And ([SBL PROJECTS].CONTRACTOR) Is Not Null) AND ((Count([SBL PROJECTS].PROJTRACKHISTSTATUS))>=3) AND (([SBL PROJECTS].PROJTRACKHISTSTATUS)="Failed Post-Inspection"));

 

[jasmith4]

Is there any way you could include some sample data fro both sources? Even if you have to use fake names and addresses, the important thing is to see the fields names (column headers) from both your data sources.

Since this is the MS Access forum, I take it that's what you're using. You could do this in Excel, since it seems the SBL Projects data are in Excel, but I'll speak Access here. By the way, which version of Access are you using?

The first thing you have to new is to create two links in the Tables area, one for SBL Projects, and one for SBL Details.

- If you're in Access 2007 or 2010, go to the External Data ribbon, and use the Excel button to begin linking SBL Projects, or the More/ODBC choice to begin linking SBL Details.

- If you're in Access 2003, go to the Tables area, right-click a blank area (not on a table icon), and choose Link Tables. use the drop-down at the bottom of the dialog box to choose Excel for SBL Projects, or ODBC (maybe is says Microsoft ODBC) for SBL Details.

Either way, provide the information it needs, and you'll end up with two linked tables among your Tables. Now comes the fun part!

Start up the Query Wizard, choose Simple Query, then:

- Use the top drop-down to choose Sel Projects, and click the ">>" button.

- Change the drop-down to Sel Details and click the ">>" button again. Now you have a query that has both tables' data.

- Click Next until you have the choice between Open and Modify: choose Modify! Then click Finish, and you'll be in the Query Design window with two tables.

- There MIGHT be a line between the key field on Sel Projects and the same key field in Sel Details. That key field has to be unique in Sel Projects, just as your social security number is unique to you. If there is no such line, click-and-drag from that key field in Sel Projects to the same key field in Sel Details. THIS IS KEY: it related the two tables in a one-to-many way, just as you are related to a table of your paychecks by social-security number.

- Down in the grid below you can now enter other conditions, choose to show or omit the field, choose a sort order, etc. You can even switch to SQL view to see the SQL it writes for you!

Good luck!