two queries from same data in table into one query

C

cquinn

I am slightly new at access. I have created two queries that are
finding and summing calculations from the same field on the same table.
So it is the same information, two different ways. Then i am combining
these two queries into a third query that is just a query to gather all
information to shoot into a report for my end of the month billings.
The queries are using data from a form that i use. That is where my
StartDate, EndDate, and JobCode are located. The third query seems to
be adding the right records, but then multiplying by the total amount
of records found in the table. Here is my code. Any help would be
greatly appreciated.

SELECT DISTINCTROW [MASTER BID TABLE].CostCode, [MASTER BID
TABLE].Description, [MASTER BID TABLE].TotalQuantity, [MASTER BID
TABLE].UnitsOfMeasure, [MASTER BID TABLE].UnitCost,
Sum([TotalQuantity]*[UnitCost]) AS TotalAmtBid, Sum(IIf(IsNull([query-
THIS BILLING QUANTITY]!Qty),0,[query- THIS BILLING QUANTITY]!Qty)) AS
QtyThisBill, Sum([query- THIS BILLING QUANTITY]!Qty*[UnitCost]) AS
TotalAmtThisBill, Sum(IIf(IsNull([query- PREV BILLED
QTY]!Qty),0,[query- PREV BILLED QTY]!Qty)) AS QtyPrevBill,
Sum([UnitCost]*[query- PREV BILLED QTY]!Qty) AS TotalAmtPrevBilled,
[QtyPrevBill]+[QtyThisBill] AS TotalQtyTD, Sum(((IIf(IsNull([query-
THIS BILLING QUANTITY]!Qty),0,[query- THIS BILLING
QUANTITY]!Qty))+(IIf(IsNull([query- PREV BILLED QTY]!Qty),0,[query-
PREV BILLED QTY]!Qty)))*[UnitCost]) AS TotalAmtTD,
Format([TotalQtyTD]/[TotalQuantity],"Percent") AS PercentComplete, [JOB
SITE].Description, [JOB SITE].Address1, [JOB SITE].Address2, [JOB
SITE].Owner, [JOB SITE].OwnerAddress1, [JOB SITE].OwnerAddress2, [JOB
SITE].OwnerPhone, Forms![form- MONTHLY BILLING]!StartDate AS StartDate,
Forms![form- MONTHLY BILLING]!EndDate AS EndDate, [JOB
SITE].ProjectManager, [MASTER BID TABLE].JobCode, [JOB
SITE].ProjectManagerPhone
FROM ([query- PREV BILLED QTY] RIGHT JOIN ([query- THIS BILLING
QUANTITY] RIGHT JOIN [MASTER BID TABLE] ON [query- THIS BILLING
QUANTITY].CostCode = [MASTER BID TABLE].CostCode) ON [query- PREV
BILLED QTY].CostCode = [MASTER BID TABLE].CostCode) INNER JOIN [JOB
SITE] ON [MASTER BID TABLE].JobCode = [JOB SITE].JobCode
GROUP BY [MASTER BID TABLE].CostCode, [MASTER BID TABLE].Description,
[MASTER BID TABLE].TotalQuantity, [MASTER BID TABLE].UnitsOfMeasure,
[MASTER BID TABLE].UnitCost, [JOB SITE].Description, [JOB
SITE].Address1, [JOB SITE].Address2, [JOB SITE].Owner, [JOB
SITE].OwnerAddress1, [JOB SITE].OwnerAddress2, [JOB SITE].OwnerPhone,
Forms![form- MONTHLY BILLING]!StartDate, Forms![form- MONTHLY
BILLING]!EndDate, [JOB SITE].ProjectManager, [MASTER BID
TABLE].JobCode, [JOB SITE].ProjectManagerPhone
HAVING ((([MASTER BID TABLE].JobCode)=[Forms]![form- MONTHLY
BILLING]![Combo6]))
ORDER BY [MASTER BID TABLE].CostCode;
 
C

Chris2

cquinn said:
I am slightly new at access. I have created two queries that are
finding and summing calculations from the same field on the same table.
So it is the same information, two different ways. Then i am combining
these two queries into a third query that is just a query to gather all
information to shoot into a report for my end of the month billings.
The queries are using data from a form that i use. That is where my
StartDate, EndDate, and JobCode are located. The third query seems to
be adding the right records, but then multiplying by the total amount
of records found in the table. Here is my code. Any help would be
greatly appreciated.
Click to expand...


cquinn,

The query, realigned for readability (at least as much as it will
ever be readable when IIF statements are included).

