Expression to total invoices

  • Thread starter Thread starter Nona
  • Start date Start date
N

Nona

I have a query that calculates the amount invoiced each month in a query:
For Instance:
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed])
AugInv: IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed])


Could someone tell me how can I get the query to give me a total for all the
months?

I tried this expression but it didn’t work: YearAmt: Sum([JulInv]+[AugInv])

Thanks in advance for your help!
 
It looks like you could use a crosstab query rather than a bunch of similar
expressions. If you want to include all months, why not just use:
AllMthsInv: [ServCodeRate]*[UnitsUsed]
Your IIf()s are also missing the 3rd argument which I assume should be
either 0 or Null.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed], Null)
 
Thanks for replying. And especially for the tip about the 3rd argument. I
fixed that right away.

However, the AllMthsInv formula doesn't give me what I need. It gives the
result per record for all the months.

The query expression I gave as an example calculates all the invoices that
were made in July, so that I have a total that were invoiced for July.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0)
The next step is to get a total for the year.

I don't understand why the query won't total the amounts found in the
expressions, as in:
[JulyInv]+[AugInv]+[SepInv], etc., using the names in each month's expression.


--
Nona


Duane Hookom said:
It looks like you could use a crosstab query rather than a bunch of similar
expressions. If you want to include all months, why not just use:
AllMthsInv: [ServCodeRate]*[UnitsUsed]
Your IIf()s are also missing the 3rd argument which I assume should be
either 0 or Null.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed], Null)

--
Duane Hookom
Microsoft Access MVP


Nona said:
I have a query that calculates the amount invoiced each month in a query:
For Instance:
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed])
AugInv: IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed])


Could someone tell me how can I get the query to give me a total for all the
months?

I tried this expression but it didn’t work: YearAmt: Sum([JulInv]+[AugInv])

Thanks in advance for your help!
Click to expand...
Click to expand...
 
Can you provide all of your SQL view for the query? It might help to get a
better understanding of your records and full specs for the query.
--
Duane Hookom
Microsoft Access MVP


Nona said:
Thanks for replying. And especially for the tip about the 3rd argument. I
fixed that right away.

However, the AllMthsInv formula doesn't give me what I need. It gives the
result per record for all the months.

The query expression I gave as an example calculates all the invoices that
were made in July, so that I have a total that were invoiced for July.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0)
The next step is to get a total for the year.

I don't understand why the query won't total the amounts found in the
expressions, as in:
[JulyInv]+[AugInv]+[SepInv], etc., using the names in each month's expression.


--
Nona


Duane Hookom said:
It looks like you could use a crosstab query rather than a bunch of similar
expressions. If you want to include all months, why not just use:
AllMthsInv: [ServCodeRate]*[UnitsUsed]
Your IIf()s are also missing the 3rd argument which I assume should be
either 0 or Null.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed], Null)

--
Duane Hookom
Microsoft Access MVP


Nona said:
I have a query that calculates the amount invoiced each month in a query:
For Instance:
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed])
AugInv: IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed])


Could someone tell me how can I get the query to give me a total for all the
months?

I tried this expression but it didn’t work: YearAmt: Sum([JulInv]+[AugInv])

Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
Yes, here it is. I will certainly appreciate any suggestions!

SELECT tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0) AS JulInv,
IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed],0) AS AugInv,
IIf([CalMonth]="September",[ServCodeRate]*[UnitsUsed],0) AS SepInv,
IIf([CalMonth]="October",[ServCodeRate]*[UnitsUsed],0) AS OctInv,
IIf([CalMonth]="November",[ServCodeRate]*[UnitsUsed],0) AS NovInv,
IIf([CalMonth]="December",[ServCodeRate]*[UnitsUsed],0) AS DecInv,
IIf([CalMonth]="January",[ServCodeRate]*[UnitsUsed],0) AS JanInv,
IIf([CalMonth]="February",[ServCodeRate]*[UnitsUsed],0) AS FebInv,
IIf([CalMonth]="March",[ServCodeRate]*[UnitsUsed],0) AS MarInv,
IIf([CalMonth]="April",[ServCodeRate]*[UnitsUsed],0) AS AprInv,
IIf([CalMonth]="May",[ServCodeRate]*[UnitsUsed],0) AS MayInv,
IIf([CalMonth]="June",[ServCodeRate]*[UnitsUsed],0) AS JunInv,
Sum([JulInv]+[AugInv]) AS Amt, tblServicesProvided.PTLRegWages,
tblServicesProvided.PTLPDO, tblServicesProvided.PQARegWages,
tblServicesProvided.FICA, tblServicesProvided.TaxOther,
tblServicesProvided.WComp, tblServicesProvided.GroupIns,
tblServicesProvided.LiabilityIns, tblServicesProvided.Match401K,
tblServicesProvided.Travel, tblServicesProvided.LocalOfficeEx,
tblServicesProvided.COOverhead, tblServicesProvided.CorpAdmExp,
tblServicesProvided.TotDirectExp, tblServicesProvided.TotIndExp,
tblServicesProvided.TotExpense, tblServicesProvided.TotNonUCRClaim,
tblServicesProvided.[PRCSS-Reg], tblServicesProvided.[PRCSS-PDO],
tblServicesProvided.NonCapExp
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
tblServicesProvided.PTLRegWages, tblServicesProvided.PTLPDO,
tblServicesProvided.PQARegWages, tblServicesProvided.FICA,
tblServicesProvided.TaxOther, tblServicesProvided.WComp,
tblServicesProvided.GroupIns, tblServicesProvided.LiabilityIns,
tblServicesProvided.Match401K, tblServicesProvided.Travel,
tblServicesProvided.LocalOfficeEx, tblServicesProvided.COOverhead,
tblServicesProvided.CorpAdmExp, tblServicesProvided.TotDirectExp,
tblServicesProvided.TotIndExp, tblServicesProvided.TotExpense,
tblServicesProvided.TotNonUCRClaim, tblServicesProvided.[PRCSS-Reg],
tblServicesProvided.[PRCSS-PDO], tblServicesProvided.NonCapExp;

--
Nona


Duane Hookom said:
Can you provide all of your SQL view for the query? It might help to get a
better understanding of your records and full specs for the query.
--
Duane Hookom
Microsoft Access MVP


Nona said:
Thanks for replying. And especially for the tip about the 3rd argument. I
fixed that right away.

However, the AllMthsInv formula doesn't give me what I need. It gives the
result per record for all the months.

The query expression I gave as an example calculates all the invoices that
were made in July, so that I have a total that were invoiced for July.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0)
The next step is to get a total for the year.

I don't understand why the query won't total the amounts found in the
expressions, as in:
[JulyInv]+[AugInv]+[SepInv], etc., using the names in each month's expression.


--
Nona


Duane Hookom said:
It looks like you could use a crosstab query rather than a bunch of similar
expressions. If you want to include all months, why not just use:
AllMthsInv: [ServCodeRate]*[UnitsUsed]
Your IIf()s are also missing the 3rd argument which I assume should be
either 0 or Null.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed], Null)

--
Duane Hookom
Microsoft Access MVP


:

I have a query that calculates the amount invoiced each month in a query:
For Instance:
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed])
AugInv: IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed])


Could someone tell me how can I get the query to give me a total for all the
months?

I tried this expression but it didn’t work: YearAmt: Sum([JulInv]+[AugInv])

Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
I'm surprised the query doesn't error with a message about your IIf()
expressions not being part of an aggregate. They should either be in a Sum,
Avg, etc or in the Group By.

Are you sure your query works?

BTW: Your table structure is highly un-normalized. You have services as
field names. If you want to add another service, it looks like you will need
to create another field. This is generally considered poor design.

--
Duane Hookom
Microsoft Access MVP


Nona said:
Yes, here it is. I will certainly appreciate any suggestions!

SELECT tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0) AS JulInv,
IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed],0) AS AugInv,
IIf([CalMonth]="September",[ServCodeRate]*[UnitsUsed],0) AS SepInv,
IIf([CalMonth]="October",[ServCodeRate]*[UnitsUsed],0) AS OctInv,
IIf([CalMonth]="November",[ServCodeRate]*[UnitsUsed],0) AS NovInv,
IIf([CalMonth]="December",[ServCodeRate]*[UnitsUsed],0) AS DecInv,
IIf([CalMonth]="January",[ServCodeRate]*[UnitsUsed],0) AS JanInv,
IIf([CalMonth]="February",[ServCodeRate]*[UnitsUsed],0) AS FebInv,
IIf([CalMonth]="March",[ServCodeRate]*[UnitsUsed],0) AS MarInv,
IIf([CalMonth]="April",[ServCodeRate]*[UnitsUsed],0) AS AprInv,
IIf([CalMonth]="May",[ServCodeRate]*[UnitsUsed],0) AS MayInv,
IIf([CalMonth]="June",[ServCodeRate]*[UnitsUsed],0) AS JunInv,
Sum([JulInv]+[AugInv]) AS Amt, tblServicesProvided.PTLRegWages,
tblServicesProvided.PTLPDO, tblServicesProvided.PQARegWages,
tblServicesProvided.FICA, tblServicesProvided.TaxOther,
tblServicesProvided.WComp, tblServicesProvided.GroupIns,
tblServicesProvided.LiabilityIns, tblServicesProvided.Match401K,
tblServicesProvided.Travel, tblServicesProvided.LocalOfficeEx,
tblServicesProvided.COOverhead, tblServicesProvided.CorpAdmExp,
tblServicesProvided.TotDirectExp, tblServicesProvided.TotIndExp,
tblServicesProvided.TotExpense, tblServicesProvided.TotNonUCRClaim,
tblServicesProvided.[PRCSS-Reg], tblServicesProvided.[PRCSS-PDO],
tblServicesProvided.NonCapExp
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
tblServicesProvided.PTLRegWages, tblServicesProvided.PTLPDO,
tblServicesProvided.PQARegWages, tblServicesProvided.FICA,
tblServicesProvided.TaxOther, tblServicesProvided.WComp,
tblServicesProvided.GroupIns, tblServicesProvided.LiabilityIns,
tblServicesProvided.Match401K, tblServicesProvided.Travel,
tblServicesProvided.LocalOfficeEx, tblServicesProvided.COOverhead,
tblServicesProvided.CorpAdmExp, tblServicesProvided.TotDirectExp,
tblServicesProvided.TotIndExp, tblServicesProvided.TotExpense,
tblServicesProvided.TotNonUCRClaim, tblServicesProvided.[PRCSS-Reg],
tblServicesProvided.[PRCSS-PDO], tblServicesProvided.NonCapExp;

--
Nona


Duane Hookom said:
Can you provide all of your SQL view for the query? It might help to get a
better understanding of your records and full specs for the query.
--
Duane Hookom
Microsoft Access MVP


Nona said:
Thanks for replying. And especially for the tip about the 3rd argument. I
fixed that right away.

However, the AllMthsInv formula doesn't give me what I need. It gives the
result per record for all the months.

The query expression I gave as an example calculates all the invoices that
were made in July, so that I have a total that were invoiced for July.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0)
The next step is to get a total for the year.

I don't understand why the query won't total the amounts found in the
expressions, as in:
[JulyInv]+[AugInv]+[SepInv], etc., using the names in each month's expression.


--
Nona


:

It looks like you could use a crosstab query rather than a bunch of similar
expressions. If you want to include all months, why not just use:
AllMthsInv: [ServCodeRate]*[UnitsUsed]
Your IIf()s are also missing the 3rd argument which I assume should be
either 0 or Null.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed], Null)

