Summing records selected

[dogmelissa]

Hello??
I posted this in early MARCH and no one has given me a suggestion/answer
yet? C'mon guys, I know *someone* can do this!!!
Please help me!

Melissa

 

[Karl]

Try this.

SELECT Count(Table2.JobNum) AS [Number of Jobs], [Enter
start date] AS [Start Date], [Enter end date] AS [End
Date]
FROM Table2
WHERE (((Table2.JobDate) Between [Enter start date] And
[Enter end date]))
GROUP BY [Enter start date], [Enter end date];

 

[Duane Hookom]

How about posting some sample records with the desired sum/count?

 

[dogmelissa]

This sounds promising, but how and where do I put this? And what is Table2?

Try this.

SELECT Count(Table2.JobNum) AS [Number of Jobs], [Enter
start date] AS [Start Date], [Enter end date] AS [End
Date]
FROM Table2
WHERE (((Table2.JobDate) Between [Enter start date] And
[Enter end date]))
GROUP BY [Enter start date], [Enter end date];

-----Original Message-----
Hello??
I posted this in early MARCH and no one has given me a suggestion/answer
yet? C'mon guys, I know *someone* can do this!!!
Please help me!

Melissa



.

 

[dogmelissa]

Ok, I will try to give you all the information needed. I had to look up some
information from a hidden part of the database to get this.
Field information: "OrderID" is an auto-number, long integer field that is
not shown on anything. I'm not sure why it's there now, but it was originally
supposed to be used as our job number. Our database ranges from OrderID 12
through 6757 as of this morning.
"JobNumber" is a field which has just recently been changed to perform some
action which adds one to the last number on record and creates a new job. We
used to assign job numbers on paper, but now we do it in the computer.
Regardless, this is a text field, and though most of our jobs are numbers, we
often have a 4-digit # followed by a letter.
Both of these fields are stored in a table called "Orders"

Sample Data: (Date range 16/03/05 through 18/03/05
OrderID Job#
6570 8038M
6568 9149G
6561 9191
6562 9192
6564 9193
6565 9193
6566 9194
6567 9196
6571 9197
6572 9198
6573 9199
6574 9200
6575 9201
6577 9202
6579 9203
6580 9204
6581 9205

When added up for this date range, it *should* read 17 records. But since
neither the OrderID or the Job# field are consecutive, obviously it's not
going to if I run any sum or subtract first from last function. Count could
work, but I don't know how to use it.

Hope this was enough information
Thanks,
Melissa

 

[Karl]

Table2 is your table - whatever name you gave it.
In query design click on menu item View and then SQL
View. Paste this statement after you replace Table2 with
you table name and the field names with your field names.

-----Original Message-----
This sounds promising, but how and where do I put this? And what is Table2?

Try this.

SELECT Count(Table2.JobNum) AS [Number of Jobs], [Enter
start date] AS [Start Date], [Enter end date] AS [End
Date]
FROM Table2
WHERE (((Table2.JobDate) Between [Enter start date] And
[Enter end date]))
GROUP BY [Enter start date], [Enter end date];

-----Original Message-----
Hello??
I posted this in early MARCH and no one has given me

a
suggestion/answer

yet? C'mon guys, I know *someone* can do this!!!
Please help me!

Melissa

:

I am creating a new report which will be a job log; displaying information of
jobs that we logged within a date range. What I

want
to do is have a field at

the end that will sum the number of records that

are
selected for that date

range. Does that make sense?
For example, if I open this report and I want to

know
the jobs that we

logged into the database between January 1-January

10,
then it will pull up

records 1040-1062, but there is also sub-jobs

1046G,
1021B & 1050G, so my

total number of records is actually 25, not 22.

What
I'm worried about is

what field do I ask it to sum in order to get sub-

jobs
AND "regular" jobs

counted? We track in two different ways; there is

the
auto-number field

called "OrderID" which we don't use anywhere, and there is the "JobNumber"
field which is what we use to actually track jobs.

In
the above example,

1046G is in the JobNumber field, I don't know what

the
OrderID would be on

this record.
Any help would be greatly appreciated. I know this

is
probably something

very simple, but my brain isn't working well today.
Thanks in advance,
Melissa

.

.

 

[dogmelissa]

Problem solved through the queries group.

Solution: Place a text box with control source "=count(*)" in the report
footer.

Thanks guys!
Melissa