Summary calculations for report

[JD2775]

Hi guys,

I have a pretty basic database that I need to add a summary report/layout to. The database tracks the number of light bulbs out in a warehouse, and will be used on an iPad. There are 10 "wings" of warehouses, A-I, with each wing having a certain number of warehouses within it. I have one table in the database, tbl_data_tracking, with the fields:

Date

Warehouse

Metal Halide (type of bulb)

High Pressure Sodium (type of bulb)

I have 10 layouts (one for each wing) and 10 value lists of warehouses. For example the A Wing has 9 warehouses, so the "A Wing" value list is (A1, A2......A9.)

On each layout is the Date field, Warehouse field (value list/radio buttons), Metal Halide (Radio buttons 0-10) and High Pressure Sodium (Radio Buttons 0-10). The two bulb fields default to 0. The user presses a "New Record" button after each entry to complete all the houses in a wing, then goes to the next layout/wing.

What I want is a report that calculates the total number of each type of bulbs that are out PER WING based off a date entered (or current date). This would all be on one layout for ease of viewing. Also a field that calculates the grand total for each type of bulbs that are out, across all layouts.

Any ideas how to go about this? Again, I am relying solely on value lists for the Warehouses, and #bulbs out per type. If this is incorrect let me know as well.

Thanks!

[comment]

I think you should have one field to indicate the bulb type, and one field for the quantity. Then it's easy to summarize the quantities by [whatever you want] using a single summary field, just by sorting the records.

I am missing the part where warehouses are associated with wings - such as a Warehouses table with a Wing field.

Finally, I don't see why you need a layout for each wing.

[JD2775]

I think you should have one field to indicate the bulb type, and one field for the quantity. Then it's easy to summarize the quantities by [whatever you want] using a single summary field, just by sorting the records.

That makes sense, and is an easy fix...

I am missing the part where warehouses are associated with wings - such as a Warehouses table with a Wing field.

There aren't any Warehouse tables I guess. All I have is 10 custom value lists, one per wing, that populates the "Warehouse" field (in a radio button format) per layout. Would it be better to have these actually added as a table, rather than value list(s)?

The only table I have is the Data Entry table (Date, Warehouse, Metal Halide, High Pressure Sodium). I am guessing this should change?

Finally, I don't see why you need a layout for each wing.

This is strictly for font size and ease of viewing/tapping on the iPad. 3 radio button fields with a font size of 24-36 is easy for the user to see. If I cram all the wings onto one layout it would be pretty much impossible to capture the data with ease and accuracy. Of course this goes back to my DB structure itself, maybe it should change?

I'd attach what I have so far but I don't have "permission to load that type of file" for some reason. It's just a .fp7 file. Strange

Thanks

[comment]

The only table I have is the Data Entry table (Date, Warehouse, Metal Halide, High Pressure Sodium). I am guessing this should change?

If you want to summarize by wing, then there must be a "map" somewhere linking each warehouse to a wing. The value list could be based on this too, but that's just gravy.

If I cram all the wings onto one layout it would be pretty much impossible to capture the data

Why can't one and the same layout show all wings, one at a time?

[JD2775]

If you want to summarize by wing, then there must be a "map" somewhere linking each warehouse to a wing. The value list could be based on this too, but that's just gravy.

Ahhh, maybe I do need a table then.,...tbl_Warehouse with the fields "Wing" and "Warehouse", linked to the Data Entry Table? This way you could summarize by wing.

Why can't one and the same layout show all wings, one at a time?

I think it could, I just need to lay it out differently. Change the radio-button to drop down menus for Warehouse field, something like that. Or better yet, have a drop-down for Wing, that activates a drop-down for Warehouse so your menu isnt too long to scroll down.

One other question I had, you mentioned I should have a bulb type field, rather than have the 2 bulbs be their own "field". That makes sense, but how would I enter the data into ONE record? It seems to be I would have to do this:

Warehouse= A1, Type =Metal Halide, Number_Out= 5

*new record*

Warehouse=A1, Type=High Pressure Sodium, Number_Out =5

Basically you would need 2 records for every Warehouse, is that correct? Is there a way around that? Seems redundant

Thanks

[comment]

Warehouse=A1, Type=Metal Halide, Number_Out=5

Warehouse=A1, Type=High Pressure Sodium, Number_Out=5

What is common between the two "transactions"? Why are they currently in the same record?.

[JD2775]

Warehouse=A1, Type=Metal Halide, Number_Out=5

Warehouse=A1, Type=High Pressure Sodium, Number_Out=5

What is common between the two "transactions"? Why are they currently in the same record?.

I guess nothing. They are unique. I just wanted a way to record both at the same time without hitting "new record" in between, hence my flawed set-up of fields "Metal Halide" and "High Pressure Sodium" to record both at once. It seems like that is not the right way to do it though, and they need to be unique records?

Also, does my quote above make sense about adding another table with the fields Wing and Warehouse and relating that to the Data table?

Thanks

[comment]

I just wanted a way to record both at the same time