--
Duane Hookom
Microsoft Access MVP


:

I have a query that calculates the amount invoiced each month in a query:
For Instance:
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed])
AugInv: IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed])


Could someone tell me how can I get the query to give me a total for all the
months?

I tried this expression but it didn’t work: YearAmt: Sum([JulInv]+[AugInv])

Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
Yes, the query works. I did get that error message once. After playing with
it a bit, I found that if I did not include a checkbox field in the query
(called CostSettled), it worked fine and has ever since. I would like to know
why the query would not accept the checkbox field. I've used it in other
queries with no problem.

How else would I set the db up without using the field names as services?
You're probably mean the Human Resource fields for payroll etc. This database
is intended to handle contracts and to monitor the costs involved in the
provision of services.

The HR fields are entered monthly as integers that come via the HR dept. All
Access does is to print those on the report (invoice) when it is billed to
the contractor. There are no calculations with those fields. Some contracts
use these fields; others do not. More importantly, the contract itself is
included in the database as projected income.

It's a little disturbing to have you say the db is poorly designed, though I
am far from being an expert. I will certainly appreciate any suggestions you
can recommend.

Can you explain why I can't get the totals I need?

With much appreciation for your time and expertise,


--
Nona


Duane Hookom said:
I'm surprised the query doesn't error with a message about your IIf()
expressions not being part of an aggregate. They should either be in a Sum,
Avg, etc or in the Group By.

Are you sure your query works?

BTW: Your table structure is highly un-normalized. You have services as
field names. If you want to add another service, it looks like you will need
to create another field. This is generally considered poor design.

--
Duane Hookom
Microsoft Access MVP


Nona said:
Yes, here it is. I will certainly appreciate any suggestions!

SELECT tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0) AS JulInv,
IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed],0) AS AugInv,
IIf([CalMonth]="September",[ServCodeRate]*[UnitsUsed],0) AS SepInv,
IIf([CalMonth]="October",[ServCodeRate]*[UnitsUsed],0) AS OctInv,
IIf([CalMonth]="November",[ServCodeRate]*[UnitsUsed],0) AS NovInv,
IIf([CalMonth]="December",[ServCodeRate]*[UnitsUsed],0) AS DecInv,
IIf([CalMonth]="January",[ServCodeRate]*[UnitsUsed],0) AS JanInv,
IIf([CalMonth]="February",[ServCodeRate]*[UnitsUsed],0) AS FebInv,
IIf([CalMonth]="March",[ServCodeRate]*[UnitsUsed],0) AS MarInv,
IIf([CalMonth]="April",[ServCodeRate]*[UnitsUsed],0) AS AprInv,
IIf([CalMonth]="May",[ServCodeRate]*[UnitsUsed],0) AS MayInv,
IIf([CalMonth]="June",[ServCodeRate]*[UnitsUsed],0) AS JunInv,
Sum([JulInv]+[AugInv]) AS Amt, tblServicesProvided.PTLRegWages,
tblServicesProvided.PTLPDO, tblServicesProvided.PQARegWages,
tblServicesProvided.FICA, tblServicesProvided.TaxOther,
tblServicesProvided.WComp, tblServicesProvided.GroupIns,
tblServicesProvided.LiabilityIns, tblServicesProvided.Match401K,
tblServicesProvided.Travel, tblServicesProvided.LocalOfficeEx,
tblServicesProvided.COOverhead, tblServicesProvided.CorpAdmExp,
tblServicesProvided.TotDirectExp, tblServicesProvided.TotIndExp,
tblServicesProvided.TotExpense, tblServicesProvided.TotNonUCRClaim,
tblServicesProvided.[PRCSS-Reg], tblServicesProvided.[PRCSS-PDO],
tblServicesProvided.NonCapExp
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
tblServicesProvided.PTLRegWages, tblServicesProvided.PTLPDO,
tblServicesProvided.PQARegWages, tblServicesProvided.FICA,
tblServicesProvided.TaxOther, tblServicesProvided.WComp,
tblServicesProvided.GroupIns, tblServicesProvided.LiabilityIns,
tblServicesProvided.Match401K, tblServicesProvided.Travel,
tblServicesProvided.LocalOfficeEx, tblServicesProvided.COOverhead,
tblServicesProvided.CorpAdmExp, tblServicesProvided.TotDirectExp,
tblServicesProvided.TotIndExp, tblServicesProvided.TotExpense,
tblServicesProvided.TotNonUCRClaim, tblServicesProvided.[PRCSS-Reg],
tblServicesProvided.[PRCSS-PDO], tblServicesProvided.NonCapExp;

--
Nona


Duane Hookom said:
Can you provide all of your SQL view for the query? It might help to get a
better understanding of your records and full specs for the query.
--
Duane Hookom
Microsoft Access MVP


:

Thanks for replying. And especially for the tip about the 3rd argument. I
fixed that right away.

However, the AllMthsInv formula doesn't give me what I need. It gives the
result per record for all the months.

The query expression I gave as an example calculates all the invoices that
were made in July, so that I have a total that were invoiced for July.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0)
The next step is to get a total for the year.

I don't understand why the query won't total the amounts found in the
expressions, as in:
[JulyInv]+[AugInv]+[SepInv], etc., using the names in each month's expression.


--
Nona


:

It looks like you could use a crosstab query rather than a bunch of similar
expressions. If you want to include all months, why not just use:
AllMthsInv: [ServCodeRate]*[UnitsUsed]
Your IIf()s are also missing the 3rd argument which I assume should be
either 0 or Null.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed], Null)

--
Duane Hookom
Microsoft Access MVP


:

I have a query that calculates the amount invoiced each month in a query:
For Instance:
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed])
AugInv: IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed])


Could someone tell me how can I get the query to give me a total for all the
months?

I tried this expression but it didn’t work: YearAmt: Sum([JulInv]+[AugInv])

Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
Regarding the table structure, I would have set up the Services as products
that can be ordered. Compare this to the Northwind database, you can add
products (services) at will to the products (tblServices) table without
changing any table structures. Each order (contract?) for products (services)
will have multiple records in the Order Details (new tblContractServices)
table.

If you remove the display checkbox in the query design, then your columns
does you no good. I'm not sure what you are doing with your query but I would
expect you want to sum these expressions.

Sum(IIf([CalMonth]="March",[ServCodeRate]*[UnitsUsed],0)) AS MarInv

Then a total sum for all months would be

Sum([ServCodeRate]*[UnitsUsed]) AS TotInv


--
Duane Hookom
Microsoft Access MVP


Nona said:
Yes, the query works. I did get that error message once. After playing with
it a bit, I found that if I did not include a checkbox field in the query
(called CostSettled), it worked fine and has ever since. I would like to know
why the query would not accept the checkbox field. I've used it in other
queries with no problem.

How else would I set the db up without using the field names as services?
You're probably mean the Human Resource fields for payroll etc. This database
is intended to handle contracts and to monitor the costs involved in the
provision of services.

The HR fields are entered monthly as integers that come via the HR dept. All
Access does is to print those on the report (invoice) when it is billed to
the contractor. There are no calculations with those fields. Some contracts
use these fields; others do not. More importantly, the contract itself is
included in the database as projected income.

It's a little disturbing to have you say the db is poorly designed, though I
am far from being an expert. I will certainly appreciate any suggestions you
can recommend.

Can you explain why I can't get the totals I need?

With much appreciation for your time and expertise,


--
Nona


Duane Hookom said:
I'm surprised the query doesn't error with a message about your IIf()
expressions not being part of an aggregate. They should either be in a Sum,
Avg, etc or in the Group By.

Are you sure your query works?

BTW: Your table structure is highly un-normalized. You have services as
field names. If you want to add another service, it looks like you will need
to create another field. This is generally considered poor design.

--
Duane Hookom
Microsoft Access MVP


Nona said:
Yes, here it is. I will certainly appreciate any suggestions!

SELECT tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0) AS JulInv,
IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed],0) AS AugInv,
IIf([CalMonth]="September",[ServCodeRate]*[UnitsUsed],0) AS SepInv,
IIf([CalMonth]="October",[ServCodeRate]*[UnitsUsed],0) AS OctInv,
IIf([CalMonth]="November",[ServCodeRate]*[UnitsUsed],0) AS NovInv,
IIf([CalMonth]="December",[ServCodeRate]*[UnitsUsed],0) AS DecInv,
IIf([CalMonth]="January",[ServCodeRate]*[UnitsUsed],0) AS JanInv,
IIf([CalMonth]="February",[ServCodeRate]*[UnitsUsed],0) AS FebInv,
IIf([CalMonth]="March",[ServCodeRate]*[UnitsUsed],0) AS MarInv,
IIf([CalMonth]="April",[ServCodeRate]*[UnitsUsed],0) AS AprInv,
IIf([CalMonth]="May",[ServCodeRate]*[UnitsUsed],0) AS MayInv,
IIf([CalMonth]="June",[ServCodeRate]*[UnitsUsed],0) AS JunInv,
Sum([JulInv]+[AugInv]) AS Amt, tblServicesProvided.PTLRegWages,
tblServicesProvided.PTLPDO, tblServicesProvided.PQARegWages,
tblServicesProvided.FICA, tblServicesProvided.TaxOther,
tblServicesProvided.WComp, tblServicesProvided.GroupIns,
tblServicesProvided.LiabilityIns, tblServicesProvided.Match401K,
tblServicesProvided.Travel, tblServicesProvided.LocalOfficeEx,
tblServicesProvided.COOverhead, tblServicesProvided.CorpAdmExp,
tblServicesProvided.TotDirectExp, tblServicesProvided.TotIndExp,
tblServicesProvided.TotExpense, tblServicesProvided.TotNonUCRClaim,
tblServicesProvided.[PRCSS-Reg], tblServicesProvided.[PRCSS-PDO],
tblServicesProvided.NonCapExp
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
tblServicesProvided.PTLRegWages, tblServicesProvided.PTLPDO,
tblServicesProvided.PQARegWages, tblServicesProvided.FICA,
tblServicesProvided.TaxOther, tblServicesProvided.WComp,
tblServicesProvided.GroupIns, tblServicesProvided.LiabilityIns,
tblServicesProvided.Match401K, tblServicesProvided.Travel,
tblServicesProvided.LocalOfficeEx, tblServicesProvided.COOverhead,
tblServicesProvided.CorpAdmExp, tblServicesProvided.TotDirectExp,
tblServicesProvided.TotIndExp, tblServicesProvided.TotExpense,
tblServicesProvided.TotNonUCRClaim, tblServicesProvided.[PRCSS-Reg],
tblServicesProvided.[PRCSS-PDO], tblServicesProvided.NonCapExp;

--
Nona


:

Can you provide all of your SQL view for the query? It might help to get a
better understanding of your records and full specs for the query.
--
Duane Hookom
Microsoft Access MVP


:

Thanks for replying. And especially for the tip about the 3rd argument. I
fixed that right away.

However, the AllMthsInv formula doesn't give me what I need. It gives the
result per record for all the months.

The query expression I gave as an example calculates all the invoices that
were made in July, so that I have a total that were invoiced for July.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0)
The next step is to get a total for the year.

I don't understand why the query won't total the amounts found in the
expressions, as in:
[JulyInv]+[AugInv]+[SepInv], etc., using the names in each month's expression.


--
Nona


:

