# Creating A report multiple queries in access 2003



## llauva (Jun 30, 2008)

I have 3 queries that are run by entering a date range. I would like all three queries to be on a single report. I believe it is the date range that is stopping my report. 
Can you help me?


----------



## jimr381 (Jul 20, 2007)

Welcome to the forum.

If you can post the shell of the database we can assist you a little easier. Fake values within the tables would be great as well. It sounds like you need to setup a search for that will pass the date value through the queries and then pull back a report with sub reports.


----------



## OBP (Mar 8, 2005)

Do you actually need three Queries?
If you have 3 Queries you will need 3 reports, 2 of which could be "Sub reports" on the main report.


----------



## llauva (Jun 30, 2008)

Here is my problem 
3 tables each holding different info.
All three have start and end dates.
I want to query the three tables for monthly details.
I originally tried have one query based on the three tables. When it came time to put the date range in it caused a continuous  circle. 
If I use one query for each table i can get my data. But when i try to put the data into a report the date range is once again a problem.


----------



## OBP (Mar 8, 2005)

Can you do as jim requested and Post a Zipped attachment of the database/


----------



## llauva (Jun 30, 2008)

I tried doing that, but it asks for the date parameters over and over again


----------



## OBP (Mar 8, 2005)

We will need a copy of the database to help you.


----------



## llauva (Jun 30, 2008)

Yes I can do that tonight.
Thanks


----------



## llauva (Jun 30, 2008)

I am getting this error. The zipped file is only 98KBs. Is there anything you can do to help?
*llauvaDatabase.zip*:
Upload of file failed.


----------



## OBP (Mar 8, 2005)

llauva, I have private mailed you my email address so that you can send me the database and I will post it for you.


----------



## llauva (Jun 30, 2008)

I just sent the database to you


----------



## OBP (Mar 8, 2005)

Which 3 tables and which report?


----------



## llauva (Jun 30, 2008)

The main report is EdSessionsMonthlyDetails. The other 3 are sub reports. The subreports work fine as stand alone reports. Getting them into one report is the problem.

They are running off of the queries EdSessMonthlyTotals, NhClassMonthlyTimeAttendees Query and SpecIniMonthlyDetails.

The table are EdSessionDetails, NhClassMonthlyTimesAttendees and SpecialInitiativesDetails.


----------



## OBP (Mar 8, 2005)

llauva, the problem that you have is that the 3 tables and queries have nothing relating them together., is there any relationship?


----------



## llauva (Jun 30, 2008)

Yes, they are classes that we teach.


----------



## OBP (Mar 8, 2005)

OK the problem with the report/subreport is that you had set Master/Child Links between them, which prevented the data being displayed. The actual relationship is just the date period that you wish to display.
I have added a Report dates Form that allows you to put the Beginning and ending dates in just once. The report appears to be working OK now, although they now needs some formatting after I played around with them.


----------



## llauva (Jun 30, 2008)

Where did you change the relationships, and what did you change them to.

I'm feeling dumb, but i don't understand the code on the new form. Where does it state to make the dates between a time frame. I saw how the form called the report. If I have more reports with the date range how would i call them?


----------



## jimr381 (Jul 20, 2007)

I would suggest that you become familiarized with "Format">>"Align" from the menu bar. This will help you clean up a lot of your layout issues that you are having. "Format">>"Size" will help you with evening out the sizes as well.

After you get the stuff worked out with your report and subreports, then we would really want to look at laying out your forms a little differently. Right now it feels like infoglut when I open them or information overload. I suggest that we would look into doing a tab form or grouping them a little better.


----------



## OBP (Mar 8, 2005)

llauva, the key to the new form is that each Query that you produce for a Report looks at the 2 Form dates in the Criteria Row with this
Between [forms]![Report dates]![Beginning Date] And [forms]![Report dates]![Ending Date]
I didn't do anything with the relationships for the Queries and reports, the Form dates create a period that links all the reports together. So any new queries and reports that you want to use those dates need that in the query criteria row.
But as I said in my email your Table Relationships definitely need sorting out.


----------



## llauva (Jun 30, 2008)

Reports aren't my thing. The format is based on a report that managemeent wants. I didn't reproduce it well. I tried to get my database to work like yours. I changed each query to pull the date from the new date form. I added relationships, but the 2nd 2 sub queries are not showing up. Did I miss a step?


----------



## OBP (Mar 8, 2005)

Did you ensure that the Master Child links between the main report and 2 Sub reports have been removed?


----------



## llauva (Jun 30, 2008)

I think i have it now.
How do i get everything on one page.
The access book i am using doesn't show much on reports.


----------



## jimr381 (Jul 20, 2007)

