Form to Generate Report (1 Viewer)

psyc0tic1

Access Moron
Local time
Today, 00:04
Joined
Jul 10, 2017
Messages
360
I am sure this has been answered somewhere but my searching has not turned it up.

What I am trying to do:

I have a table called tbl_auditdata that has many fields. Data from several different input forms are first created by one form, updated by a second form and finally completely finished updating by a third form (three different groups of people manipulating records). The fields in tbl_auditdata I am concerned with are:

PartNumber (there is another table listing all part numbers called tbl_parts and is a dropdown field in the first input form used to create the record)
TotalReceived
TotalVisInspected
TotalVisBad
TotalVisGood
TotalFunctTested
TotalFunctBad
TotalFunctGood

I am trying to make a form that has one thing on it... The Part Number dropdown.

With this dropdown I want to choose a single part number to generate a report that: (*note: there are many records related to each different part number).
1) Displays the part number selected from the form.
2) Totals all quantities of that selected part number received (in tbl_auditdata the field is named TotalReceived).
3) Totals all quantities of that selected part number visually inspected (in tbl_auditdata the field is named TotalVisInspected).
4) Totals all quantities of that selected part number visually bad (in tbl_auditdata the field is named TotalVisBad).
5) Totals all quantities of that selected part number visually good (in tbl_auditdata the field is named TotalVisgood).
6) Totals all quantities of that selected part number functionally tested (in tbl_auditdata the field is named TotalFunctTested).
7) Totals all quantities of that selected part number functionally bad (in tbl_auditdata the field is named TotalFunctBad).
8) Totals all quantities of that selected part number functionally good (in tbl_auditdata the field is named TotalFunctGood).

I am trying to determine some statistics on this data in an easy to use form for my bosses.

Here is a scenario. In tbl_auditdata I can filter by a single part number. I have the row at the bottom of the TotalReceived field in the datasheet that totals the row and I can see how many have been received. I have the same below the other fields I need to see totals for as well.

This shows me some of the information I would like to have show up on a report when someone chooses a part number from the dropdown on the form.

Is there anyone that can see their way clear to help me with this?
 
Last edited:

bastanu

AWF VIP
Local time
Yesterday, 22:04
Joined
Apr 13, 2010
Messages
1,401
Why not build a totals query that groups by the PartNumber and sums the rest of the fields? You can then use that in a form and have the combo box simply filter by the part number.

Cheers,
Vlad
 

psyc0tic1

Access Moron
Local time
Today, 00:04
Joined
Jul 10, 2017
Messages
360
Why not build a totals query that groups by the PartNumber and sums the rest of the fields? You can then use that in a form and have the combo box simply filter by the part number.

Cheers,
Vlad

Can you show me an example of the totals query? I don't know how to make that... been trying.
 

bastanu

AWF VIP
Local time
Yesterday, 22:04
Joined
Apr 13, 2010
Messages
1,401
Have a look at the attached file.

Cheers,
Vlad
 

Attachments

  • TotalsQuery.zip
    25.1 KB · Views: 222

psyc0tic1

Access Moron
Local time
Today, 00:04
Joined
Jul 10, 2017
Messages
360
Have a look at the attached file.

Cheers,
Vlad

Ok I understand what you were saying now... I didn't before. This part I understand... it is the part about having the form for choosing the part number and the submit button opening a report they can see.

Thank you for the example.
 

psyc0tic1

Access Moron
Local time
Today, 00:04
Joined
Jul 10, 2017
Messages
360
I have no troubles looking at the data totaled and filtered in the table datasheet view... it is the fancy part I was asking about. it is for the bosses who don't like the datasheet view.
 

bastanu

AWF VIP
Local time
Yesterday, 22:04
Joined
Apr 13, 2010
Messages
1,401
Have a look at the form and the report, you will need to adjust it to fir your needs.

Cheers,
Vlad
 

Attachments

  • TotalsQuery_ver2.zip
    56.8 KB · Views: 211

psyc0tic1

Access Moron
Local time
Today, 00:04
Joined
Jul 10, 2017
Messages
360
Have a look at the form and the report, you will need to adjust it to fir your needs.

Cheers,
Vlad

Thank you very much for your help. I pretty much have what I am looking for by modifying your first example. rather than selecting part numbers and generating a report they can just click on the link to the report and it shows all part numbers with the sums.

However... for some reason it is showing the ID of the part number instead of the part number itself.

I have been scratching my head trying to remember how to fix that but am coming up blank.
 

Attachments

  • screenshot.jpg
    screenshot.jpg
    57.2 KB · Views: 199

June7

AWF VIP
Local time
Yesterday, 21:04
Joined
Mar 9, 2014
Messages
5,424
Report is showing ID not part number? Include table with part number in the report RecordSource query and bind textbox to part number field.
 
Last edited:

bastanu

AWF VIP
Local time
Yesterday, 22:04
Joined
Apr 13, 2010
Messages
1,401
You need to change the control source of the text box to be PartNumber instead of PartNumberID.

Cheers,
Vlad
 

psyc0tic1

Access Moron
Local time
Today, 00:04
Joined
Jul 10, 2017
Messages
360
So... the data stored in the tbl_auditdata in the row called PartNumber is pulling the part number from the tbl_parts in the row called PartNumber

The query using tbl_auditdata in the datasheet displays the part number but if I call it in a report it shows the ID number of the part number. The properties in the report does not have a "Row Source" for me to specify the information to retrieve the part number from the tbl_parts table.
 

bastanu

AWF VIP
Local time
Yesterday, 22:04
Joined
Apr 13, 2010
Messages
1,401
Look at the RecordSource property of the report, edit it to add the part number then change the controlsource property of the text box on the report to make it part number instead of ID.
 

psyc0tic1

Access Moron
Local time
Today, 00:04
Joined
Jul 10, 2017
Messages
360
Look at the RecordSource property of the report, edit it to add the part number then change the controlsource property of the text box on the report to make it part number instead of ID.

Yea it wasn't that easy. I figured it out though. The field for the PartNumber was set as a plain text box and it needed to be a combo box so I could specify a row source from the tbl_parts table and that fixed the part number versus part number ID issue.

Also rather than doing the SumOftotal etc in the query I added those to the report and was able to hide the detail portion of the report and only show the sums and that worked as well.

The only thing I am struggling with at the moment is the percentages I am trying to add.

This sums the total received for each part number:
Code:
=Sum([TotalReceived])

This sums the total visually inspected for each part number:
Code:
=Sum([TotalVisInspected])

Needing to figure out the code to show the percentage of TotalVisInspected from the TotalReceived for each part number.

This does not work:
Code:
=Sum([TotalVisInspected])/([TotalReceived])
 

bastanu

AWF VIP
Local time
Yesterday, 22:04
Joined
Apr 13, 2010
Messages
1,401
Try =Sum([TotalVisInspected])/Sum([TotalReceived])
 

bastanu

AWF VIP
Local time
Yesterday, 22:04
Joined
Apr 13, 2010
Messages
1,401
Glad to hear you got it working!

Cheers,
Vlad
 

psyc0tic1

Access Moron
Local time
Today, 00:04
Joined
Jul 10, 2017
Messages
360
I attached a pic of the resulting report for anyone that wants to see the outcome. Thanks again for all the help.
 

Attachments

  • screenshot.jpg
    screenshot.jpg
    97.7 KB · Views: 205

Users who are viewing this thread

Top Bottom