Totaling records in a field for a query/report... help please!!! :) 1

[Spyridon]

I'm trying to create a simple database that inputs work orders into a table through a form.The table is then queried various ways for different reports. The info is entered into a form with a work order field, a schedule date and a check box for various types of orders.The information is going fine into the table, although the value of each check box if checked is -1, and I'm not sure if that will complicate any calculations that I try and perform. And that is where my problem lies, I'm trying to get a total number of orders for a range of dates for each particular type of order. I would think this is fairly simple, and it probably is for someone who actually knows what they are doing.

Anyhow, I'm trying to get information from a table that looks like this:

WORKORDER STATUS DATE BASIC EXBASIC SILVER

5011 complete 06-12-03 -1 0 0
5012 complete 06-12-03 0 -1 0
5013 complete 06-12-03 -1 0 0
5014 complete 06-20-03 -1 0 0
5015 open 06-20-03 0 0 -1

etc,

to show up in a query/report by the amount of complete orders through the dates of 6/12-6/20

STATUS BASIC EXBASIC SILVER

complete 3 1 0



At least that is how I need to see it in the report. After that I need to find how to multiply the respective orders under each column by a price amount and add all the
price amounts together for a grand total. i.e,

basic's price is $5, so 3x5 gives the total of $15, plus the total for exbasic and silver
in a grand total on the report.

I hope I explained everything without it being to convuluted. If anyone can give me some help I would be extremely appreciative. I know I'm asking for a lot of information, so even just a part would be very helpful. I understand how to do the basic query,just not how to
total it all up. When I try to Sum them up in the query, it gives me a total for each date, not all the dates put together. But I have to have the dates in there to get the
correct range. Once again, I thank you, my wife thanks you, and the brick wall I've been pounding my head into in thanks you.


Michael

 

[PaulBricker]

In the Report Footer, or Group Footer if you use one, you can use expressions like this as the Control Source for textboxes
=Sum(IIf(Status = "Complete",1,0))
this will give you a total complete
=Sum(IIf(Status = "Complete" And Basic = -1,1,0))
this will tell you how many Basic you have where status = Complete
=Sum(IIf(Status = "Complete" And ExBasic = -1,1,0))
for the ExBasic field.
Try these out and see how it goes.

Paul

 

[PaulBricker]

I reread your post and saw the part about multiplying the values returned. The expressions would look like this

=(Sum(IIf(Status = "Complete" And Basic = -1,1,0))*5

To get a grand total you would have to do this in one large expression

=(Sum(IIf(Status = "Complete" And Basic = -1,1,0))*5 + (Sum(IIf(Status = "Complete" And ExBasic = -1,1,0))*5 + (Sum(IIf(Status = "Complete" And Silver = -1,1,0))*5


Paul

 

[Spyridon]

Hey thanks Paul, I'm in the process of tring it out and figuring out how to make it work right now. What I can't figure out is what the numbers are after the = sign, "=-1,1,0)". At first I thought that maybe they were cases, counts, but for all I know they could be crocadiles. Anyway, thanks a lot for getting me going in the right way.


Michael

 

[PaulBricker]

Michael, what we are doing is Adding (Summing) 1's and 0's. Basically it's the same thing as counting. The first -1 is what we are testing the Basic, ExBasic and Silver fields for and then either adding 1 or 0 to our Sum. Try it out and let me know if you run into problems.

Paul

 

[Spyridon]

Thank you very much Paul, everything is up and running just like it should be, and you caused the snowball effect! Now all I have to do is figure out how to put this on a network.......

You wouldn't happen to know how to keep all the access forms/reports grouped together as one in the Windows taskbar would you? I have XP at home and it does it automatically for me, but here at work with 2000 I can have upteen million little taskbar items...

Once again, thank you so much!

 

[PaulBricker]

Sorry, don't know about the task bar. You should probably start a new thread on that one. Glad you got the rest going.

Paul

 

[Spyridon]

Just to answer my own question, I found out that it is an easy task to group together items on the taskbar. All you have to do is go Tools/Options/Windows In Taskbar and uncheck it.