It looks like you could use a crosstab query rather than a bunch of similar
expressions. If you want to include all months, why not just use:
AllMthsInv: [ServCodeRate]*[UnitsUsed]
Your IIf()s are also missing the 3rd argument which I assume should be
either 0 or Null.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed], Null)

--
Duane Hookom
Microsoft Access MVP


:

I have a query that calculates the amount invoiced each month in a query:
For Instance:
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed])
AugInv: IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed])


Could someone tell me how can I get the query to give me a total for all the
months?

I tried this expression but it didn’t work: YearAmt: Sum([JulInv]+[AugInv])

Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
If you haven't tried crosstab queries, enter this into the SQL view of a new
query:

TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) as TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum([ServCodeRate]*[UnitsUsed]) as TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth;

--
Duane Hookom
Microsoft Access MVP


Nona said:
Yes, the query works. I did get that error message once. After playing with
it a bit, I found that if I did not include a checkbox field in the query
(called CostSettled), it worked fine and has ever since. I would like to know
why the query would not accept the checkbox field. I've used it in other
queries with no problem.

How else would I set the db up without using the field names as services?
You're probably mean the Human Resource fields for payroll etc. This database
is intended to handle contracts and to monitor the costs involved in the
provision of services.

The HR fields are entered monthly as integers that come via the HR dept. All
Access does is to print those on the report (invoice) when it is billed to
the contractor. There are no calculations with those fields. Some contracts
use these fields; others do not. More importantly, the contract itself is
included in the database as projected income.

It's a little disturbing to have you say the db is poorly designed, though I
am far from being an expert. I will certainly appreciate any suggestions you
can recommend.

Can you explain why I can't get the totals I need?

With much appreciation for your time and expertise,


--
Nona


Duane Hookom said:
I'm surprised the query doesn't error with a message about your IIf()
expressions not being part of an aggregate. They should either be in a Sum,
Avg, etc or in the Group By.

Are you sure your query works?

BTW: Your table structure is highly un-normalized. You have services as
field names. If you want to add another service, it looks like you will need
to create another field. This is generally considered poor design.

--
Duane Hookom
Microsoft Access MVP


Nona said:
Yes, here it is. I will certainly appreciate any suggestions!

SELECT tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0) AS JulInv,
IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed],0) AS AugInv,
IIf([CalMonth]="September",[ServCodeRate]*[UnitsUsed],0) AS SepInv,
IIf([CalMonth]="October",[ServCodeRate]*[UnitsUsed],0) AS OctInv,
IIf([CalMonth]="November",[ServCodeRate]*[UnitsUsed],0) AS NovInv,
IIf([CalMonth]="December",[ServCodeRate]*[UnitsUsed],0) AS DecInv,
IIf([CalMonth]="January",[ServCodeRate]*[UnitsUsed],0) AS JanInv,
IIf([CalMonth]="February",[ServCodeRate]*[UnitsUsed],0) AS FebInv,
IIf([CalMonth]="March",[ServCodeRate]*[UnitsUsed],0) AS MarInv,
IIf([CalMonth]="April",[ServCodeRate]*[UnitsUsed],0) AS AprInv,
IIf([CalMonth]="May",[ServCodeRate]*[UnitsUsed],0) AS MayInv,
IIf([CalMonth]="June",[ServCodeRate]*[UnitsUsed],0) AS JunInv,
Sum([JulInv]+[AugInv]) AS Amt, tblServicesProvided.PTLRegWages,
tblServicesProvided.PTLPDO, tblServicesProvided.PQARegWages,
tblServicesProvided.FICA, tblServicesProvided.TaxOther,
tblServicesProvided.WComp, tblServicesProvided.GroupIns,
tblServicesProvided.LiabilityIns, tblServicesProvided.Match401K,
tblServicesProvided.Travel, tblServicesProvided.LocalOfficeEx,
tblServicesProvided.COOverhead, tblServicesProvided.CorpAdmExp,
tblServicesProvided.TotDirectExp, tblServicesProvided.TotIndExp,
tblServicesProvided.TotExpense, tblServicesProvided.TotNonUCRClaim,
tblServicesProvided.[PRCSS-Reg], tblServicesProvided.[PRCSS-PDO],
tblServicesProvided.NonCapExp
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
tblServicesProvided.PTLRegWages, tblServicesProvided.PTLPDO,
tblServicesProvided.PQARegWages, tblServicesProvided.FICA,
tblServicesProvided.TaxOther, tblServicesProvided.WComp,
tblServicesProvided.GroupIns, tblServicesProvided.LiabilityIns,
tblServicesProvided.Match401K, tblServicesProvided.Travel,
tblServicesProvided.LocalOfficeEx, tblServicesProvided.COOverhead,
tblServicesProvided.CorpAdmExp, tblServicesProvided.TotDirectExp,
tblServicesProvided.TotIndExp, tblServicesProvided.TotExpense,
tblServicesProvided.TotNonUCRClaim, tblServicesProvided.[PRCSS-Reg],
tblServicesProvided.[PRCSS-PDO], tblServicesProvided.NonCapExp;

--
Nona


:

Can you provide all of your SQL view for the query? It might help to get a
better understanding of your records and full specs for the query.
--
Duane Hookom
Microsoft Access MVP


:

Thanks for replying. And especially for the tip about the 3rd argument. I
fixed that right away.

However, the AllMthsInv formula doesn't give me what I need. It gives the
result per record for all the months.

The query expression I gave as an example calculates all the invoices that
were made in July, so that I have a total that were invoiced for July.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0)
The next step is to get a total for the year.

I don't understand why the query won't total the amounts found in the
expressions, as in:
[JulyInv]+[AugInv]+[SepInv], etc., using the names in each month's expression.


--
Nona


:

It looks like you could use a crosstab query rather than a bunch of similar
expressions. If you want to include all months, why not just use:
AllMthsInv: [ServCodeRate]*[UnitsUsed]
Your IIf()s are also missing the 3rd argument which I assume should be
either 0 or Null.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed], Null)

--
Duane Hookom
Microsoft Access MVP


:

I have a query that calculates the amount invoiced each month in a query:
For Instance:
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed])
AugInv: IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed])


Could someone tell me how can I get the query to give me a total for all the
months?

I tried this expression but it didn’t work: YearAmt: Sum([JulInv]+[AugInv])

Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your help. I've never done a crosstab query, so I'll need to spend
some time working on this...

Thanks again!
--
Nona


Duane Hookom said:
If you haven't tried crosstab queries, enter this into the SQL view of a new
query:

TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) as TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum([ServCodeRate]*[UnitsUsed]) as TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth;

--
Duane Hookom
Microsoft Access MVP


Nona said:
Yes, the query works. I did get that error message once. After playing with
it a bit, I found that if I did not include a checkbox field in the query
(called CostSettled), it worked fine and has ever since. I would like to know
why the query would not accept the checkbox field. I've used it in other
queries with no problem.

How else would I set the db up without using the field names as services?
You're probably mean the Human Resource fields for payroll etc. This database
is intended to handle contracts and to monitor the costs involved in the
provision of services.

The HR fields are entered monthly as integers that come via the HR dept. All
Access does is to print those on the report (invoice) when it is billed to
the contractor. There are no calculations with those fields. Some contracts
use these fields; others do not. More importantly, the contract itself is
included in the database as projected income.

It's a little disturbing to have you say the db is poorly designed, though I
am far from being an expert. I will certainly appreciate any suggestions you
can recommend.

Can you explain why I can't get the totals I need?

With much appreciation for your time and expertise,


--
Nona


Duane Hookom said:
I'm surprised the query doesn't error with a message about your IIf()
expressions not being part of an aggregate. They should either be in a Sum,
Avg, etc or in the Group By.

Are you sure your query works?

BTW: Your table structure is highly un-normalized. You have services as
field names. If you want to add another service, it looks like you will need
to create another field. This is generally considered poor design.

--
Duane Hookom
Microsoft Access MVP


:

Yes, here it is. I will certainly appreciate any suggestions!

SELECT tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0) AS JulInv,
IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed],0) AS AugInv,
IIf([CalMonth]="September",[ServCodeRate]*[UnitsUsed],0) AS SepInv,
IIf([CalMonth]="October",[ServCodeRate]*[UnitsUsed],0) AS OctInv,
IIf([CalMonth]="November",[ServCodeRate]*[UnitsUsed],0) AS NovInv,
IIf([CalMonth]="December",[ServCodeRate]*[UnitsUsed],0) AS DecInv,
IIf([CalMonth]="January",[ServCodeRate]*[UnitsUsed],0) AS JanInv,
IIf([CalMonth]="February",[ServCodeRate]*[UnitsUsed],0) AS FebInv,
IIf([CalMonth]="March",[ServCodeRate]*[UnitsUsed],0) AS MarInv,
IIf([CalMonth]="April",[ServCodeRate]*[UnitsUsed],0) AS AprInv,
IIf([CalMonth]="May",[ServCodeRate]*[UnitsUsed],0) AS MayInv,
IIf([CalMonth]="June",[ServCodeRate]*[UnitsUsed],0) AS JunInv,
Sum([JulInv]+[AugInv]) AS Amt, tblServicesProvided.PTLRegWages,
tblServicesProvided.PTLPDO, tblServicesProvided.PQARegWages,
tblServicesProvided.FICA, tblServicesProvided.TaxOther,
tblServicesProvided.WComp, tblServicesProvided.GroupIns,
tblServicesProvided.LiabilityIns, tblServicesProvided.Match401K,
tblServicesProvided.Travel, tblServicesProvided.LocalOfficeEx,
tblServicesProvided.COOverhead, tblServicesProvided.CorpAdmExp,
tblServicesProvided.TotDirectExp, tblServicesProvided.TotIndExp,
tblServicesProvided.TotExpense, tblServicesProvided.TotNonUCRClaim,
tblServicesProvided.[PRCSS-Reg], tblServicesProvided.[PRCSS-PDO],
tblServicesProvided.NonCapExp
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
tblServicesProvided.PTLRegWages, tblServicesProvided.PTLPDO,
tblServicesProvided.PQARegWages, tblServicesProvided.FICA,
tblServicesProvided.TaxOther, tblServicesProvided.WComp,
tblServicesProvided.GroupIns, tblServicesProvided.LiabilityIns,
tblServicesProvided.Match401K, tblServicesProvided.Travel,
tblServicesProvided.LocalOfficeEx, tblServicesProvided.COOverhead,
tblServicesProvided.CorpAdmExp, tblServicesProvided.TotDirectExp,
tblServicesProvided.TotIndExp, tblServicesProvided.TotExpense,
tblServicesProvided.TotNonUCRClaim, tblServicesProvided.[PRCSS-Reg],
tblServicesProvided.[PRCSS-PDO], tblServicesProvided.NonCapExp;

--
Nona


:

Can you provide all of your SQL view for the query? It might help to get a
better understanding of your records and full specs for the query.
--
Duane Hookom
Microsoft Access MVP


:

Thanks for replying. And especially for the tip about the 3rd argument. I
fixed that right away.

However, the AllMthsInv formula doesn't give me what I need. It gives the
result per record for all the months.

The query expression I gave as an example calculates all the invoices that
were made in July, so that I have a total that were invoiced for July.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0)
The next step is to get a total for the year.

I don't understand why the query won't total the amounts found in the
expressions, as in:
[JulyInv]+[AugInv]+[SepInv], etc., using the names in each month's expression.


--
Nona


:

It looks like you could use a crosstab query rather than a bunch of similar
expressions. If you want to include all months, why not just use:
AllMthsInv: [ServCodeRate]*[UnitsUsed]
Your IIf()s are also missing the 3rd argument which I assume should be
either 0 or Null.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed], Null)

--
Duane Hookom
Microsoft Access MVP


:

I have a query that calculates the amount invoiced each month in a query:
For Instance:
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed])
AugInv: IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed])


Could someone tell me how can I get the query to give me a total for all the
months?

I tried this expression but it didn’t work: YearAmt: Sum([JulInv]+[AugInv])

Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
Did you try the SQL I provided?

--
Duane Hookom
Microsoft Access MVP


Nona said:
Thanks for your help. I've never done a crosstab query, so I'll need to spend
some time working on this...

Thanks again!
--
Nona


Duane Hookom said:
If you haven't tried crosstab queries, enter this into the SQL view of a new
query:

TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) as TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum([ServCodeRate]*[UnitsUsed]) as TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth;

--
Duane Hookom
Microsoft Access MVP


Nona said:
Yes, the query works. I did get that error message once. After playing with
it a bit, I found that if I did not include a checkbox field in the query
(called CostSettled), it worked fine and has ever since. I would like to know
why the query would not accept the checkbox field. I've used it in other
queries with no problem.

How else would I set the db up without using the field names as services?
You're probably mean the Human Resource fields for payroll etc. This database
is intended to handle contracts and to monitor the costs involved in the
provision of services.

The HR fields are entered monthly as integers that come via the HR dept. All
Access does is to print those on the report (invoice) when it is billed to
the contractor. There are no calculations with those fields. Some contracts
use these fields; others do not. More importantly, the contract itself is
included in the database as projected income.

It's a little disturbing to have you say the db is poorly designed, though I
am far from being an expert. I will certainly appreciate any suggestions you
can recommend.

Can you explain why I can't get the totals I need?

With much appreciation for your time and expertise,


--
Nona


:

I'm surprised the query doesn't error with a message about your IIf()
expressions not being part of an aggregate. They should either be in a Sum,
Avg, etc or in the Group By.

Are you sure your query works?

BTW: Your table structure is highly un-normalized. You have services as
field names. If you want to add another service, it looks like you will need
to create another field. This is generally considered poor design.

--
Duane Hookom
Microsoft Access MVP


:

Yes, here it is. I will certainly appreciate any suggestions!

SELECT tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0) AS JulInv,
IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed],0) AS AugInv,
IIf([CalMonth]="September",[ServCodeRate]*[UnitsUsed],0) AS SepInv,
IIf([CalMonth]="October",[ServCodeRate]*[UnitsUsed],0) AS OctInv,
IIf([CalMonth]="November",[ServCodeRate]*[UnitsUsed],0) AS NovInv,
IIf([CalMonth]="December",[ServCodeRate]*[UnitsUsed],0) AS DecInv,
IIf([CalMonth]="January",[ServCodeRate]*[UnitsUsed],0) AS JanInv,
IIf([CalMonth]="February",[ServCodeRate]*[UnitsUsed],0) AS FebInv,
IIf([CalMonth]="March",[ServCodeRate]*[UnitsUsed],0) AS MarInv,
IIf([CalMonth]="April",[ServCodeRate]*[UnitsUsed],0) AS AprInv,
IIf([CalMonth]="May",[ServCodeRate]*[UnitsUsed],0) AS MayInv,
IIf([CalMonth]="June",[ServCodeRate]*[UnitsUsed],0) AS JunInv,
Sum([JulInv]+[AugInv]) AS Amt, tblServicesProvided.PTLRegWages,
tblServicesProvided.PTLPDO, tblServicesProvided.PQARegWages,
tblServicesProvided.FICA, tblServicesProvided.TaxOther,
tblServicesProvided.WComp, tblServicesProvided.GroupIns,
tblServicesProvided.LiabilityIns, tblServicesProvided.Match401K,
tblServicesProvided.Travel, tblServicesProvided.LocalOfficeEx,
tblServicesProvided.COOverhead, tblServicesProvided.CorpAdmExp,
tblServicesProvided.TotDirectExp, tblServicesProvided.TotIndExp,
tblServicesProvided.TotExpense, tblServicesProvided.TotNonUCRClaim,
tblServicesProvided.[PRCSS-Reg], tblServicesProvided.[PRCSS-PDO],
tblServicesProvided.NonCapExp
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
tblServicesProvided.PTLRegWages, tblServicesProvided.PTLPDO,
tblServicesProvided.PQARegWages, tblServicesProvided.FICA,
tblServicesProvided.TaxOther, tblServicesProvided.WComp,
tblServicesProvided.GroupIns, tblServicesProvided.LiabilityIns,
tblServicesProvided.Match401K, tblServicesProvided.Travel,
tblServicesProvided.LocalOfficeEx, tblServicesProvided.COOverhead,
tblServicesProvided.CorpAdmExp, tblServicesProvided.TotDirectExp,
tblServicesProvided.TotIndExp, tblServicesProvided.TotExpense,
tblServicesProvided.TotNonUCRClaim, tblServicesProvided.[PRCSS-Reg],
tblServicesProvided.[PRCSS-PDO], tblServicesProvided.NonCapExp;

--
Nona


:

Can you provide all of your SQL view for the query? It might help to get a
better understanding of your records and full specs for the query.
--
Duane Hookom
Microsoft Access MVP


:

Thanks for replying. And especially for the tip about the 3rd argument. I
fixed that right away.

However, the AllMthsInv formula doesn't give me what I need. It gives the
result per record for all the months.

The query expression I gave as an example calculates all the invoices that
were made in July, so that I have a total that were invoiced for July.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0)
The next step is to get a total for the year.

I don't understand why the query won't total the amounts found in the
expressions, as in:
[JulyInv]+[AugInv]+[SepInv], etc., using the names in each month's expression.


--
Nona


:

It looks like you could use a crosstab query rather than a bunch of similar
expressions. If you want to include all months, why not just use:
AllMthsInv: [ServCodeRate]*[UnitsUsed]
Your IIf()s are also missing the 3rd argument which I assume should be
either 0 or Null.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed], Null)

--
Duane Hookom
Microsoft Access MVP


:

I have a query that calculates the amount invoiced each month in a query:
For Instance:
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed])
AugInv: IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed])


Could someone tell me how can I get the query to give me a total for all the
months?

I tried this expression but it didn’t work: YearAmt: Sum([JulInv]+[AugInv])

Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
Not yet, but I will! I'm eager to try it, but it may be next week before I
can get immersed in the db again due to a lot of family issues - a death,
then home-comings, etc this weekend. May have a window of time tonight. I'll
let you know.

--
Nona


Duane Hookom said:
Did you try the SQL I provided?

--
Duane Hookom
Microsoft Access MVP


Nona said:
Thanks for your help. I've never done a crosstab query, so I'll need to spend
some time working on this...

Thanks again!
--
Nona


Duane Hookom said:
If you haven't tried crosstab queries, enter this into the SQL view of a new
query:

TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) as TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum([ServCodeRate]*[UnitsUsed]) as TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth;

--
Duane Hookom
Microsoft Access MVP


:


Yes, the query works. I did get that error message once. After playing with
it a bit, I found that if I did not include a checkbox field in the query
(called CostSettled), it worked fine and has ever since. I would like to know
why the query would not accept the checkbox field. I've used it in other
queries with no problem.

How else would I set the db up without using the field names as services?
You're probably mean the Human Resource fields for payroll etc. This database
is intended to handle contracts and to monitor the costs involved in the
provision of services.

The HR fields are entered monthly as integers that come via the HR dept. All
Access does is to print those on the report (invoice) when it is billed to
the contractor. There are no calculations with those fields. Some contracts
use these fields; others do not. More importantly, the contract itself is
included in the database as projected income.

It's a little disturbing to have you say the db is poorly designed, though I
am far from being an expert. I will certainly appreciate any suggestions you
can recommend.

Can you explain why I can't get the totals I need?

With much appreciation for your time and expertise,


--
Nona


:

I'm surprised the query doesn't error with a message about your IIf()
expressions not being part of an aggregate. They should either be in a Sum,
Avg, etc or in the Group By.

Are you sure your query works?

BTW: Your table structure is highly un-normalized. You have services as
field names. If you want to add another service, it looks like you will need
to create another field. This is generally considered poor design.

--
Duane Hookom
Microsoft Access MVP


:

Yes, here it is. I will certainly appreciate any suggestions!

SELECT tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0) AS JulInv,
IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed],0) AS AugInv,
IIf([CalMonth]="September",[ServCodeRate]*[UnitsUsed],0) AS SepInv,
IIf([CalMonth]="October",[ServCodeRate]*[UnitsUsed],0) AS OctInv,
IIf([CalMonth]="November",[ServCodeRate]*[UnitsUsed],0) AS NovInv,
IIf([CalMonth]="December",[ServCodeRate]*[UnitsUsed],0) AS DecInv,
IIf([CalMonth]="January",[ServCodeRate]*[UnitsUsed],0) AS JanInv,
IIf([CalMonth]="February",[ServCodeRate]*[UnitsUsed],0) AS FebInv,
IIf([CalMonth]="March",[ServCodeRate]*[UnitsUsed],0) AS MarInv,
IIf([CalMonth]="April",[ServCodeRate]*[UnitsUsed],0) AS AprInv,
IIf([CalMonth]="May",[ServCodeRate]*[UnitsUsed],0) AS MayInv,
IIf([CalMonth]="June",[ServCodeRate]*[UnitsUsed],0) AS JunInv,
Sum([JulInv]+[AugInv]) AS Amt, tblServicesProvided.PTLRegWages,
tblServicesProvided.PTLPDO, tblServicesProvided.PQARegWages,
tblServicesProvided.FICA, tblServicesProvided.TaxOther,
tblServicesProvided.WComp, tblServicesProvided.GroupIns,
tblServicesProvided.LiabilityIns, tblServicesProvided.Match401K,
tblServicesProvided.Travel, tblServicesProvided.LocalOfficeEx,
tblServicesProvided.COOverhead, tblServicesProvided.CorpAdmExp,
tblServicesProvided.TotDirectExp, tblServicesProvided.TotIndExp,
tblServicesProvided.TotExpense, tblServicesProvided.TotNonUCRClaim,
tblServicesProvided.[PRCSS-Reg], tblServicesProvided.[PRCSS-PDO],
tblServicesProvided.NonCapExp
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
tblServicesProvided.PTLRegWages, tblServicesProvided.PTLPDO,
tblServicesProvided.PQARegWages, tblServicesProvided.FICA,
tblServicesProvided.TaxOther, tblServicesProvided.WComp,
tblServicesProvided.GroupIns, tblServicesProvided.LiabilityIns,
tblServicesProvided.Match401K, tblServicesProvided.Travel,
tblServicesProvided.LocalOfficeEx, tblServicesProvided.COOverhead,
tblServicesProvided.CorpAdmExp, tblServicesProvided.TotDirectExp,
tblServicesProvided.TotIndExp, tblServicesProvided.TotExpense,
tblServicesProvided.TotNonUCRClaim, tblServicesProvided.[PRCSS-Reg],
tblServicesProvided.[PRCSS-PDO], tblServicesProvided.NonCapExp;

--
Nona


:

Can you provide all of your SQL view for the query? It might help to get a
better understanding of your records and full specs for the query.
--
Duane Hookom
Microsoft Access MVP