SELECT DISTINCTROW
[MASTER BID TABLE].CostCode
,[MASTER BID TABLE].Description
,[MASTER BID TABLE].TotalQuantity
,[MASTER BID TABLE].UnitsOfMeasure
,[MASTER BID TABLE].UnitCost
,Sum([TotalQuantity]*[UnitCost]) AS TotalAmtBid
,Sum(IIf(IsNull([query-THIS BILLING QUANTITY]!Qty)
,0
,[query- THIS BILLING QUANTITY]!Qty))
AS QtyThisBill
,Sum([query- THIS BILLING QUANTITY]!Qty * [UnitCost])
AS TotalAmtThisBill
,Sum(IIf(IsNull([query- PREV BILLED QTY]!Qty)
,0
,[query- PREV BILLED QTY]!Qty)) AS QtyPrevBill
,Sum([UnitCost] * [query- PREV BILLED QTY]!Qty)
AS TotalAmtPrevBilled
,[QtyPrevBill] + [QtyThisBill] AS TotalQtyTD
,Sum(((IIf(IsNull([query- THIS BILLING QUANTITY]!Qty)
,0
,[query- THIS BILLING QUANTITY]!Qty))
+
(IIf(IsNull([query- PREV BILLED QTY]!Qty)
,0
,[query- PREV BILLED QTY]!Qty)))
*
[UnitCost])
AS TotalAmtTD
,Format([TotalQtyTD]/[TotalQuantity],"Percent")
AS PercentComplete
,[JOB SITE].Description
,[JOB SITE].Address1
,[JOB SITE].Address2
,[JOB SITE].Owner
,[JOB SITE].OwnerAddress1
,[JOB SITE].OwnerAddress2
,[JOB SITE].OwnerPhone
,Forms![form- MONTHLY BILLING]!StartDate AS StartDate
,Forms![form- MONTHLY BILLING]!EndDate AS EndDate
,[JOB SITE].ProjectManager
,[MASTER BID TABLE].JobCode
,[JOB SITE].ProjectManagerPhone
FROM ([query- PREV BILLED QTY]
RIGHT JOIN
([query- THIS BILLING QUANTITY]
RIGHT JOIN
[MASTER BID TABLE]
ON [query- THIS BILLINGQUANTITY].CostCode =
[MASTER BID TABLE].CostCode)
ON [query- PREV BILLED QTY].CostCode =
[MASTER BID TABLE].CostCode)
INNER JOIN
[JOB SITE]
ON [MASTER BID TABLE].JobCode =
[JOB SITE].JobCode
GROUP BY [MASTER BID TABLE].CostCode
,[MASTER BID TABLE].Description
,[MASTER BID TABLE].TotalQuantity
,[MASTER BID TABLE].UnitsOfMeasure
,[MASTER BID TABLE].UnitCost
,[JOB SITE].Description
,[JOB SITE].Address1
,[JOB SITE].Address2
,[JOB SITE].Owner
,[JOB SITE].OwnerAddress1
,[JOB SITE].OwnerAddress2
,[JOB SITE].OwnerPhone
,Forms![form- MONTHLY BILLING]!StartDate
,Forms![form- MONTHLY BILLING]!EndDate
,[JOB SITE].ProjectManager
,[MASTER BID TABLE].JobCode
,[JOB SITE].ProjectManagerPhone
HAVING ((([MASTER BID TABLE].JobCode) =
[Forms]![form- MONTHLY BILLING]![Combo6]))
ORDER BY [MASTER BID TABLE].CostCode;


Ok, now we just need:

1) A good description of your table structures.
2) The SQL of the other two queries.
3) A sampling of your data.
4) A sampling of the result data that are currently in error (based
on the sample data in #3).


Notes: If you have control of your database structure:

1) Remove all spaces and non-letter/number characters (anything not
A-Z, a-z, or 0-9; and avoid 0-9 if you can).
2) Chose a consistent naming standard:
a) All capital (EMPLOYEES, CUSTOMERS, ADDRESSES, CUSTOMERCONTACTS)
b) All lower case (employees, customers, addresses,
customercontacts)
c) Or all first letter capital and the rest small case (Employees,
Customers, Addresses, CustomerContacts)
For object names (tables, queries, column names, everything).
One naming standard will increase readability.
3) If you need to include object types in object names, use "tbl,
qry, frm" instead of "query- , form- " (but strongly consider
avoiding it), and then either use all prefixes (the standard) or all
suffixes. Use them consistently in all cases, instead of some in
some places, and not in others. Consider omitting all prefixes and
suffixes. Use plural/collective nouns for table names, and singular
nouns for column names.


Sincerely,

Chris O.
 
C

cquinn

Here is the code from by first query [query- PREV BILLED QTY]

SELECT
[MASTER INFO TABLE].Date,
[MASTER INFO TABLE].Qty,
[MASTER INFO TABLE].CostCode,
[MASTER INFO TABLE].JobCode

FROM [MASTER INFO TABLE]
WHERE ((([MASTER INFO TABLE].Date)
Between #1/1/2004#
And (([Forms]![form- MONTHLY BILLING]![StartDate])-1)));



Here is the code from by second query [query- THIS BILLING QUANTITY]

SELECT
[MASTER INFO TABLE].Date,
[MASTER INFO TABLE].Qty,
[MASTER INFO TABLE].CostCode,
[MASTER INFO TABLE].JobCode

FROM [MASTER INFO TABLE]
WHERE ((([MASTER INFO TABLE].Date)
Between ([Forms]![form- MONTHLY BILLING]![StartDate])
And ([Forms]![form- MONTHLY BILLING]![EndDate])));


I know that my labeling is not the best coding style. I am going to
try to clean it up.My table structure for my [MASTER BID TABLE] is as
follows:

ID JobCode CostCode Description TotalQuantity UnitsOfMeasure
UnitCost

My table structure for my [MASTER INFO TABLE] is as follows:

ID Date Name JobCode CostCode Qty
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I need to display info from two different table into one query 12
Test records from one table / query against a date range in another table 2
run a make table query using parameters in multi-select list box 7

Top