Reports, Subreports, oh my

[Ms Kathy]

Hi All!
I'm using Access 2007. I am a beginner - no formal training just trying to figure it all out; and willing to learn.

What I want to do is create a report that I can use to pull data by a date range. This is the data I need (columns) in the report: #Days worked / Minutes available / Minutes worked / Total Minutes Down / Minutes for each Down Code / Total Batch Weight for each type / and then a calculation

On researching this site, and many others in my Google travels I came to the conclusion that I should create queries first and then use the queries in the reporting. So, I tried to do that. The images attached include: 1 The tables involved 2 Query to count the days worked 3 Query to calculate the number of minutes worked 4 Query that sums the number of cases produced on each line. The problem (at least one of them) is that I don't have a field within these queries of which I can link to the master. I have a unique field in the table but when I use that in the queries I don't get the results I want - such as Query2 then counts each day separately rather than totaling them for the day; and Query3 is based on Query2 so that's no longer accurate either. I'm at a loss as to how to proceed.

Any help is greatly appreciated!

 

[plog]

We don't really have a good idea of where you are going, and only a very vague idea of where you are coming from.

I think the best way for us to help you is for you to provide sample data from your 3 tables, then based on your that sample data what you expect your report to produce.

So, can you post a spreadsheet with 2 tabs? The first would have the table's data (include table and field names) and the 2nd would have what you expect on your report based on that initial data.

 

[Ms Kathy]

Thank you Plog. I've attached what I believe is what you need. Please let me know if I should be sending something else too.

My apologies, but work day here ends in 10 minutes. I will be back on Tuesday and will come back to the forum at that time. Thanks!!

 

[Ms Kathy]

Please don't forget me.

 

[plog]

Your data doesn't jive. On your report you have this as the first entry:

DATE, ITEM, LINE, ...
5/29/2015, 4936, Icing, ...

On your tables tab I can not find any date of 5/29/2015 nor the Item number of 4936. I don't believe the data on the report tab is based on the tables tab.

 

[Ms Kathy]

I'm sorry. No you're right the data on the report is not based on the tables tab. The tables just have dummy data in them. I need to create this report in Access. It is currently a spreadsheet. I think I have all the fields I need in the tables to create this report (that is currently in Excel). Is there something more I can do?

 

[plog]

Provide me with what I asked for?

 

[Ms Kathy]

ok, I will reduce the amount of data in the tables; and then use that data to show in the report. Thank you.

 

[Ms Kathy]

Hi Plog - I'm pretty sure I have everything now. I look forward to hearing your thoughts on this project. (It is confusing to me.) Thank you!

 

[plog]

How are the values for [Total Ava Minutes] determined?

Nevermind, its 550 minutes per shift.

 

[plog]

Another one:

How come CO downcodes aren't part of the total? Is this just a typo?

On your example report, Prod_Shift=2 has 20 minutes of CO down time, but its not added to the total downtime nor subtracted from total time available. Is that correct?

 

[plog]

Me again, no question just a note:

Your Cases per minute field isn't correct. First row is 325 Cases, 550 minutes which should be less than 1 case per minute. However your value is 1.69 which is the exact inverse of what it should be (.591). 1.69 is the minutes per case.

Again, just a note.

 

[Ms Kathy]

CO down time is a typo - I inserted that column and forgot to put the calculation at the bottom. Yes, I now see that the math is wrong on that case per minute (it should be reversed). Thank you.

 

[Ms Kathy]

My apologies - I was so focused on getting the right data from the tables to the report that I didn't double check the calculated fields. I've attached a revised spreadsheet.

 

[plog]

Those were no problem, I figured those were just oversights. However, the last few columns of your report data are troublesome.

Columns K, L & M are unachievable with the data you provided on the Tables tab

Columns N - T make sense until I see you've used 5 rows for that data. Shouldn't those values be rolled up into the first two rows?

 

[Ms Kathy]

I'm probably not understanding about columns K-M, here is what I entered:
Prod_Waste: cell K3 = 600 from the table (typo previously)
Prod_Waste: cell K4 = 300 + 15 from table
Prod_Remix: cell L3 = 250 from table
Prod_Remix: cell L4 = 215 + 401 from table
Prod_Thrift: cell M3 = 0 from table
Prod_Thrift: cell M4 = 20 + 50 from table

Regarding columns N-T
I don't know how to combine these totals. Yes, I DO want them to be rolled up; this is part of what I was hoping to achieve with the reporting . . I'm at a loss as to how to do it though

 

[plog]

For your data in columns K-M you provided no way to attach them to a shift. All your other tables can logically be attached to your Main table (via Prod_ID), that data cannot be.

Attached is a database that generates all the other data for your report. The only queries you need to work with are ProductionReport_sub1, which allows you to configure what date range to use and ProductionReport which produces the final output of data you want.

let me know if you have any questions.

 

[Ms Kathy]

Maybe I didn't go about it right - but I have the field ProdID_Down from table T_Production_Down linked to the field Prod_ID in the main table T_Production_Main. Wouldn't that enable me to get these numbers?

I'll look at the database now. I was not expecting this and I thank you greatly! This may take some time to wrap my head around. I will get back with questions.

Thank you kindly.

 

[plog]

I thought Prod_Waste, Prod_Remix, Prod_Thrift were in a different table that you didn't provide a name for. They are in fact in the Main table. To include those values you would put them in sub query that is based on the Main table (sub2 I think).

 

[Ms Kathy]

I will do that.
Please note that it may be a week or so until I am able to wrap my head around this and get back to you with questions. (I'm just here a day and a half each week.) Please do not consider my tardy response as uninterested. I shall return