:

Thanks for replying. And especially for the tip about the 3rd argument. I
fixed that right away.

However, the AllMthsInv formula doesn't give me what I need. It gives the
result per record for all the months.

The query expression I gave as an example calculates all the invoices that
were made in July, so that I have a total that were invoiced for July.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0)
The next step is to get a total for the year.

I don't understand why the query won't total the amounts found in the
expressions, as in:
[JulyInv]+[AugInv]+[SepInv], etc., using the names in each month's expression.


--
Nona


:

It looks like you could use a crosstab query rather than a bunch of similar
expressions. If you want to include all months, why not just use:
AllMthsInv: [ServCodeRate]*[UnitsUsed]
Your IIf()s are also missing the 3rd argument which I assume should be
either 0 or Null.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed], Null)

--
Duane Hookom
Microsoft Access MVP


:

I have a query that calculates the amount invoiced each month in a query:
For Instance:
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed])
AugInv: IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed])


Could someone tell me how can I get the query to give me a total for all the
months?

I tried this expression but it didn’t work: YearAmt: Sum([JulInv]+[AugInv])

Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
Wow! This is amazing and wonderful
I will really have to study this because I don't understand how you did
this, but it's great. I added an invoice for Sept, and it added it to the
query as I hoped it would.

Couple of things: This gives me the Tot Inv for each contract. How can I
get the total of all contracts invoiced for a single month?

And is there a way to list all 12 months in a column on a form and show the
total for each?

Also, can I format the amts to 2 decimal points?

Thank you SO VERY MUCH! I am learning a lot through this process.

qryCrossTabExperiment
InvType ContractName TotInv August July September
0 0
Cost Invoice Asheville Jail Diversion 0 0
UCR Buncombe Detention Facility 30117.53 19139.52 10978.01
UCR Buncombe Health Center & Pretrial 22205.8875 11634.115 10271.7725 300
UCR with Provider Psych Support 2195 2195

--
Nona


Nona said:
Not yet, but I will! I'm eager to try it, but it may be next week before I
can get immersed in the db again due to a lot of family issues - a death,
then home-comings, etc this weekend. May have a window of time tonight. I'll
let you know.

--
Nona


Duane Hookom said:
Did you try the SQL I provided?

--
Duane Hookom
Microsoft Access MVP


Nona said:
Thanks for your help. I've never done a crosstab query, so I'll need to spend
some time working on this...

Thanks again!
--
Nona


:

If you haven't tried crosstab queries, enter this into the SQL view of a new
query:

TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) as TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum([ServCodeRate]*[UnitsUsed]) as TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth;

--
Duane Hookom
Microsoft Access MVP


:


Yes, the query works. I did get that error message once. After playing with
it a bit, I found that if I did not include a checkbox field in the query
(called CostSettled), it worked fine and has ever since. I would like to know
why the query would not accept the checkbox field. I've used it in other
queries with no problem.

How else would I set the db up without using the field names as services?
You're probably mean the Human Resource fields for payroll etc. This database
is intended to handle contracts and to monitor the costs involved in the
provision of services.

The HR fields are entered monthly as integers that come via the HR dept. All
Access does is to print those on the report (invoice) when it is billed to
the contractor. There are no calculations with those fields. Some contracts
use these fields; others do not. More importantly, the contract itself is
included in the database as projected income.

It's a little disturbing to have you say the db is poorly designed, though I
am far from being an expert. I will certainly appreciate any suggestions you
can recommend.

Can you explain why I can't get the totals I need?

With much appreciation for your time and expertise,


--
Nona


:

I'm surprised the query doesn't error with a message about your IIf()
expressions not being part of an aggregate. They should either be in a Sum,
Avg, etc or in the Group By.

Are you sure your query works?

BTW: Your table structure is highly un-normalized. You have services as
field names. If you want to add another service, it looks like you will need
to create another field. This is generally considered poor design.

--
Duane Hookom
Microsoft Access MVP


:

Yes, here it is. I will certainly appreciate any suggestions!

SELECT tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0) AS JulInv,
IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed],0) AS AugInv,
IIf([CalMonth]="September",[ServCodeRate]*[UnitsUsed],0) AS SepInv,
IIf([CalMonth]="October",[ServCodeRate]*[UnitsUsed],0) AS OctInv,
IIf([CalMonth]="November",[ServCodeRate]*[UnitsUsed],0) AS NovInv,
IIf([CalMonth]="December",[ServCodeRate]*[UnitsUsed],0) AS DecInv,
IIf([CalMonth]="January",[ServCodeRate]*[UnitsUsed],0) AS JanInv,
IIf([CalMonth]="February",[ServCodeRate]*[UnitsUsed],0) AS FebInv,
IIf([CalMonth]="March",[ServCodeRate]*[UnitsUsed],0) AS MarInv,
IIf([CalMonth]="April",[ServCodeRate]*[UnitsUsed],0) AS AprInv,
IIf([CalMonth]="May",[ServCodeRate]*[UnitsUsed],0) AS MayInv,
IIf([CalMonth]="June",[ServCodeRate]*[UnitsUsed],0) AS JunInv,
Sum([JulInv]+[AugInv]) AS Amt, tblServicesProvided.PTLRegWages,
tblServicesProvided.PTLPDO, tblServicesProvided.PQARegWages,
tblServicesProvided.FICA, tblServicesProvided.TaxOther,
tblServicesProvided.WComp, tblServicesProvided.GroupIns,
tblServicesProvided.LiabilityIns, tblServicesProvided.Match401K,
tblServicesProvided.Travel, tblServicesProvided.LocalOfficeEx,
tblServicesProvided.COOverhead, tblServicesProvided.CorpAdmExp,
tblServicesProvided.TotDirectExp, tblServicesProvided.TotIndExp,
tblServicesProvided.TotExpense, tblServicesProvided.TotNonUCRClaim,
tblServicesProvided.[PRCSS-Reg], tblServicesProvided.[PRCSS-PDO],
tblServicesProvided.NonCapExp
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
tblServicesProvided.PTLRegWages, tblServicesProvided.PTLPDO,
tblServicesProvided.PQARegWages, tblServicesProvided.FICA,
tblServicesProvided.TaxOther, tblServicesProvided.WComp,
tblServicesProvided.GroupIns, tblServicesProvided.LiabilityIns,
tblServicesProvided.Match401K, tblServicesProvided.Travel,
tblServicesProvided.LocalOfficeEx, tblServicesProvided.COOverhead,
tblServicesProvided.CorpAdmExp, tblServicesProvided.TotDirectExp,
tblServicesProvided.TotIndExp, tblServicesProvided.TotExpense,
tblServicesProvided.TotNonUCRClaim, tblServicesProvided.[PRCSS-Reg],
tblServicesProvided.[PRCSS-PDO], tblServicesProvided.NonCapExp;

--
Nona


:

Can you provide all of your SQL view for the query? It might help to get a
better understanding of your records and full specs for the query.
--
Duane Hookom
Microsoft Access MVP


:

Thanks for replying. And especially for the tip about the 3rd argument. I
fixed that right away.

However, the AllMthsInv formula doesn't give me what I need. It gives the
result per record for all the months.

The query expression I gave as an example calculates all the invoices that
were made in July, so that I have a total that were invoiced for July.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0)
The next step is to get a total for the year.

I don't understand why the query won't total the amounts found in the
expressions, as in:
[JulyInv]+[AugInv]+[SepInv], etc., using the names in each month's expression.


--
Nona


:

It looks like you could use a crosstab query rather than a bunch of similar
expressions. If you want to include all months, why not just use:
AllMthsInv: [ServCodeRate]*[UnitsUsed]
Your IIf()s are also missing the 3rd argument which I assume should be
either 0 or Null.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed], Null)

--
Duane Hookom
Microsoft Access MVP


:

I have a query that calculates the amount invoiced each month in a query:
For Instance:
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed])
AugInv: IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed])


Could someone tell me how can I get the query to give me a total for all the
months?

I tried this expression but it didn’t work: YearAmt: Sum([JulInv]+[AugInv])

Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
I should have also suggested the formatting of the values should be done in
controls on forms and reports.
--
Duane Hookom
Microsoft Access MVP


Nona said:
Wow! This is amazing and wonderful
I will really have to study this because I don't understand how you did
this, but it's great. I added an invoice for Sept, and it added it to the
query as I hoped it would.

Couple of things: This gives me the Tot Inv for each contract. How can I
get the total of all contracts invoiced for a single month?

And is there a way to list all 12 months in a column on a form and show the
total for each?

Also, can I format the amts to 2 decimal points?

Thank you SO VERY MUCH! I am learning a lot through this process.

qryCrossTabExperiment
InvType ContractName TotInv August July September
0 0
Cost Invoice Asheville Jail Diversion 0 0
UCR Buncombe Detention Facility 30117.53 19139.52 10978.01
UCR Buncombe Health Center & Pretrial 22205.8875 11634.115 10271.7725 300
UCR with Provider Psych Support 2195 2195

--
Nona


Nona said:
Not yet, but I will! I'm eager to try it, but it may be next week before I
can get immersed in the db again due to a lot of family issues - a death,
then home-comings, etc this weekend. May have a window of time tonight. I'll
let you know.

--
Nona


Duane Hookom said:
Did you try the SQL I provided?

--
Duane Hookom
Microsoft Access MVP


:

Thanks for your help. I've never done a crosstab query, so I'll need to spend
some time working on this...

Thanks again!
--
Nona


:

If you haven't tried crosstab queries, enter this into the SQL view of a new
query:

TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) as TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum([ServCodeRate]*[UnitsUsed]) as TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth;

--
Duane Hookom
Microsoft Access MVP


:


Yes, the query works. I did get that error message once. After playing with
it a bit, I found that if I did not include a checkbox field in the query
(called CostSettled), it worked fine and has ever since. I would like to know
why the query would not accept the checkbox field. I've used it in other
queries with no problem.

How else would I set the db up without using the field names as services?
You're probably mean the Human Resource fields for payroll etc. This database
is intended to handle contracts and to monitor the costs involved in the
provision of services.

The HR fields are entered monthly as integers that come via the HR dept. All
Access does is to print those on the report (invoice) when it is billed to
the contractor. There are no calculations with those fields. Some contracts
use these fields; others do not. More importantly, the contract itself is
included in the database as projected income.

It's a little disturbing to have you say the db is poorly designed, though I
am far from being an expert. I will certainly appreciate any suggestions you
can recommend.

Can you explain why I can't get the totals I need?

With much appreciation for your time and expertise,


--
Nona


:

I'm surprised the query doesn't error with a message about your IIf()
expressions not being part of an aggregate. They should either be in a Sum,
Avg, etc or in the Group By.

Are you sure your query works?

BTW: Your table structure is highly un-normalized. You have services as
field names. If you want to add another service, it looks like you will need
to create another field. This is generally considered poor design.

--
Duane Hookom
Microsoft Access MVP


:

Yes, here it is. I will certainly appreciate any suggestions!

