Putting Report Output in a Table?

[Goldsmack12]

I have a failrly simple database, but I have a lot of records. I have
a report that will summarize my data by breaking down by date, time,
and then this other field with 3 different possible values, and
displaying the counts. AKA something records transactions, then I
break down how busy we are by each hour and what type of transaction.
The report gives me plenty of useful information that I would like to
copy to excel so i can then determine where and when we are busiest.
I'm not very good with Access, but I am good with formulas and stuff.
Is there a way to have a report write to a table or set up a quiery
that can do the equivalent of sorting and counting? I may just be
completely off base but any point in the right direction would be
welcome. Thanks in advance.
-J

 

[Arvin Meyer [MVP]]

Queries can easily be output to Excel. Using the menubar, choose:

Tools >>> Office Links >>> Analyze it with Microsoft Excel

You can also create your own button to export a specific table or query, in
code.

 

[Goldsmack12]

Arvin,
Thanks for the quick reply, but I want the report to be exported to
excel. I can't figure out how to perform a query that will sort,
group, and count in the way that i have everything broken down
reports.

I have a table with 4 fields: Date, Time, Location, and Event

I want to breakdown every time period for which I have data. I can do
this in a report by using an update query to add a field "time range"
where I assign them #'s 1-24, depending upon when they fall. From
there I generate a report that groups first date, then time range,
then location, and gives me a count on each. Is there a way that I
can query the same information? For time range, there are 24 distinct
values, and location has exactly 3 values. For date, the range could
be anything.
Thanks again,
-J

 

[Arvin Meyer [MVP]]

Run the report, then:

Tools >>> Office Links >>> Analyze it with Microsoft Excel

It may or may not run depending upon which hotfixes and service packs you
have.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin,
Thanks for the quick reply, but I want the report to be exported to
excel. I can't figure out how to perform a query that will sort,
group, and count in the way that i have everything broken down
reports.

I have a table with 4 fields: Date, Time, Location, and Event

I want to breakdown every time period for which I have data. I can do
this in a report by using an update query to add a field "time range"
where I assign them #'s 1-24, depending upon when they fall. From
there I generate a report that groups first date, then time range,
then location, and gives me a count on each. Is there a way that I
can query the same information? For time range, there are 24 distinct
values, and location has exactly 3 values. For date, the range could
be anything.
Thanks again,
-J

 

[Goldsmack12]

Arvin,
I believe you are referring to Office 2K3 but I am using 2K7. The
export to excel option is greyed out.
-J

 

[Arvin Meyer [MVP]]

Then I'd say that it no longer works in Access 2007 for reports. You'll need
to export the query.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Arvin,
I believe you are referring to Office 2K3 but I am using 2K7. The
export to excel option is greyed out.
-J

 

[a a r o n . k e m p f]

you can use a maketable query to take query results and push them into
a table

 

[Goldsmack12]

Aaron,
Do you have any pointers on how to set up the query itself? I am
familiar with the maketable query, but my issue is figuring out the
logic of going from

Date Count
Time Count
Location Count
Event Count

to a query that will go through all combinations of date, time,
location, and event. I know how to query for a specific date, but I
don't know how to say "Everytime a different date appears, go through
all of this crap for it" and the same for time, location, and event.
-J

 

[Arvin Meyer [MVP]]

Unfortunately Aaron's knowledge of databases is a bit weak. You do not need
a Make-Table as the query behind the report has all the raw data you need.
You have, apparently, been using the Sorting and Grouping properties of the
report to get what you need, but cannot output that to Excel.

Try building another query, using the same query as the source for your
report to be the source for the export. Now experiment with Grouping in the
report by using the Group By or Totals button (the Greek letter Sigma) to
get your grouped values. You may not be able to get an exact duplication of
your report, but it should be pretty close.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Aaron,
Do you have any pointers on how to set up the query itself? I am
familiar with the maketable query, but my issue is figuring out the
logic of going from

Date Count
Time Count
Location Count
Event Count

to a query that will go through all combinations of date, time,
location, and event. I know how to query for a specific date, but I
don't know how to say "Everytime a different date appears, go through
all of this crap for it" and the same for time, location, and event.
-J

 

[a a r o n . k e m p f]

Arvin;

you are so full of shit, do you know that? NAME ONE THING I HAVE EVER
SAID THAT IS WRONG, ASSHOLE.
PLEASE DO IT. BECAUSE YOU LIE.

I know much much more about database than any of the kids in this
group. And I listen to the OP.
Does the OP say anything about Excel?

he asks how to 'Putting Report Output in a Table?'

does this involve EXCEL?

It just makes me sick that you kids haven't helped this guy yet.

Just make a query, under the query menu select query type = 'Make
Table' and then it will ask what table you want to put it into.

 

[a a r o n . k e m p f]

if it is not a simple group by statement-- then it sounds to me like
'correlated subqueries'

Select distinct OrderDate, count(*) as CT_Orders
(Select count(*) from employees where Employees.hiredate =
Orders.Date) as CT_Hired
From Orders
Group by OrderDate

Correlated subqueries mean that yuou RELATE the query to the subquery
in order to get the results you're looking for

 

[Goldsmack12]

Arvin, that was a deek move, I appreciate the help but I have no
patience for condescending egomaniacs

But anyway my report is not built on a query. I put the "field" on
the report by clicking and dragging and then went to group, added a
new group, did that 3 times, selected the option for a count. Are you
saying there's a way of changing that into a query?

If it's not possible to put the actual report into a table, then I
went to put the data that the report yields into a table...as this
does not seem doable either, I am looking for some assistance in the
logic/steps to making a query that will duplicate this effort.
ex. 1) do a select quiery of blah blah blah
2) then this blah blah blah
3) then append to blah blah blah
-J

 

[Larry Linson]

Arvin, that was a deek move, I appreciate the help
but I have no patience for condescending egomaniacs

What, in your usage, does "deek" mean?

And just who are you describing as a "condescending egomaniac"?

Larry Linson
Microsoft Office Access MVP

 

[a a r o n . k e m p f]

the ASSHOLES who attack me; even when I'm right?