Query/Form pull from several tables, multiple common fields

[mbedford]

I'm buildling a form for tracking printer costs. This form will display data from several different tables pulled together by queries.

formPrintCost will display:

• printer information from tblAssets
• support, maintenance, downtime and supply information and costs from tblSupport
• page counts from tblPrinterMeter

The main control in formPrintCost is listPrinter, which is a List Box with all the printers in tblAssets. Selecting a printer will display all the information described above as well as totals for the costs, cost per page, supply costs, service costs, total downtime, and (eventually, as I learn enough) a report button to generate graphs showing the cost history.

So far I've built queries that display the appropriate information based on the selection in listPrinter, but to make the VBA underlying formPrintCost easier (I'm feeling more confident working with SQL than VBA based on my current skill levels), I'm trying to build one master qryPrintCostMaster that will pull nearly all the data necessary for formPrintCost.

The current query structure is as follows:
On making a selection in formPrintCost!listPrinter, qry PrintCostAssets pulls the selected asset information:

SELECT *
FROM tblAssets
WHERE (((tblAssets.ID)=Forms!formPrintCost!listPrinter));

One of the fields returned is tblAssets.AcquireID. qryPrintCostAcquire uses this field to return the related fields from tblAcquire:

SELECT tblAcquire.*
FROM tblAcquire INNER JOIN qryPrintCostAssets ON tblAcquire.ID=qryPrintCostAssets.AcquireID;

In formPrintCost, using a tab control, I'll show a historical list of support tickets for the selected printer and a list of page count meter readings from tblSupport and tblPrinterMeter, respectively. This is well within my current ability and will be a simple List Box control with sources in qryPrintCostSupport and qryPrintCostMeter and a simple requery command in the VBA upon selection of a printer in listPrinter.

Also based on qryPrintCostSupport, I'll be totalling the value of the Cost field in field SCost in query qryPrintCostSupportCostTotals.

And based on qryPrintCostMeter, I want to pull the latest entries for Color, Black, and Total page counts from the fields CMeter, BMeter, and TMeter into fields CCount, BCount, and TCount in query qryPrintCostMeterTotals.

qryPrintCostMaster will need to pull all the fields from qryPrintCostAssets, qryPrintCostAcquire, qryPrintCostSupportCostTotals, and qryPrintCostMeterTotals.

Since each of these queries will have only one row of data when an entry is selected in formPrintCost!listPrinters, this should be relatively simple, right?

SELECT qryPrintCostAssets.ID AS AssetID, qryPrintCostAssets.UID, qryPrintCostAssets.ModelID, qryPrintCostAssets.Status, qryPrintCostAssets.Dept, qryPrintCostAssets.SubDept, qryPrintCostAssets.SN, qryPrintCostAssets.Box, qryPrintCostAssets.BoxDrop, qryPrintCostAssets.NetName, qryPrintCostAssets.MACAddress, qryPrintCostAssets.IPAddress, qryPrintCostAssets.BESSNo, qryPrintCostAssets.IMMS, qryPrintCostAssets.Description, qryPrintCostAcquire.ID AS AcquireID, qryPrintCostAcquire.Method, qryPrintCostAcquire.Price, qryPrintCostAcquire.DateAcquired, qryPrintCostAcquire.Number, qryPrintCostAcquire.LeaseBegin, qryPrintCostAcquire.LeaseEnd, qryPrintCostAcquire.Vendor, qryPrintCostMeterTotals.BCount, qryPrintCostMeterTotals.CCount, qryPrintCostMeterTotals.TCount, qryPrintCostSupportCostTotals.SCost
FROM qryPrintCostAcquire, qryPrintCostAssets, qryPrintCostSupportCostTotals, qryPrintCostMeterTotals;
 

Right. There are no errors, but all that displays in Datasheet View is all the appropriate field headings and no data.

Running each individual query shows me the correct data based on the selection in formPrinterCost!listPrinters, but qryPrintCostMaster is empty.

First question: Is this a reasonable way to accomplish what I'm trying to accomplish, or am I way off based and trying way too hard? I'm rather new to Access and VBA and this the first time I've tried merging several queries into one, and as mentioned before, I feel more confident writing SQL than working in VBA, being still primarily at the Copy/Paste level in VBA and just into the "I know three different things" level in SQL.

Second question: If I'm not totally off-base, where am I going wrong? Do I need an INNER JOIN or similar to make this query work as well? If so, how would I match the multiple and different related fields among the multiple queries?