two queries from same data in table into one query
cquinn
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;
Chris2
"cquinn" <chris_...@yahoo.com> wrote in message
news:1134315976.2...@g49g2000cwa.googlegroups.com...
> 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.
>
<snip>
>
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.
cquinn
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