Query Results error

[cfobare79]

I have the SQL for my query below. I'm pulling from multiple tables. The query works great until I added the VerizonTable. Once the VerizonTable is added I recieve no results from the query. I have ensured that all the Field Names and Field Types are the same between DaveWelchTable and VerizonTable. I receive an error message stating that there are multiple Quantities to draw from. Please please with code. Thank you.

[ranman256]

Then there is a problem with the query. Try using single link to Verizon only, check the results.
Then work outward. Add tables. It may be too many tables causes unexpected results.

I would build a union query that contains all other parties as a single query, THEN join to other tables.
(it seems a design flaw. you would not have separate tables for separate companies. They would all be in 1 table tCompanyData , with a field to determine which company it belongs to.)

[cfobare79]

SELECT AgencyList.AgencyID, AgencyList.AgencyName, AgencyList.Address, AgencyList.ZipCode, ZipCodeT.City, ZipCodeT.State, RateTable.Rate, RateTable.Unit, DaveWelchTable.Quantity, DaveWelchTable.BillingPeriod, RateTable.ServiceName, ServiceGroupT.ServiceGroup, AgencyList.WPSBillingType, IIf(IsNull([Quantity]),0,[Quantity]*[Rate]) AS Totals, VerizonTable.Usage
FROM ZipCodeT INNER JOIN ((ServiceGroupT INNER JOIN (RateTable INNER JOIN (AgencyList INNER JOIN DaveWelchTable ON AgencyList.AgencyID = DaveWelchTable.AgencyID) ON RateTable.ServiceID = DaveWelchTable.ServiceID) ON ServiceGroupT.ServiceGroup = RateTable.ServiceGroup) INNER JOIN VerizonTable ON (RateTable.ServiceID = VerizonTable.ServiceID) AND (AgencyList.AgencyID = VerizonTable.AgencyID)) ON ZipCodeT.ZipCode = AgencyList.ZipCode
GROUP BY AgencyList.AgencyID, AgencyList.AgencyName, AgencyList.Address, AgencyList.ZipCode, ZipCodeT.City, ZipCodeT.State, RateTable.Rate, RateTable.Unit, DaveWelchTable.Quantity, DaveWelchTable.BillingPeriod, RateTable.ServiceName, ServiceGroupT.ServiceGroup, AgencyList.WPSBillingType, IIf(IsNull([Quantity]),0,[Quantity]*[Rate]), VerizonTable.Usage
HAVING (((AgencyList.AgencyID)=[Forms]![Filter]![Combo4]) AND ((DaveWelchTable.BillingPeriod)>=[Forms]![Filter]![Combo0] And (DaveWelchTable.BillingPeriod)<=[Forms]![Filter]![Combo2]) AND ((ServiceGroupT.ServiceGroup)=[Forms]![Filter]![Combo13]));

[cfobare79]

The tables have to be set the way they are because of the importation of data and the structure.

[June7]

Essentially duplicate topic https://www.accessforums.net/access/...sue-52163.html