SELECT tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0) AS JulInv,
IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed],0) AS AugInv,
IIf([CalMonth]="September",[ServCodeRate]*[UnitsUsed],0) AS SepInv,
IIf([CalMonth]="October",[ServCodeRate]*[UnitsUsed],0) AS OctInv,
IIf([CalMonth]="November",[ServCodeRate]*[UnitsUsed],0) AS NovInv,
IIf([CalMonth]="December",[ServCodeRate]*[UnitsUsed],0) AS DecInv,
IIf([CalMonth]="January",[ServCodeRate]*[UnitsUsed],0) AS JanInv,
IIf([CalMonth]="February",[ServCodeRate]*[UnitsUsed],0) AS FebInv,
IIf([CalMonth]="March",[ServCodeRate]*[UnitsUsed],0) AS MarInv,
IIf([CalMonth]="April",[ServCodeRate]*[UnitsUsed],0) AS AprInv,
IIf([CalMonth]="May",[ServCodeRate]*[UnitsUsed],0) AS MayInv,
IIf([CalMonth]="June",[ServCodeRate]*[UnitsUsed],0) AS JunInv,
Sum([JulInv]+[AugInv]) AS Amt, tblServicesProvided.PTLRegWages,
tblServicesProvided.PTLPDO, tblServicesProvided.PQARegWages,
tblServicesProvided.FICA, tblServicesProvided.TaxOther,
tblServicesProvided.WComp, tblServicesProvided.GroupIns,
tblServicesProvided.LiabilityIns, tblServicesProvided.Match401K,
tblServicesProvided.Travel, tblServicesProvided.LocalOfficeEx,
tblServicesProvided.COOverhead, tblServicesProvided.CorpAdmExp,
tblServicesProvided.TotDirectExp, tblServicesProvided.TotIndExp,
tblServicesProvided.TotExpense, tblServicesProvided.TotNonUCRClaim,
tblServicesProvided.[PRCSS-Reg], tblServicesProvided.[PRCSS-PDO],
tblServicesProvided.NonCapExp
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
tblServicesProvided.PTLRegWages, tblServicesProvided.PTLPDO,
tblServicesProvided.PQARegWages, tblServicesProvided.FICA,
tblServicesProvided.TaxOther, tblServicesProvided.WComp,
tblServicesProvided.GroupIns, tblServicesProvided.LiabilityIns,
tblServicesProvided.Match401K, tblServicesProvided.Travel,
tblServicesProvided.LocalOfficeEx, tblServicesProvided.COOverhead,
tblServicesProvided.CorpAdmExp, tblServicesProvided.TotDirectExp,
tblServicesProvided.TotIndExp, tblServicesProvided.TotExpense,
tblServicesProvided.TotNonUCRClaim, tblServicesProvided.[PRCSS-Reg],
tblServicesProvided.[PRCSS-PDO], tblServicesProvided.NonCapExp;

--
Nona


:

Can you provide all of your SQL view for the query? It might help to get a
better understanding of your records and full specs for the query.
--
Duane Hookom
Microsoft Access MVP


:

Thanks for replying. And especially for the tip about the 3rd argument. I
fixed that right away.

However, the AllMthsInv formula doesn't give me what I need. It gives the
result per record for all the months.

The query expression I gave as an example calculates all the invoices that
were made in July, so that I have a total that were invoiced for July.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0)
The next step is to get a total for the year.

I don't understand why the query won't total the amounts found in the
expressions, as in:
[JulyInv]+[AugInv]+[SepInv], etc., using the names in each month's expression.


--
Nona


:

It looks like you could use a crosstab query rather than a bunch of similar
expressions. If you want to include all months, why not just use:
AllMthsInv: [ServCodeRate]*[UnitsUsed]
Your IIf()s are also missing the 3rd argument which I assume should be
either 0 or Null.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed], Null)

--
Duane Hookom
Microsoft Access MVP


:

I have a query that calculates the amount invoiced each month in a query:
For Instance:
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed])
AugInv: IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed])


Could someone tell me how can I get the query to give me a total for all the
months?

I tried this expression but it didn’t work: YearAmt: Sum([JulInv]+[AugInv])

Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
This gives me the Tot Inv for each contract. How can I get the total of all
contracts invoiced for a single month?
I believe you would create a report of all contracts and total them in the
report footer. If you want to do this with a crosstab, take out the contracts:

TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) as TheValue
SELECT Sum([ServCodeRate]*[UnitsUsed]) as TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
PIVOT tblServicesProvided.CalMonth;

You can get all months by specifying the Column Headings property:
PIVOT tblServicesProvided.CalMonth IN
("January","February","March",...."December");

I don't know where your CalMonth comes from but I would never store a month
name since it has very little data functionality. I usually store full dates
or month numbers ie: 1 for Jan, 2 for Feb, etc.



--
Duane Hookom
Microsoft Access MVP


Nona said:
Wow! This is amazing and wonderful
I will really have to study this because I don't understand how you did
this, but it's great. I added an invoice for Sept, and it added it to the
query as I hoped it would.

Couple of things: This gives me the Tot Inv for each contract. How can I
get the total of all contracts invoiced for a single month?

And is there a way to list all 12 months in a column on a form and show the
total for each?

Also, can I format the amts to 2 decimal points?

Thank you SO VERY MUCH! I am learning a lot through this process.

qryCrossTabExperiment
InvType ContractName TotInv August July September
0 0
Cost Invoice Asheville Jail Diversion 0 0
UCR Buncombe Detention Facility 30117.53 19139.52 10978.01
UCR Buncombe Health Center & Pretrial 22205.8875 11634.115 10271.7725 300
UCR with Provider Psych Support 2195 2195

--
Nona


Nona said:
Not yet, but I will! I'm eager to try it, but it may be next week before I
can get immersed in the db again due to a lot of family issues - a death,
then home-comings, etc this weekend. May have a window of time tonight. I'll
let you know.

--
Nona


Duane Hookom said:
Did you try the SQL I provided?

--
Duane Hookom
Microsoft Access MVP


:

Thanks for your help. I've never done a crosstab query, so I'll need to spend
some time working on this...

Thanks again!
--
Nona


:

If you haven't tried crosstab queries, enter this into the SQL view of a new
query:

TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) as TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum([ServCodeRate]*[UnitsUsed]) as TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth;

--
Duane Hookom
Microsoft Access MVP


:


Yes, the query works. I did get that error message once. After playing with
it a bit, I found that if I did not include a checkbox field in the query
(called CostSettled), it worked fine and has ever since. I would like to know
why the query would not accept the checkbox field. I've used it in other
queries with no problem.

How else would I set the db up without using the field names as services?
You're probably mean the Human Resource fields for payroll etc. This database
is intended to handle contracts and to monitor the costs involved in the
provision of services.

The HR fields are entered monthly as integers that come via the HR dept. All
Access does is to print those on the report (invoice) when it is billed to
the contractor. There are no calculations with those fields. Some contracts
use these fields; others do not. More importantly, the contract itself is
included in the database as projected income.

It's a little disturbing to have you say the db is poorly designed, though I
am far from being an expert. I will certainly appreciate any suggestions you
can recommend.

Can you explain why I can't get the totals I need?

With much appreciation for your time and expertise,


--
Nona


:

I'm surprised the query doesn't error with a message about your IIf()
expressions not being part of an aggregate. They should either be in a Sum,
Avg, etc or in the Group By.

Are you sure your query works?

BTW: Your table structure is highly un-normalized. You have services as
field names. If you want to add another service, it looks like you will need
to create another field. This is generally considered poor design.

--
Duane Hookom
Microsoft Access MVP


:

Yes, here it is. I will certainly appreciate any suggestions!

SELECT tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0) AS JulInv,
IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed],0) AS AugInv,
IIf([CalMonth]="September",[ServCodeRate]*[UnitsUsed],0) AS SepInv,
IIf([CalMonth]="October",[ServCodeRate]*[UnitsUsed],0) AS OctInv,
IIf([CalMonth]="November",[ServCodeRate]*[UnitsUsed],0) AS NovInv,
IIf([CalMonth]="December",[ServCodeRate]*[UnitsUsed],0) AS DecInv,
IIf([CalMonth]="January",[ServCodeRate]*[UnitsUsed],0) AS JanInv,
IIf([CalMonth]="February",[ServCodeRate]*[UnitsUsed],0) AS FebInv,
IIf([CalMonth]="March",[ServCodeRate]*[UnitsUsed],0) AS MarInv,
IIf([CalMonth]="April",[ServCodeRate]*[UnitsUsed],0) AS AprInv,
IIf([CalMonth]="May",[ServCodeRate]*[UnitsUsed],0) AS MayInv,
IIf([CalMonth]="June",[ServCodeRate]*[UnitsUsed],0) AS JunInv,
Sum([JulInv]+[AugInv]) AS Amt, tblServicesProvided.PTLRegWages,
tblServicesProvided.PTLPDO, tblServicesProvided.PQARegWages,
tblServicesProvided.FICA, tblServicesProvided.TaxOther,
tblServicesProvided.WComp, tblServicesProvided.GroupIns,
tblServicesProvided.LiabilityIns, tblServicesProvided.Match401K,
tblServicesProvided.Travel, tblServicesProvided.LocalOfficeEx,
tblServicesProvided.COOverhead, tblServicesProvided.CorpAdmExp,
tblServicesProvided.TotDirectExp, tblServicesProvided.TotIndExp,
tblServicesProvided.TotExpense, tblServicesProvided.TotNonUCRClaim,
tblServicesProvided.[PRCSS-Reg], tblServicesProvided.[PRCSS-PDO],
tblServicesProvided.NonCapExp
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
tblServicesProvided.PTLRegWages, tblServicesProvided.PTLPDO,
tblServicesProvided.PQARegWages, tblServicesProvided.FICA,
tblServicesProvided.TaxOther, tblServicesProvided.WComp,
tblServicesProvided.GroupIns, tblServicesProvided.LiabilityIns,
tblServicesProvided.Match401K, tblServicesProvided.Travel,
tblServicesProvided.LocalOfficeEx, tblServicesProvided.COOverhead,
tblServicesProvided.CorpAdmExp, tblServicesProvided.TotDirectExp,
tblServicesProvided.TotIndExp, tblServicesProvided.TotExpense,
tblServicesProvided.TotNonUCRClaim, tblServicesProvided.[PRCSS-Reg],
tblServicesProvided.[PRCSS-PDO], tblServicesProvided.NonCapExp;

--
Nona


:

Can you provide all of your SQL view for the query? It might help to get a
better understanding of your records and full specs for the query.
--
Duane Hookom
Microsoft Access MVP


:

Thanks for replying. And especially for the tip about the 3rd argument. I
fixed that right away.

However, the AllMthsInv formula doesn't give me what I need. It gives the
result per record for all the months.

The query expression I gave as an example calculates all the invoices that
were made in July, so that I have a total that were invoiced for July.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0)
The next step is to get a total for the year.

I don't understand why the query won't total the amounts found in the
expressions, as in:
[JulyInv]+[AugInv]+[SepInv], etc., using the names in each month's expression.


--
Nona


:

It looks like you could use a crosstab query rather than a bunch of similar
expressions. If you want to include all months, why not just use:
AllMthsInv: [ServCodeRate]*[UnitsUsed]
Your IIf()s are also missing the 3rd argument which I assume should be
either 0 or Null.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed], Null)

--
Duane Hookom
Microsoft Access MVP


:

I have a query that calculates the amount invoiced each month in a query:
For Instance:
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed])
AugInv: IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed])


Could someone tell me how can I get the query to give me a total for all the
months?

I tried this expression but it didn’t work: YearAmt: Sum([JulInv]+[AugInv])

Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
Thank you again for all this.
--
Nona


Duane Hookom said:
I should have also suggested the formatting of the values should be done in
controls on forms and reports.
--
Duane Hookom
Microsoft Access MVP