But what do you mean by "at the same time"? Are there always two transactions taking place at the same time?

Also, does my quote above make sense about adding another table with the fields Wing and Warehouse and relating that to the Data table?

Yes.

[JD2775]

But what do you mean by "at the same time"? Are there always two transactions taking place at the same time?

Ok, well, as an example...you are recording the Date and the Bulb "outage" at the same time, correct? I wanted to record both types of Bulb outages on the same Form, same record. It seems like I would need 2 lines (records) though instead. I dont know if that makes any sense of not. For a visual...

Record 1

03/16/2012

Warehouse A1

Metal Halide 5

High Pressure Sodium 5

vs.

Record 1

03/16/2012

Warehouse A1

Metal Halide 5

Record 2

03/16/2012

Warehouse A1

High Pressure Sodium 5

I guess the bottom line is there are approx 60 warehouses where this data will be recorded. It is a difference between having to do 2 entries per warehouse (120 lines of data) vs 1 entry (60 lines). It would take less time I think on an iPad to do 1 entry (record) encompassing all the data, but that may not be the proper set-up for the database and collecting the data as you said...

[comment]

Perhaps I have misunderstood the issue: if you are concerned about the convenience of data entry, you could build your user interface in such way that the user enters the date once, then selects a wing, and enters two numbers for each warehouse in that wing.

However, behind the scenes, this would still result in having an individual record for each entered number.

[JD2775]

Perhaps I have misunderstood the issue: if you are concerned about the convenience of data entry, you could build your user interface in such way that the user enters the date once, then selects a wing, and enters two numbers for each warehouse in that wing.

However, behind the scenes, this would still result in having an individual record for each entered number.

Yep, that would be ideal. Behind the scenes I realize it would be 2 records, but how do I get around that in the data-entry layout? Do I have duplicated fields for Bulb Type and Number? Or? I'm trying to visualize it

[comment]

Here is a rather rough sketch of how it could work:

WarehouseTX.zip

[JD2775]

Hey,

Thanks so much for putting that together. I was able to replicate this and figure out how it works. Makes perfect sense to duplicate the tables in the graph, link it as you have and use global fields. 2 questions:

What is the dash '-' field for in the Warehouse table? I can't figure out it's use...

and..

What would the calculations be, given your set-up to show totals for Type1 and Type2 per wing based off a certain date, and the grand total based off a certain date as well? Also, what table should these calculated fields reside in? Or should they be in a seperate table?

Thank you again!

[comment]

What is the dash '-' field for in the Warehouse table? I can't figure out it's use...

It's just a visual separator, it's not actually used for anything.

What would the calculations be, given your set-up to show totals for Type1 and Type2 per wing based off a certain date,

You only need a summary field [Total of Quantity] in the Transactions table. To produce the report, find the transactions you want to include (by date?) and sort them by type and by wing. Use sub-summary parts to show the sub-totals, and a grand summary part for the grand total (the same summary field will provide all of these).

[JD2775]

Ahhh ok. That makes sense. Ok one more question...

I want to pre-load all the Warehouse Wings and Warehouses into a table. Do I need a new table for this? The reason being is in the Data Entry table I want to have a pop-up custom value list of the Wings, and then I want a pop-up list of Warehouses that are related to that wing. That way you don't choose from a value list of 60+ Warehouses, more like 7-8. (this was the part i was screwing up with before, having a different layout for each wing)

Thanks

[comment]

The Data Entry layout is based on the Warehouses tables - so you only need to perform a find to show the warehouses of selected wing only (see attached).

I am not sure why you need a pop-up of the individual warehouses themselves.

WarehouseTX2.zip

[JD2775]

Ahhhh ok, it just clicked. You enter the Warehouse once in the Warehouse table, associated with a Wing. The data entry table shows ALL the warehouses at the same time. The only data you are actually entering is the quantity for Type 1 and Type 2. Now it makes sense.

When I said pop-up, I didnt visualize all the warehouses already being shown in a table view, or narrowed down by a "Find". Your way is more efficient.

[JD2775]

I hate to keep bugging you with this, but I am having a hard time figuring out the Reporting and sub-summary. I created a new field in the Transactions Table called "Summary", type-Summary-TotalCount.

I created a blank report based off the Transactions Records. I set it up as a List View Report. I inserted all the appropriate fields into the Body, then adding a "Trailing Grand Summary" at the bottom. That is only thing that works, it totals everything. When I add a sub-summary part in between, it either shows the Grand Summary again, or no data at all. Ive got my "Missing Manual" in front of me, still cant figure it out. First time doing this kinda thing. Maybe I am dragging the wrong fields in, from the wrong tables? Hmmmmm

[comment]

Hard to tell without seeing your file. See if the attached can help. Note the match between sub-summary parts and the sort order.

WarehouseTX3.zip

[JD2775]

Hi,

I don't want to bog down this thread anymore than I have...if you get a second can you shoot me an email? jdenault00 AT yahoo.com. I can't figure this out. If you are too busy don't worry about it. Thanks man