pauls681
asked on
Parameter and crosstab queries
Using Access 2000
Does anyone know if you can have a parameter query with a cross tab
query.
Ex: I created a cross tab query that is date specific. To do this I
created a reg. parameter query and based the crosstab query off of it.
Is there a way to do both at once???
I found a prev post ob this but it didn't make any sense to me.
Does anyone know if you can have a parameter query with a cross tab
query.
Ex: I created a cross tab query that is date specific. To do this I
created a reg. parameter query and based the crosstab query off of it.
Is there a way to do both at once???
I found a prev post ob this but it didn't make any sense to me.
ASKER
Ok, I got that to work, Thanks.
But I have a different problem now.
When the report comes out it list every order by date from the same supplier.
I want it to tell me how many orders were taken by each supplier for the specified parameters. I dont need to know each date an order was taken, just the total number of orders during that time.
This was working the way I wanted it to when I was basing the crosstab querey off a pre defined query.
Ex:
what I DON'T Want
Customer1 1 order
customer1 1 order
customer2 2 orders
customer2 2 orders
What I WANT:
Customer1 2 orders
Customer2 4 orders
Hope I;m beeing clear on this.
But I have a different problem now.
When the report comes out it list every order by date from the same supplier.
I want it to tell me how many orders were taken by each supplier for the specified parameters. I dont need to know each date an order was taken, just the total number of orders during that time.
This was working the way I wanted it to when I was basing the crosstab querey off a pre defined query.
Ex:
what I DON'T Want
Customer1 1 order
customer1 1 order
customer2 2 orders
customer2 2 orders
What I WANT:
Customer1 2 orders
Customer2 4 orders
Hope I;m beeing clear on this.
Hi
In query design grid click Totals (sigma) button, if u notice the bottom pane u will see it will change. (I belive it should already be selected in case of crosstab query, if it is just do the following)
Now lets take your 2 fields Customers and Orders as an example.
In bottom pane just change the total for "Orders" field to "Sum" from drop list, leave the total field of Customers to "Group by". So that would looks like similar to following:
Field : Customers Orders
Table : Table1 Table1
Total : Group By Sum (u can change it to Count if u
wanna count orders instead.
Good luck
In query design grid click Totals (sigma) button, if u notice the bottom pane u will see it will change. (I belive it should already be selected in case of crosstab query, if it is just do the following)
Now lets take your 2 fields Customers and Orders as an example.
In bottom pane just change the total for "Orders" field to "Sum" from drop list, leave the total field of Customers to "Group by". So that would looks like similar to following:
Field : Customers Orders
Table : Table1 Table1
Total : Group By Sum (u can change it to Count if u
wanna count orders instead.
Good luck
ASKER
Thanks you For all your help.
I have just about got it.
There is one more issue with this that hopefully you can help me with.
All the data is correct amd it is working well
but when I run the report it ask me twice for the date paramters.
It will ask me start date and end date then go off and then ask me start date and end date again and then it shows the report and all the data is correct but why is it askin my 2 times for the date parameters?
When I run the query by itself I only get asked once.
To make this issue worse, it doesn't happen all the time .
Any Suggestions?
Thanks again for your help
I have just about got it.
There is one more issue with this that hopefully you can help me with.
All the data is correct amd it is working well
but when I run the report it ask me twice for the date paramters.
It will ask me start date and end date then go off and then ask me start date and end date again and then it shows the report and all the data is correct but why is it askin my 2 times for the date parameters?
When I run the query by itself I only get asked once.
To make this issue worse, it doesn't happen all the time .
Any Suggestions?
Thanks again for your help
I am not sure why its doing that, u can try to update Ms Office Service pack from microsoft.com and see.
If you wanna go a little step ahead i can tell you a better user friendly solution which is providing "parameters" through forms.
Pls let me know.
If you wanna go a little step ahead i can tell you a better user friendly solution which is providing "parameters" through forms.
Pls let me know.
ASKER
Thast sounds like a good idea.
I would like to know how to do that.
I would like to know how to do that.
Hi pauls
Here are few simple steps which doesn't need any coding and thats what Access is user friendly and let u do a lot of stuff without actual coding.
1. Create a Form in design view
2. Look for a "toolbox" icon on toolbar and click on it.
click on "Combo Box" icon and click inside form, wizard will start, by using default option click next select yr table, select yr field (for eg: if yr query criteria is based on customer field, then select customer field) finish the wizard and remember the name of combo box.
u can rename it by right clicking on properties but i wil keep it "Combo1" as an example.
3. Now look for "command button" icon from tool box toolbar
click on it and click inside form anywhere, wizard will start from Categories click Report operations and from right column select "preview report" click next and select yr report name, select finish and a button will appear on yr form.
Save yr form, by default it will save as "form1" if u don't already have any forms present, otherwise give anyname and remember it.
Here i am caling it as "FrmMyForm".
Thats it. Now open yr crosstab query in design view.
In criteria box where it use to be "[Enter emp name] if u remember or whatever u actually had it, change it to following:
[Forms]![FrmMyForm]![Combo 1]
Change the same in Parameters from Query menu.
Save the query, now open yr form, from yr combo box select yr desired customer name and click on button, yr report will appear with exact criteria.
Hope u will like it, no input no criteria.
Good luck
Here are few simple steps which doesn't need any coding and thats what Access is user friendly and let u do a lot of stuff without actual coding.
1. Create a Form in design view
2. Look for a "toolbox" icon on toolbar and click on it.
click on "Combo Box" icon and click inside form, wizard will start, by using default option click next select yr table, select yr field (for eg: if yr query criteria is based on customer field, then select customer field) finish the wizard and remember the name of combo box.
u can rename it by right clicking on properties but i wil keep it "Combo1" as an example.
3. Now look for "command button" icon from tool box toolbar
click on it and click inside form anywhere, wizard will start from Categories click Report operations and from right column select "preview report" click next and select yr report name, select finish and a button will appear on yr form.
Save yr form, by default it will save as "form1" if u don't already have any forms present, otherwise give anyname and remember it.
Here i am caling it as "FrmMyForm".
Thats it. Now open yr crosstab query in design view.
In criteria box where it use to be "[Enter emp name] if u remember or whatever u actually had it, change it to following:
[Forms]![FrmMyForm]![Combo
Change the same in Parameters from Query menu.
Save the query, now open yr form, from yr combo box select yr desired customer name and click on button, yr report will appear with exact criteria.
Hope u will like it, no input no criteria.
Good luck
ASKER
Thanks, But how would I do this for a date parameter query?
I need to find records between 2 dates.
For this it seems easier to run the report and just enter the start date and end date.
I need to find records between 2 dates.
For this it seems easier to run the report and just enter the start date and end date.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is great! It works Perfect!
I'm going to change this in my other queries.
Makes me look like im an expert in this.
Thanks Alot for your help!
I'm going to change this in my other queries.
Makes me look like im an expert in this.
Thanks Alot for your help!
In your crosstab query (query design grid) enter yr parameters in criteria for example: [Enter emp name]
Then select Parameters option from Query Menu and enter same for example: [Enter emp name] in data field u can type Text and it will work.
If u are using a form to supply parameters then that would be same for example : [Forms]![Form1]![Emp name]
Hope it work.