Subreports are typically setup as nested reports. What section did you place your subreports within? If there is too much data to display try hiding the headers/footers from the view menu and resizing some of the different sections.


----------



## llauva (Jun 30, 2008)

I put the subreports in the detail section. Is this correct?


----------



## OBP (Mar 8, 2005)

Yes that is correct, you can make everything smaller by using a smaller font.


----------



## jimr381 (Jul 20, 2007)

Well the only problem with putting it in the details section is that it will try to repeat that subreport for each record. It typically will put it in the report footer.


----------



## llauva (Jun 30, 2008)

I put the subreports in the main form's report footer section. 
My data is not showing up now. Was i supposed to put the sub-reports data in it's detail part?


----------



## jimr381 (Jul 20, 2007)

No putting it in the report footer should work. Did you resize it or change to property to allow it to grow and do what OBP said with regards to breaking the master/child relationship within the subform?


----------



## llauva (Jun 30, 2008)

How do i break the relationship?


----------



## jimr381 (Jul 20, 2007)

Off the top of my head you will select the subreport and go into the property sheet for it. Within the "Data" tab you should see some property for the relationship.


----------



## OBP (Mar 8, 2005)

It is the "Master/Child" link when you click on the Sub Report's outer frame and look in "Properties"


----------



## llauva (Jun 30, 2008)

Found it thanks,
Now do i delete what is there?


----------



## OBP (Mar 8, 2005)

yes, they should be blank


----------



## llauva (Jun 30, 2008)

You are my hero.
It worked like a charm.


----------



## llauva (Jun 30, 2008)

How do i get the spacing between the rows of data smaller?


----------



## OBP (Mar 8, 2005)

In the subforms, lift the bottom of the "Detail Section" up to just under the Fields.


----------



## llauva (Jun 30, 2008)

I would now like to total the each sub report and then get a Grand total. 
Do I do this on the Report?


----------



## OBP (Mar 8, 2005)

Total each Sub Report in it's Footer, the field(s) holding the totals do not have to be visible and then on the main form total the Subforms.


----------



## llauva (Jun 30, 2008)

I have 2 totals and one average text boxes. Do I need to do a sorting grouping footer for each box?


----------



## OBP (Mar 8, 2005)

I shouldn't think so.


----------



## llauva (Jun 30, 2008)

I've put my totals on the subforms. I want to show the subforms totals on the main form then show the grand totals.

How would i display the 3 forms totals in the main form. Would that be an = statement in the control field of the main form?


----------



## OBP (Mar 8, 2005)

Yes but you will probably have to refer to the "Subform" as well, other than that you could use VBA to do it.


----------



## jimr381 (Jul 20, 2007)

He should be able to refer to the control via the long name for summarizations right? [objecttype]![objectname]![Fieldname] I have never tried to actually summarize data within subreports before.


----------



## OBP (Mar 8, 2005)

Jim, they should be able to do it that way, but Totalling can be a bit of a pain.


----------



## llauva (Jun 30, 2008)

Thank you Jim, your suggestion worked. This is the code i wrote
txtGrandEdAtten = [Report]![EdSessMonthlyDetailssubreport]![txtSumAttendees]

By the way i'm a she not he


----------



## jimr381 (Jul 20, 2007)

Lo Siento. She!


----------



## llauva (Jun 30, 2008)

Thanks for your help.


----------



## llauva (Jun 30, 2008)

I am trying to get a grand total for the totals in my 3 sub reports.
I tried to use =sum then list all three fields. I am getting a pop-up requesting parameter values. I'm not sure what i did wrong. here is the line

=sum([txtGrandEdAtten]+[txtGrandNHAtten] +[txtGrandSIAtten])


----------



## llauva (Jun 30, 2008)

I was also trying to put the following code in and the error is "compile error sub or function not found and it highlights sum.
txtMthTotalsAtten = sum(txtGrandEdAtten) + (txtGrandEdLength) + (txtGrandEdEval)

when I try Dsum I get arguement not optional


----------



## jimr381 (Jul 20, 2007)

When looking at subitems you need to format the field information like this. [object type]![object name]![field name]


----------



## llauva (Jun 30, 2008)

this is the code
txtmthTotalsAtten = sum([Report]![EdSessMonthlyDetailssubreport]![txtSumAttendees] + [Report]![EdSessMonthlyDetailssubreport]![txtSumLength] + [Report]![EdSessMonthlyDetailssubreport]![txtSumEvalAv])

I'm getting a sub or function not defined


----------



## jimr381 (Jul 20, 2007)

You typed this out within a text box right?

= sum([Report]![EdSessMonthlyDetailssubreport]![txtSumAttendees] + [Report]![EdSessMonthlyDetailssubreport]![txtSumLength] + [Report]![EdSessMonthlyDetailssubreport]![txtSumEvalAv])