Nona said:
Wow! This is amazing and wonderful
I will really have to study this because I don't understand how you did
this, but it's great. I added an invoice for Sept, and it added it to the
query as I hoped it would.

Couple of things: This gives me the Tot Inv for each contract. How can I
get the total of all contracts invoiced for a single month?

And is there a way to list all 12 months in a column on a form and show the
total for each?

Also, can I format the amts to 2 decimal points?

Thank you SO VERY MUCH! I am learning a lot through this process.

qryCrossTabExperiment
InvType ContractName TotInv August July September
0 0
Cost Invoice Asheville Jail Diversion 0 0
UCR Buncombe Detention Facility 30117.53 19139.52 10978.01
UCR Buncombe Health Center & Pretrial 22205.8875 11634.115 10271.7725 300
UCR with Provider Psych Support 2195 2195

--
Nona


Nona said:
Not yet, but I will! I'm eager to try it, but it may be next week before I
can get immersed in the db again due to a lot of family issues - a death,
then home-comings, etc this weekend. May have a window of time tonight. I'll
let you know.

--
Nona


:

Did you try the SQL I provided?

--
Duane Hookom
Microsoft Access MVP


:

Thanks for your help. I've never done a crosstab query, so I'll need to spend
some time working on this...

Thanks again!
--
Nona


:

If you haven't tried crosstab queries, enter this into the SQL view of a new
query:

TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) as TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum([ServCodeRate]*[UnitsUsed]) as TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth;

--
Duane Hookom
Microsoft Access MVP


:


Yes, the query works. I did get that error message once. After playing with
it a bit, I found that if I did not include a checkbox field in the query
(called CostSettled), it worked fine and has ever since. I would like to know
why the query would not accept the checkbox field. I've used it in other
queries with no problem.

How else would I set the db up without using the field names as services?
You're probably mean the Human Resource fields for payroll etc. This database
is intended to handle contracts and to monitor the costs involved in the
provision of services.

The HR fields are entered monthly as integers that come via the HR dept. All
Access does is to print those on the report (invoice) when it is billed to
the contractor. There are no calculations with those fields. Some contracts
use these fields; others do not. More importantly, the contract itself is
included in the database as projected income.

It's a little disturbing to have you say the db is poorly designed, though I
am far from being an expert. I will certainly appreciate any suggestions you
can recommend.

Can you explain why I can't get the totals I need?

With much appreciation for your time and expertise,


--
Nona


:

I'm surprised the query doesn't error with a message about your IIf()
expressions not being part of an aggregate. They should either be in a Sum,
Avg, etc or in the Group By.

Are you sure your query works?

BTW: Your table structure is highly un-normalized. You have services as
field names. If you want to add another service, it looks like you will need
to create another field. This is generally considered poor design.

--
Duane Hookom
Microsoft Access MVP


:

Yes, here it is. I will certainly appreciate any suggestions!

SELECT tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0) AS JulInv,
IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed],0) AS AugInv,
IIf([CalMonth]="September",[ServCodeRate]*[UnitsUsed],0) AS SepInv,
IIf([CalMonth]="October",[ServCodeRate]*[UnitsUsed],0) AS OctInv,
IIf([CalMonth]="November",[ServCodeRate]*[UnitsUsed],0) AS NovInv,
IIf([CalMonth]="December",[ServCodeRate]*[UnitsUsed],0) AS DecInv,
IIf([CalMonth]="January",[ServCodeRate]*[UnitsUsed],0) AS JanInv,
IIf([CalMonth]="February",[ServCodeRate]*[UnitsUsed],0) AS FebInv,
IIf([CalMonth]="March",[ServCodeRate]*[UnitsUsed],0) AS MarInv,
IIf([CalMonth]="April",[ServCodeRate]*[UnitsUsed],0) AS AprInv,
IIf([CalMonth]="May",[ServCodeRate]*[UnitsUsed],0) AS MayInv,
IIf([CalMonth]="June",[ServCodeRate]*[UnitsUsed],0) AS JunInv,
Sum([JulInv]+[AugInv]) AS Amt, tblServicesProvided.PTLRegWages,
tblServicesProvided.PTLPDO, tblServicesProvided.PQARegWages,
tblServicesProvided.FICA, tblServicesProvided.TaxOther,
tblServicesProvided.WComp, tblServicesProvided.GroupIns,
tblServicesProvided.LiabilityIns, tblServicesProvided.Match401K,
tblServicesProvided.Travel, tblServicesProvided.LocalOfficeEx,
tblServicesProvided.COOverhead, tblServicesProvided.CorpAdmExp,
tblServicesProvided.TotDirectExp, tblServicesProvided.TotIndExp,
tblServicesProvided.TotExpense, tblServicesProvided.TotNonUCRClaim,
tblServicesProvided.[PRCSS-Reg], tblServicesProvided.[PRCSS-PDO],
tblServicesProvided.NonCapExp
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
tblServicesProvided.PTLRegWages, tblServicesProvided.PTLPDO,
tblServicesProvided.PQARegWages, tblServicesProvided.FICA,
tblServicesProvided.TaxOther, tblServicesProvided.WComp,
tblServicesProvided.GroupIns, tblServicesProvided.LiabilityIns,
tblServicesProvided.Match401K, tblServicesProvided.Travel,
tblServicesProvided.LocalOfficeEx, tblServicesProvided.COOverhead,
tblServicesProvided.CorpAdmExp, tblServicesProvided.TotDirectExp,
tblServicesProvided.TotIndExp, tblServicesProvided.TotExpense,
tblServicesProvided.TotNonUCRClaim, tblServicesProvided.[PRCSS-Reg],
tblServicesProvided.[PRCSS-PDO], tblServicesProvided.NonCapExp;

--
Nona


:

Can you provide all of your SQL view for the query? It might help to get a
better understanding of your records and full specs for the query.
--
Duane Hookom
Microsoft Access MVP


:

Thanks for replying. And especially for the tip about the 3rd argument. I
fixed that right away.

However, the AllMthsInv formula doesn't give me what I need. It gives the
result per record for all the months.

The query expression I gave as an example calculates all the invoices that
were made in July, so that I have a total that were invoiced for July.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0)
The next step is to get a total for the year.

I don't understand why the query won't total the amounts found in the
expressions, as in:
[JulyInv]+[AugInv]+[SepInv], etc., using the names in each month's expression.


--
Nona


:

It looks like you could use a crosstab query rather than a bunch of similar
expressions. If you want to include all months, why not just use:
AllMthsInv: [ServCodeRate]*[UnitsUsed]
Your IIf()s are also missing the 3rd argument which I assume should be
either 0 or Null.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed], Null)

--
Duane Hookom
Microsoft Access MVP


:

I have a query that calculates the amount invoiced each month in a query:
For Instance:
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed])
AugInv: IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed])


Could someone tell me how can I get the query to give me a total for all the
months?

I tried this expression but it didn’t work: YearAmt: Sum([JulInv]+[AugInv])

Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
Your suggestions are working beautifully! I'm really happy. Thank you.

May I ask another questions?
On a form, each month is listed in a column as in:
July JulInv
Aug AugInv...

and at the bottom
TotInv

I figured out that all months can be listed in the column on the form by
creating a dummy contract and a dummy service code with a rate of $0.00.
Fields for all 12 months are now on the form and ready to receive the data.

This works, but, of course, the dummy contract shows up as a contract, which
is not a good thing.

Is there a better way to get all 12 months displayed on the form even if
there is no data yet?

Or is there a way to hide that contract so it doesn't appear on reports and
forms?

(I'm trying to improve at database design!) :)









--
Nona


Nona said:
Thank you again for all this.
--
Nona


Duane Hookom said:
I should have also suggested the formatting of the values should be done in
controls on forms and reports.
--
Duane Hookom
Microsoft Access MVP


Nona said:
Wow! This is amazing and wonderful
I will really have to study this because I don't understand how you did
this, but it's great. I added an invoice for Sept, and it added it to the
query as I hoped it would.

Couple of things: This gives me the Tot Inv for each contract. How can I
get the total of all contracts invoiced for a single month?

And is there a way to list all 12 months in a column on a form and show the
total for each?

Also, can I format the amts to 2 decimal points?

Thank you SO VERY MUCH! I am learning a lot through this process.

qryCrossTabExperiment
InvType ContractName TotInv August July September
0 0
Cost Invoice Asheville Jail Diversion 0 0
UCR Buncombe Detention Facility 30117.53 19139.52 10978.01
UCR Buncombe Health Center & Pretrial 22205.8875 11634.115 10271.7725 300
UCR with Provider Psych Support 2195 2195

--
Nona


:

Not yet, but I will! I'm eager to try it, but it may be next week before I
can get immersed in the db again due to a lot of family issues - a death,
then home-comings, etc this weekend. May have a window of time tonight. I'll
let you know.

--
Nona


:

Did you try the SQL I provided?

--
Duane Hookom
Microsoft Access MVP


:

Thanks for your help. I've never done a crosstab query, so I'll need to spend
some time working on this...

Thanks again!
--
Nona


:

If you haven't tried crosstab queries, enter this into the SQL view of a new
query:

TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) as TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum([ServCodeRate]*[UnitsUsed]) as TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth;

--
Duane Hookom
Microsoft Access MVP


:


Yes, the query works. I did get that error message once. After playing with
it a bit, I found that if I did not include a checkbox field in the query
(called CostSettled), it worked fine and has ever since. I would like to know
why the query would not accept the checkbox field. I've used it in other
queries with no problem.

How else would I set the db up without using the field names as services?
You're probably mean the Human Resource fields for payroll etc. This database
is intended to handle contracts and to monitor the costs involved in the
provision of services.

The HR fields are entered monthly as integers that come via the HR dept. All
Access does is to print those on the report (invoice) when it is billed to
the contractor. There are no calculations with those fields. Some contracts
use these fields; others do not. More importantly, the contract itself is
included in the database as projected income.

It's a little disturbing to have you say the db is poorly designed, though I
am far from being an expert. I will certainly appreciate any suggestions you
can recommend.

Can you explain why I can't get the totals I need?

With much appreciation for your time and expertise,


--
Nona


:

I'm surprised the query doesn't error with a message about your IIf()
expressions not being part of an aggregate. They should either be in a Sum,
Avg, etc or in the Group By.

Are you sure your query works?

BTW: Your table structure is highly un-normalized. You have services as
field names. If you want to add another service, it looks like you will need
to create another field. This is generally considered poor design.

--
Duane Hookom
Microsoft Access MVP


:

Yes, here it is. I will certainly appreciate any suggestions!

