graphs on a report

[chr1stoper1]

Hi guys

On my database I have a report that runs from a query which uses a prompt in the criteria to filter the information that is required to populate the report. I have also made a graph on the report that uses the same information to populate the graph. So the report should have the written information and the same information in visual form.
The problem I'm struggling to overcome is that because the prompt in the criteria is used in the query when the report is opened the prompt asks for the information you want the report based on and after entering this it fills in the data on the form then puts up the prompt again .If I fill it in it draws the graph.
Is there a way of linking the graph and the data so that I only fill in the info into the prompt once?
Hope that makes sence

 

[Ranman256]

The report should be using the prompt query. Because of this, it should only ask once.
Did you put in extra prompts somewhere?

 

[chr1stoper1]

Hi Thanks for the reply
The report is generated from a query of a query .
The first query has a date criteria to return only results from this year and also a prompt the enter a product number. Then several fault codes.
The query of the query doesn't have the product number or the date and just adds up using the sum function.
The fields in the first query are product number ,date, made, scrap, reason 1 ,reason two, reason 3 ect.
The ideas is I want to return the sum of all made, and scrap and for what reason they were scrap for this year for the entered product number.
Hope that all make some kind of sense

 

[sneuberg]

If you run the report from a form you could put a text box of the form for the product number and then reference the text box in the query, thus avoid the prompt in the first place. Better yet put a combo box on the form and populate it's row source with the product numbers.

 

[chr1stoper1]

Hi thanks for the reply I will give it a try ive never ran a report from a form before. The combo box wouldn't work due to the amount of different product numbers there are several hundred.

 

[sneuberg]

Just in case you don't know to how to reference the forms text box in the query do this.

Make the form and the text box first. I suggest giving the textbox a name rather than leave it like text1. Then add a button to run the report.

Open your query in design view, erase anything in the in the criteria for product number and type Form! (Form followed by an exclamation point) After you type that the intelliSense should present a drop drop from which you can choose the form name. Then type another ! after the form name and your text box should be in the drop down. You should end up with

Forms!theformname!thetextboxname

Your done.

 

[chr1stoper1]

hi thanks for the reply and your help. I did try your suggestion but ive come across a problem . The database has a query of a query .The first query has the product number in it the second doesn't which allows me to sum the qtys .
So I don't seem to be able to sum the qtys of the product name if I use your suggestion.
Using the prompt in the first query allows it to prompt in the second even though the product number is not in the second query ...Hope that makes sense

 

[sneuberg]

This should work. If the product number is available to the query through the form reference it will be just be there any time it was needed before you did this with the form. If you upload your database I'll figure out what's going wrong.

 

[chr1stoper1]

the database is too big even after zippibg so ill try and make a simplified version

 

[chr1stoper1]

View attachment Database2.zip

Find the product numbers in the table ... This is just quick rought copy of what I am getting

 

[sneuberg]

The database you posted didn't have the form so I went ahead and made one which you can find in the attached database. To make it easier to me to test I put a combo box on the form instead of a text box but that doesn't make any difference. It would work in either case as it does in the attached. So I don't know what problems you were having. Would you post the database with the form you made.

Also with a form you could allow select of the start and end dates from combo boxes rather than having fixed values in the query.

 

[chr1stoper1]

Hi Sneuberg

Many thanks for that I was adding it to the second query. I also think the combo box is a better idea than what I was doing and the variable date has given me extra food for thought as well .I may well go back to the beginning and re jig the database a little using the form idea . The database query I sent you was just one of many analysing the data in different ways . As you probably realise I am still fairly new to access but I'm learning all the time .
Thanks again __________________________________