----------



## OBP (Mar 8, 2005)

I don't think you need the [Report], just the subreport's name


----------



## llauva (Jun 30, 2008)

it doesn't like the *Sum *
I'm getting sub or function not defined

I know this is basic VB but i'm stuck


----------



## OBP (Mar 8, 2005)

Are you using VBA or a function in the field Control Source?


----------



## llauva (Jun 30, 2008)

I was putting in the code window.
That is where I put the statement for text boxes to display the totals of each subreport. I am attempting to get a grand total. When i try to put it ing the control field I get a parameter request pop-up. When i try to put it in the code i am getting the sub or function not definged.


----------



## OBP (Mar 8, 2005)

In VBA you use the me. prefix to the Subform and Field names ie
me.txtmthTotalsAtten = me.[EdSessMonthlyDetailssubreport]![txtSumAttendees] + me.[EdSessMonthlyDetailssubreport]![txtSumLength] + me.[EdSessMonthlyDetailssubreport]![txtSumEvalAv]


----------



## llauva (Jun 30, 2008)

beautiful

I was thinking that I have forgotton this stuff because it has been a couple of years since I took the course. But i am now realizing that i never took vba. I took VB 6.0. they are similar but not the same. I know i never learned the me command. Thank you for the help. I hope you don't mind but i will be turning to you for more help.


----------



## OBP (Mar 8, 2005)

That is what I am here for. :up:


----------



## llauva (Jun 30, 2008)

My report is complete. I am using a form to gather the date range for each month. I would like to have the report title to indicate the month and year systematically. How would i do the code for that . Should i do an if then?


----------



## jimr381 (Jul 20, 2007)

You are going to group by month and year too then right? It sounds like you are looking for a group header and not a report header.


----------



## OBP (Mar 8, 2005)

If you want it in the Title section you can add it referencing the Form, or add it to the Query by referencing the form using
Forms![Form name]![Field name]
where form and field names are the ones for your form, in the query heading you do not use an "=" sign but in the report you do, ie
=Forms![Form name]![Field name]
in a text field on the report.


----------



## llauva (Jun 30, 2008)

When referencing the form how do i convert the specific date range to just the month.
example date range is 1-1-08 to 1-31-08. I want the textbox to display January 2008


----------



## jimr381 (Jul 20, 2007)

If you answer my question I will see you and raise it with an answer of my own.


----------



## OBP (Mar 8, 2005)

Providing that you are always using a Month's worth of records you can just pick up on the first field and use this in a new in a report field

=format(Forms![Form name]![Field name], "mmmm")

where the field name is one of the date fields
or like this for a Query Column heading

Month: Format([Date Arrived],"mmmm")

that is from one of my queries in the database that I am currently working on.

Jim :up:


----------



## jimr381 (Jul 20, 2007)

Wouldn't you take it a step further and add the year in as well though?

=format(Forms![Form name]![Field name], "mmmm")&", "& format(Forms![Form name]![Field name], "yyyy")

You would want to group by that as well so it groups via that interval.


----------



## OBP (Mar 8, 2005)

Yep.


----------



## llauva (Jun 30, 2008)

I'm working on another form. I have it set up that that form B will open from a command button on form A. I have a command button of form b that will open a third form, form c. I would like fields in form C to autopopulate with some fields from Form B. 
This is how I wrote it in form B's command button.
Trainer = [Form]![TimeOff]![TrainerName]. The message is that it can't find the field timeoff which is the name of the form not the field.

I put it in form C's open code and got the same message. Why is it not recognizing the form


----------



## jimr381 (Jul 20, 2007)

You did put brackets around the field trainer as well right? [Trainer] You are doing a setvalue right? To set the value of the field to something? It seems like it might be recognizing as a filter or Where condition instead.


----------



## llauva (Jun 30, 2008)

yes, I did put the year on it, and it looks great. I have submitted it to the powers above.

Did you get a chance to see my other question?


----------



## llauva (Jun 30, 2008)

i did not put brackets around the field trainer. I thought since that field was on the current form i would not need brackets. 
I think it is a setvalue. I want the fields from the one form to populate into the the other form both forms are feeding into different tables. I'm trying to populate both at the same time.


----------



## jimr381 (Jul 20, 2007)

I do macros so I do not know the underlying VBA code, but it is called Setvalue within the macro commands. As a rule of thumb, whenever you are referencing fields you will want to put brackets around the field name.


----------



## llauva (Jun 30, 2008)

could i do this with a macro?


----------



## jimr381 (Jul 20, 2007)

Setvalue within a macro should work for it yes. But I want to say there is a setvalue VBA command as well. I do not touch VBA that much unless I am parsing it.


----------



## llauva (Jun 30, 2008)