SELECT tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0) AS JulInv,
IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed],0) AS AugInv,
IIf([CalMonth]="September",[ServCodeRate]*[UnitsUsed],0) AS SepInv,
IIf([CalMonth]="October",[ServCodeRate]*[UnitsUsed],0) AS OctInv,
IIf([CalMonth]="November",[ServCodeRate]*[UnitsUsed],0) AS NovInv,
IIf([CalMonth]="December",[ServCodeRate]*[UnitsUsed],0) AS DecInv,
IIf([CalMonth]="January",[ServCodeRate]*[UnitsUsed],0) AS JanInv,
IIf([CalMonth]="February",[ServCodeRate]*[UnitsUsed],0) AS FebInv,
IIf([CalMonth]="March",[ServCodeRate]*[UnitsUsed],0) AS MarInv,
IIf([CalMonth]="April",[ServCodeRate]*[UnitsUsed],0) AS AprInv,
IIf([CalMonth]="May",[ServCodeRate]*[UnitsUsed],0) AS MayInv,
IIf([CalMonth]="June",[ServCodeRate]*[UnitsUsed],0) AS JunInv,
Sum([JulInv]+[AugInv]) AS Amt, tblServicesProvided.PTLRegWages,
tblServicesProvided.PTLPDO, tblServicesProvided.PQARegWages,
tblServicesProvided.FICA, tblServicesProvided.TaxOther,
tblServicesProvided.WComp, tblServicesProvided.GroupIns,
tblServicesProvided.LiabilityIns, tblServicesProvided.Match401K,
tblServicesProvided.Travel, tblServicesProvided.LocalOfficeEx,
tblServicesProvided.COOverhead, tblServicesProvided.CorpAdmExp,
tblServicesProvided.TotDirectExp, tblServicesProvided.TotIndExp,
tblServicesProvided.TotExpense, tblServicesProvided.TotNonUCRClaim,
tblServicesProvided.[PRCSS-Reg], tblServicesProvided.[PRCSS-PDO],
tblServicesProvided.NonCapExp
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
tblServicesProvided.PTLRegWages, tblServicesProvided.PTLPDO,
tblServicesProvided.PQARegWages, tblServicesProvided.FICA,
tblServicesProvided.TaxOther, tblServicesProvided.WComp,
tblServicesProvided.GroupIns, tblServicesProvided.LiabilityIns,
tblServicesProvided.Match401K, tblServicesProvided.Travel,
tblServicesProvided.LocalOfficeEx, tblServicesProvided.COOverhead,
tblServicesProvided.CorpAdmExp, tblServicesProvided.TotDirectExp,
tblServicesProvided.TotIndExp, tblServicesProvided.TotExpense,
tblServicesProvided.TotNonUCRClaim, tblServicesProvided.[PRCSS-Reg],
tblServicesProvided.[PRCSS-PDO], tblServicesProvided.NonCapExp;

--
Nona


:

Can you provide all of your SQL view for the query? It might help to get a
better understanding of your records and full specs for the query.
--
Duane Hookom
Microsoft Access MVP


:

Thanks for replying. And especially for the tip about the 3rd argument. I
fixed that right away.

However, the AllMthsInv formula doesn't give me what I need. It gives the
result per record for all the months.

The query expression I gave as an example calculates all the invoices that
were made in July, so that I have a total that were invoiced for July.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0)
The next step is to get a total for the year.

I don't understand why the query won't total the amounts found in the
expressions, as in:
[JulyInv]+[AugInv]+[SepInv], etc., using the names in each month's expression.


--
Nona


:

It looks like you could use a crosstab query rather than a bunch of similar
expressions. If you want to include all months, why not just use:
AllMthsInv: [ServCodeRate]*[UnitsUsed]
Your IIf()s are also missing the 3rd argument which I assume should be
either 0 or Null.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed], Null)

--
Duane Hookom
Microsoft Access MVP


:

I have a query that calculates the amount invoiced each month in a query:
For Instance:
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed])
AugInv: IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed])


Could someone tell me how can I get the query to give me a total for all the
months?

I tried this expression but it didn’t work: YearAmt: Sum([JulInv]+[AugInv])

Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
I provided the solution to the columns in a previous message. If you can't
find it, search on Crosstab Column Headings Property.
--
Duane Hookom
Microsoft Access MVP


Nona said:
Your suggestions are working beautifully! I'm really happy. Thank you.

May I ask another questions?
On a form, each month is listed in a column as in:
July JulInv
Aug AugInv...

and at the bottom
TotInv

I figured out that all months can be listed in the column on the form by
creating a dummy contract and a dummy service code with a rate of $0.00.
Fields for all 12 months are now on the form and ready to receive the data.

This works, but, of course, the dummy contract shows up as a contract, which
is not a good thing.

Is there a better way to get all 12 months displayed on the form even if
there is no data yet?

Or is there a way to hide that contract so it doesn't appear on reports and
forms?

(I'm trying to improve at database design!) :)









--
Nona


Nona said:
Thank you again for all this.
--
Nona


Duane Hookom said:
I should have also suggested the formatting of the values should be done in
controls on forms and reports.
--
Duane Hookom
Microsoft Access MVP


:

Wow! This is amazing and wonderful
I will really have to study this because I don't understand how you did
this, but it's great. I added an invoice for Sept, and it added it to the
query as I hoped it would.

Couple of things: This gives me the Tot Inv for each contract. How can I
get the total of all contracts invoiced for a single month?

And is there a way to list all 12 months in a column on a form and show the
total for each?

Also, can I format the amts to 2 decimal points?

Thank you SO VERY MUCH! I am learning a lot through this process.

qryCrossTabExperiment
InvType ContractName TotInv August July September
0 0
Cost Invoice Asheville Jail Diversion 0 0
UCR Buncombe Detention Facility 30117.53 19139.52 10978.01
UCR Buncombe Health Center & Pretrial 22205.8875 11634.115 10271.7725 300
UCR with Provider Psych Support 2195 2195

--
Nona


:

Not yet, but I will! I'm eager to try it, but it may be next week before I
can get immersed in the db again due to a lot of family issues - a death,
then home-comings, etc this weekend. May have a window of time tonight. I'll
let you know.

--
Nona


:

Did you try the SQL I provided?

--
Duane Hookom
Microsoft Access MVP


:

Thanks for your help. I've never done a crosstab query, so I'll need to spend
some time working on this...

Thanks again!
--
Nona


:

If you haven't tried crosstab queries, enter this into the SQL view of a new
query:

TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) as TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum([ServCodeRate]*[UnitsUsed]) as TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth;

--
Duane Hookom
Microsoft Access MVP


:


Yes, the query works. I did get that error message once. After playing with
it a bit, I found that if I did not include a checkbox field in the query
(called CostSettled), it worked fine and has ever since. I would like to know
why the query would not accept the checkbox field. I've used it in other
queries with no problem.

How else would I set the db up without using the field names as services?
You're probably mean the Human Resource fields for payroll etc. This database
is intended to handle contracts and to monitor the costs involved in the
provision of services.

The HR fields are entered monthly as integers that come via the HR dept. All
Access does is to print those on the report (invoice) when it is billed to
the contractor. There are no calculations with those fields. Some contracts
use these fields; others do not. More importantly, the contract itself is
included in the database as projected income.

It's a little disturbing to have you say the db is poorly designed, though I
am far from being an expert. I will certainly appreciate any suggestions you
can recommend.

Can you explain why I can't get the totals I need?

With much appreciation for your time and expertise,


--
Nona


:

I'm surprised the query doesn't error with a message about your IIf()
expressions not being part of an aggregate. They should either be in a Sum,
Avg, etc or in the Group By.

Are you sure your query works?

BTW: Your table structure is highly un-normalized. You have services as
field names. If you want to add another service, it looks like you will need
to create another field. This is generally considered poor design.

--
Duane Hookom
Microsoft Access MVP


:

Yes, here it is. I will certainly appreciate any suggestions!

SELECT tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0) AS JulInv,
IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed],0) AS AugInv,
IIf([CalMonth]="September",[ServCodeRate]*[UnitsUsed],0) AS SepInv,
IIf([CalMonth]="October",[ServCodeRate]*[UnitsUsed],0) AS OctInv,
IIf([CalMonth]="November",[ServCodeRate]*[UnitsUsed],0) AS NovInv,
IIf([CalMonth]="December",[ServCodeRate]*[UnitsUsed],0) AS DecInv,
IIf([CalMonth]="January",[ServCodeRate]*[UnitsUsed],0) AS JanInv,
IIf([CalMonth]="February",[ServCodeRate]*[UnitsUsed],0) AS FebInv,
IIf([CalMonth]="March",[ServCodeRate]*[UnitsUsed],0) AS MarInv,
IIf([CalMonth]="April",[ServCodeRate]*[UnitsUsed],0) AS AprInv,
IIf([CalMonth]="May",[ServCodeRate]*[UnitsUsed],0) AS MayInv,
IIf([CalMonth]="June",[ServCodeRate]*[UnitsUsed],0) AS JunInv,
Sum([JulInv]+[AugInv]) AS Amt, tblServicesProvided.PTLRegWages,
tblServicesProvided.PTLPDO, tblServicesProvided.PQARegWages,
tblServicesProvided.FICA, tblServicesProvided.TaxOther,
tblServicesProvided.WComp, tblServicesProvided.GroupIns,
tblServicesProvided.LiabilityIns, tblServicesProvided.Match401K,
tblServicesProvided.Travel, tblServicesProvided.LocalOfficeEx,
tblServicesProvided.COOverhead, tblServicesProvided.CorpAdmExp,
tblServicesProvided.TotDirectExp, tblServicesProvided.TotIndExp,
tblServicesProvided.TotExpense, tblServicesProvided.TotNonUCRClaim,
tblServicesProvided.[PRCSS-Reg], tblServicesProvided.[PRCSS-PDO],
tblServicesProvided.NonCapExp
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblServCodes.ServCode, tblServCodes.ServCodeRate,
tblServicesProvided.UnitsUsed, tblServicesProvided.CalMonth,
tblContracts.InvType, tblServicesProvided.ContractName,
tblServicesProvided.PTLRegWages, tblServicesProvided.PTLPDO,
tblServicesProvided.PQARegWages, tblServicesProvided.FICA,
tblServicesProvided.TaxOther, tblServicesProvided.WComp,
tblServicesProvided.GroupIns, tblServicesProvided.LiabilityIns,
tblServicesProvided.Match401K, tblServicesProvided.Travel,
tblServicesProvided.LocalOfficeEx, tblServicesProvided.COOverhead,
tblServicesProvided.CorpAdmExp, tblServicesProvided.TotDirectExp,
tblServicesProvided.TotIndExp, tblServicesProvided.TotExpense,
tblServicesProvided.TotNonUCRClaim, tblServicesProvided.[PRCSS-Reg],
tblServicesProvided.[PRCSS-PDO], tblServicesProvided.NonCapExp;

--
Nona


:

Can you provide all of your SQL view for the query? It might help to get a
better understanding of your records and full specs for the query.
--
Duane Hookom
Microsoft Access MVP


:

Thanks for replying. And especially for the tip about the 3rd argument. I
fixed that right away.

However, the AllMthsInv formula doesn't give me what I need. It gives the
result per record for all the months.

The query expression I gave as an example calculates all the invoices that
were made in July, so that I have a total that were invoiced for July.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed],0)
The next step is to get a total for the year.

I don't understand why the query won't total the amounts found in the
expressions, as in:
[JulyInv]+[AugInv]+[SepInv], etc., using the names in each month's expression.


--
Nona


:

It looks like you could use a crosstab query rather than a bunch of similar
expressions. If you want to include all months, why not just use:
AllMthsInv: [ServCodeRate]*[UnitsUsed]
Your IIf()s are also missing the 3rd argument which I assume should be
either 0 or Null.
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed], Null)

--
Duane Hookom
Microsoft Access MVP


:

I have a query that calculates the amount invoiced each month in a query:
For Instance:
JulInv: IIf([CalMonth]="July",[ServCodeRate]*[UnitsUsed])
AugInv: IIf([CalMonth]="August",[ServCodeRate]*[UnitsUsed])


Could someone tell me how can I get the query to give me a total for all the
months?

I tried this expression but it didn’t work: YearAmt: Sum([JulInv]+[AugInv])

Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top