Quick grouping question

[Guest]

Is it possible to have grouping in a query based off another field in a
query? You can have a report in Access look like the below. But can all the
grouping totals etc be done in a query and none of it done directly in the
report? Reason being I need to transfer the results into an Excel spreadsheet
and I am thinking it needs to go into a report then export to excel. Any help
is appreciated.

I.E. A Access report can look like this..But can you do it in a query

EXAMPLE:

Emp ID

XXXXX
XXXXX
XXXX
XXXX Total number of emp XXXXX

Emp Phone NUMBERS
XXXXXXXXXX
XXXXXXXXXX
XXXXXXXXX Total nunmber of phone number XXXXXXXXX

 

[bmarland]

One way to do it could be to write another query for your calculation
and then add it to the end of your first query results with a UNION.

ReportQuery:
select emp_id
from tbl_employee
order by emp_id
union
select "Total number of emp: " & (select count(*) from tbl_employee)


Or something like that...

 

[Guest]

Thanks I will give it a try

One way to do it could be to write another query for your calculation
and then add it to the end of your first query results with a UNION.

ReportQuery:
select emp_id
from tbl_employee
order by emp_id
union
select "Total number of emp: " & (select count(*) from tbl_employee)


Or something like that...

 

[Guest]

How would it be possible to group these fields with totals under them like in
a report. I dont think it is possible in a query? It may need to be done in a
report but that is out of the question. They need the grouping etc in Excel.
Any other ideas?

 

[Ben]

Well, I'm a little confused about what you mean by group by. If you
really want to group by then that would mean another field in the same
row with the total number of records.
i.e.

select emp_id, count(*)
from tbl_employee
group by emp_id
order by emp_id

But that wouldn't really give you a reasonable report.

If your going to just put it in excel anyway why not use a function in
excel (count) to show the total at the end with proper formatting?

 

[Guest]

Ben,

No I have about 13 fields and need to goup and give total for 3 seprate
fields. I dont think that is possible in a query is it..

IE

planID
1001
total for field1 (1001)

plan ID
2001
total for field1 (32)

plan ID
222
total for field 1 (101)

I need to group on a field so I can get the results (count) from aother
field such as field 1 above.