Query Problem

[Darknight850]

Hello All,

I have just got thrown something at me for this Monster i have created :D. Well I am not exactly sure how to go about getting what I need in the report, but I know I have to do something with the query. What I have going is a full report of all jobs in my work place, in the report it shows Total workers per job, workers on vacation, works that have Permanent ID cards and workers that have temporary ID cards. What i want to do is subtract the records that have the on vacation fields checked, this way the numbers are more accurate, but at the same time keep the count of on vacation.

[Darknight850]

Bump,Bump,Bump,Bump,Bump,Bump,Bump,Bump,Bump,Bump, Bump,Bump,

[puppydogbuddy]

Hello All,

I have just got thrown something at me for this Monster i have created :D. Well I am not exactly sure how to go about getting what I need in the report, but I know I have to do something with the query. What I have going is a full report of all jobs in my work place, in the report it shows Total workers per job, workers on vacation, works that have Permanent ID cards and workers that have temporary ID cards. What i want to do is subtract the records that have the on vacation fields checked, this way the numbers are more accurate, but at the same time keep the count of on vacation.

I am not sure of what you want, but here is my suggestion based on the above description.

Your totals that you have now are probably in the Report Footer. I suggest that you add a Vacation Row to the report footer, and also, an Adjusted total row as illustrated below:

..............................Total Workers...........Permanent...........Temporary... .
................................All Jobs....................ID Cards..............ID Cards.....
Unadjusted Totals...........500..........................430. .......................70
..Vacation Adj..................( 14).......................( 14)........................(0)
Adjusted Total................486.......................... 416........................70 .......


From your description, you already have the unadjusted totals above from the textboxes in the report footer. For the vacation adjustment row, you can put an expression similar to the following in the textboxes as shown.:
=Sum(IIf([VacCkbox] = True,Count([VacCkBox]),0))

And an expression similar to the folowing for the adjusted total:
= [NameofYourAdjTotalTxtbox]- [NameofYourVacAdjTxtbox]

[Darknight850]

Thank you for your reply and sorry about not explaining it well. Truthfully I don't really know how to explain it to well. I do need the totals to be deducted as well, but my main purpose is the actual data in the detail section. I tried to put the code that you gave me in the detail section and take the same steps as in the footer, but i couldn't get it to work, kept giving me an error saying the code was wrong. I think the problem is that it couldn't find the field Vacation, because in the query i am basing this off of, is an expression counting all of the true boxes. I tried to add just the Vacation field to the end of the query and just do a group by, this way I could use it in the code, but every time it would mess up my numbers.

[puppydogbuddy]

Thank you for your reply and sorry about not explaining it well. Truthfully I don't really know how to explain it to well. I do need the totals to be deducted as well, but my main purpose is the actual data in the detail section. I tried to put the code that you gave me in the detail section and take the same steps as in the footer, but i couldn't get it to work, kept giving me an error saying the code was wrong. I think the problem is that it couldn't find the field Vacation, because in the query i am basing this off of, is an expression counting all of the true boxes. I tried to add just the Vacation field to the end of the query and just do a group by, this way I could use it in the code, but every time it would mess up my numbers.

You can only have detail amounts, not summations in the detail section. What you can do is eliminate the detail by erasing the textboxes or making the entire detail section not visible, and just show totals for each group in the group footer. For example, if you wanted to see a total for each employee, but not the detail of each paycheck......you would show no details, and group by employee with the totals in the employee group footer.

[Darknight850]

You can only have detail amounts, not summations in the detail section. What you can do is eliminate the detail by erasing the textboxes or making the entire detail section not visible, and just show totals for each group in the group footer. For example, if you wanted to see a total for each employee, but not the detail of each paycheck......you would show no details, and group by employee with the totals in the employee group footer.

I need the detail amount, that is my main focus

[puppydogbuddy]

I need the detail amount, that is my main focus

Ok, then post the SQL for the query you are currently using as the source for your report.

[Darknight850]

SELECT tblAll.Jobs, Count(tblAll.Name) AS CountOfName, Sum(tblAll.[Vacation])*-1 AS [SumOfOff Base], Sum(tblAll.IDCard)*-1 AS SumOfMNFIBadge, Count(tblAll.IDCard)+Sum(MNFIBadge) AS CountOfIDCard, Sum(tblAll.[Interview Complete])*-1 AS [Interview Complete], Count(tblAll.[Interview])+Sum([Interview]) AS [CountOfInterview Complete], Sum(tblAll.Temporary)*-1 AS SumOfTemporary, Count(IIf([tblAll.IDCard]=14,[tblall.IDCard])) AS CountOfIDCard
FROM tblAll
GROUP BY tblAll.Jobs;

[puppydogbuddy]

SELECT tblAll.Jobs, Count(tblAll.Name) AS CountOfName, Sum(tblAll.[Vacation])*-1 AS [SumOfOff Base], Sum(tblAll.IDCard)*-1 AS SumOfMNFIBadge, Count(tblAll.IDCard)+Sum(MNFIBadge) AS CountOfIDCard, Sum(tblAll.[Interview Complete])*-1 AS [Interview Complete], Count(tblAll.[Interview])+Sum([Interview]) AS [CountOfInterview Complete], Sum(tblAll.Temporary)*-1 AS SumOfTemporary, Count(IIf([tblAll.IDCard]=14,[tblall.IDCard])) AS CountOfIDCard
FROM tblAll
GROUP BY tblAll.Jobs;

The above query is a summation (totals) query with no detail info. If you want detail and summation info in this query, undo the summation (click the summation key on the toolbar), use groupby's to group details and use Dcount and DSum for your aggegated totals....or leave your query at the detail level and do the summation through the report.

[Darknight850]

It already works with the detail section, it gives me the totals for every job that is what i am looking to do, But, not having the off base records count. yet at the same time show the total of the off base records still be there, and not 0

[puppydogbuddy]

It already works with the detail section, it gives me the totals for every job that is what i am looking to do, But, not having the off base records count. yet at the same time show the total of the off base records still be there, and not 0

Try using the domain aggregate functions like DCount or DSum for those totals ....