What is parsing. I just got that message when i tried to create the macro


----------



## slurpee55 (Oct 20, 2004)

parsing, or, more formally, syntactic analysis - a fancy way of saying one analyzes the grammar/structure of a language. http://en.wikipedia.org/wiki/Parsing
jim's just using big words.


----------



## jimr381 (Jul 20, 2007)

I wasn't trying to use big words. I thought that was the term they would have used in her VB class and tried to adjust my terms towards what she might know.


----------



## llauva (Jun 30, 2008)

thanks, although that still doesn't help me. 
I don't want the associates to double data enter. They have to record time off in one table 
I want to use that number to base calculations on a form that feeds into a second table that has some of the same fields, trainer name , month and date.
Is there a way for them to put data into one form and then trainsfer the info to the next form that will be used for the next step of the process.


----------



## llauva (Jun 30, 2008)

You know one of the problems is me. I never used VBA before. The class is my capstone project that i have to build a database for. I didn't realize that i was using something different from the VB that i took in school. My class technically over. My professor gave me a grade and is awaiting my database. My job is waiting for it too, because i did my project for a business need. The person who tutors at my college is not able to help me either. This site has been my saving grace


----------



## OBP (Mar 8, 2005)

The answer to your question is yes, although you do not have to use VBA to do it, but if using VBA demonstrates your understanding for your project then you can.
How many fields do you want to transfer?
How are you opening the second Form, with a Command Button?


----------



## jimr381 (Jul 20, 2007)

Also why are you having the same data in two tables? That is bad database design.


----------



## llauva (Jun 30, 2008)

Yes I am opening the form with a command button.

I have the data in two seperate tables because I didn't plan it well. The repetitive fields are the name of the trainer, the month and year for the statistics. The info is pulled from each corresponding table.


----------



## jimr381 (Jul 20, 2007)

Well you can just nix it in the table that it should not be in and you should be fine. If you are unsure if you added data to one and not the other then I would recommend that you setup a query that searched for null values in the fields that you will be keeping and having those be updated with data from the fields that you will be deleting.


----------



## llauva (Jun 30, 2008)

Your right, but i'm not sure how else to do it. 
Can you look at the two tables and advise?
The tables are TimeOff and TrainerMonthlyActivity


----------



## jimr381 (Jul 20, 2007)

I can look at them, but it will probably not be until around 2PM. I have a couple of ceremonies to attend and still have to yell at my network to allow me to download zip files again. Can you please zip up and post the latest rendition of your file?


----------



## llauva (Jun 30, 2008)

I can't zip at work either. The two tables I am referring to haven't changed since last posting, can you use the posted version since they haven't changed? If not I can send it to you tonight.


----------



## OBP (Mar 8, 2005)

The only thing that needs to be in the second table is the RecordID from the first table.
A query picks up the rest of the data.


----------



## llauva (Jun 30, 2008)

This is where my problem is. I think i am blurring the lines of queries and tables.

Each of the trainers have to report monthly, total hours on each activity. I have a table for the monthly activity. I can't figure out how the trainers will report the hours without repeating which month, year, and trainer for each activity. The trainers could have worked on 10 different activities in a month. 

I have a monthly activity table that i'm questioning. If you could look at it that would be a big help.


----------



## OBP (Mar 8, 2005)

Have you heard of CrossTab Queries and Pivot Table Forms?
What is the Monthly Activity Table called?


----------



## llauva (Jun 30, 2008)

The monthly activity is called TrainerMonthlyActivity

I know only what is in the MS 2003 Access step by step book which is very basic.


----------



## OBP (Mar 8, 2005)

llauva, I have supplied some queries which provide totals in different ways. There are some with "Totals" and Grouping and 2 with Crosstab Queries.
I have serious reserves about the Relationships you have, they should be One to Many relationships with "Referential Integrity" imposed.
Your "Trainer Activity" Form also has an "Exit" button that closes Access rather than just closing the Form.


----------



## llauva (Jun 30, 2008)

How do i edit my relationships?


----------



## OBP (Mar 8, 2005)

You right click on the "Line" between the two tables and then Edit Relationships.


----------



## llauva (Jun 30, 2008)

And I should make them all one to many?


----------



## OBP (Mar 8, 2005)

If a Table can only One instance of the Record and it's related table can have Many instances of the Record ID then it should be a One to Many Reletionship.


----------



## llauva (Jun 30, 2008)

ok I've edited my relationships. I will zip it to you tonight.


----------



## llauva (Jun 30, 2008)

I am having trouble with my one form now. It is requiring an entry one field and I want the field to be optionable. The field is pulling from a different table. Does this have to do with the relationships?


----------



## llauva (Jun 30, 2008)

I figured it out please ignore last question


----------

