Reporting Data from Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query grouped by date and location. I have 3 locations so for each
date I have three separate rows. On my report I need to show data from
separate rows in the query in separate fields. All I can get the report to
do is show the first row of data in the query. What can I do to get the data
I need off the queries? My report somewhat looks like this:
Location 1:
Mon
Tue
Wed
Thur
Fri
Sat
Location 2:
Days of Week
Location 3:
Days of Week

Please Help!
 
The only way I know to do this is to create 3 new queries

1. Make Table Query - Select 1st location
2. Append Table Query - Select 2nd location
3. Append Table Query - Select 3rd location

Then create a Macro that runs all three queries, one after
the other. Prefix the commands with SetWarnings=No

The reason I don't like this method is that the
information is not live, it is only valid the last time
the macro was run. But if you are doing this for a
report, just preface the report with this string and you
should be fine.

Good Luck,
Mark Matzke
 
Sorry, I don't follow your description: in the Subject, you wrote "from
Queries" but in the Body, you have "a query".

Do you mean you have 3 separate Queries, one for each location, and you want
to combine the data selected from these 3 Queries using a Query which will
be used as the RecordSource for the Report?

If that's the case, try a Union Query.
 
Right now I have 1 query with a row of data for each location, so 3 rows per
date. Do I need to make separate queries for each location and do a Union
query? I was trying to stay away from that because this is not my only
report and I will end up with 50 different queries combined into a few union
queries which will be the record sources for my reports. If this is the only
way I can do it that way, I just thought there would be an easier way to go
about it.
 
One Query is fine. All you need to do is to set the Sorting & Grouping to
group by location and then sort by date.

You can access the Sorting & Group using the Menu View / Sorting & Grouping
.... in the DesignView of the Report (or the icon right of the ToolBox icon
in the Report Design toolbar).
 
Brent said:
I have a query grouped by date and location. I have 3 locations so for each
date I have three separate rows. On my report I need to show data from
separate rows in the query in separate fields. All I can get the report to
do is show the first row of data in the query. What can I do to get the data
I need off the queries? My report somewhat looks like this:
Location 1:
Mon
Tue
Wed
Thur
Fri
Sat
Location 2:
Days of Week
Location 3:
Days of Week

Please Help!
Click to expand...
 
I guess I'm not following. It still wont let me get data from the second and
third row of my query. I am running it with one day of data so I have three
rows, one for each location. The first locations data is pulling fine onto
my report when I select the column as the control source of my text box, but
the second and third rows aren't pulling. I would really like to be able to
pull single individual cells from my query and put the data in individual
controls on my report. Is this possible?
 
Okay, I have one query grouped by date. On my report I have this query set
as my record source. In each one of my text boxes on my report I have which
column needs to be represented set as my control source and is only pulling
the data from the first date on my query. For instance, I have Monday
through Friday in my Query. Well on my report only Monday's numbers are
showing up. How do I get data from the other rows to show on my report? It
seems like it should be simple but I just cant figure this out.
 
Something I am not picking up.

Post the Tble Structure, the SQL String of the Query being used as the
RecordSource for the Report, and the details of the (Textbox) Controls with
the binding Field name for each Control.
 
My query is a very large union query and the SQL is very long so I don't know
if I should put it on here. My Query is good though. All the data is right,
I have checked it. I have it grouped by date so when it displays in
datasheet view I have column headings with the names of data and a row for
each date. So each date has many columns of data I need to report. My
problem is that on the report only the first row(or first date) is showing up
in my text boxes(controls). I need to be able to get the data from one date
and one column(so an individual cell) to its own text box on my report. This
is working fine for the first date, but I cannot find an option to get the
other rows data onto my report.
 
Let me know if I need to post the SQL and the text box properties. I really
appreciate your help.
 
I think I figured it out. My problem was that I designed these reports
before creating my queries and that made me overlook some things. Thanks so
much for your help though.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Week number 1
Access 2007 sort by day of week 3
date range 1
Sorting day names chronologically 5
Query returning data for every day of a period. 1
Line charting the same series using data from multiple sheets? 1
Week by Day 1
conditional running sum 7

Back
Top