Count totals per item from different tables

[dgaletar]

Hello. I'm sure this is a pretty easy one for most of you, so I'll get right to it.

What we need to do is to keep track of how many of what size windshield wipers we use on our trucks. (I know, right!?!)



I created a table named "Wipers" with four columns in it; ID [which is an autonumber], Wiper Blade (DS)[Number], Wiper Blade (PS)[Number], and Wiper Blade (Rear)[Number].

I also created a query, "qryWipers", which is used for the drop-down list for each column. We basically (at this point) have 4 sizes; 16", 18", 20" & 22". This query also has the same three columns as its relevant table, "Wipers".

Now I just need to be able to add up how many of each size we are using. The problem is that, when I created another query called "qryWipers_Count" and added the "Count" command in the queries "Total" Column, I simply got a total of how many wiper blades are being used for the drivers side, the passengers side & the rear. Not how many of each size.

I couldn't care less how many 18" wiper blades are used on the passenger side or the drivers side. Just how many 18" wipers are used in all.

Where am I going wrong here???

Thx!

dgaletar

[Siiig]

If you wouldn't mind elaborating a bit. How are you keeping track of the different size wiper blades used in the original table? Is each row representative of a different size wiper?

I ran with the assumption of the above, and I'm assuming your TABLE looks something like this...

Size	DS	PS	Rear
16	2	3	4
18	4	6	2
20	2	5	3
22	3	2	4

What I did was create a query (much like what you have), and added an expression to summarize in the last column.

Count: [DS]+[PS]+[Rear]

Size	DS	PS	Rear	Count
16	2	3	4	9
18	4	6	2	12
20	2	5	3	10
22	3	2	4	9

You could also not include the DS/PS/Rear columns, which would simply leave you with the total.

[dgaletar]

Um, no... my table looks nothing like that. Your table makes sense... mine is a mess! Mine looks more like this:

ID	DS	PS	Rear
1	16	16	16
2	18	18	18
3	20	20	20
4	22	22	22

So lets start from scratch. How do I make my table look like that???

[dgaletar]

Come on, Siiig... how are you getting there??? Are you having the end user just type a number into the form, and then the table counts how many of that number were used? Or are you using the first column, "Size", as their choice in a drop-down, and then calculating the entries in each column selected? There are over a million "How To's" for Access on the internet... and I can NEVER find the one that I need!!!

Arg...

[JoeM]

What exactly is your table supposed to be tracking? It seems odd to me that their doesn't seem to be any sort of "ownership" id field, so that each record is associated with a particular truck, order number, etc.

Then I would normalize your data table (it currently is not normalized they way you have created it). Instead of having three sizes per record, you should have three records, with a Location field to tell you which one you are looking at. Then there is only one size field, which makes doing COUNTS very easy, using Aggregate (Totals) Queries.

So you data table might look something like this:

Truck ID	Location	Size
1	DS	16
1	PS	16
1	Rear	16
2	DS	18
2	PS	18
2	Rear	18
3	DS	20
3	PS	20
3	Rear	20
4	DS	22
4	PS	22
4	Rear	22

[dgaletar]

I am not sure what is going on with me this week... I've been sick... but I just can't wrap my head around this one! I built this entire database from scratch with no prior Access experience, & now I'm stumped on the wiper blades!?!

Alright JoeM, I see where you r going with this, & I think that I understand. So if I set up the table like you have it, how would I setup the form? As of right now I have drop downs on the main form for each category, DS, PS, & Rear. The drop downs were obviously getting their choices from my table. Therefore my table wasn't recording any of the "used" info.

Please help me start from scratch & fix this STUPID WIPERS MESS!!!

[JoeM]

I built this entire database from scratch with no prior Access experience

My stomach flipped a little when I read this. I once tried creating a database with no prior experience in Access or any understanding of relational databases, and it was an unmitigated disaster. Access is not nearly as intuitive as Excel, and if you do not know what you are doing, it is very easy to program yourself in a corner and drive yourself crazy. After a few months of getting nowhere, I stopped, read up on Relational Databases and introductory Access books, and started over from scratch. It made a huge difference. If this is going to be a big project, I would highly recommend doing this yourself, or hiring a consultant to create the database for you.

Here is a good write-up on relational databases and rules of normalization: http://www.deeptraining.com/litwin/d...aseDesign.aspx

Back to your question at hand. Here is how I would attack it.
You should have a separate "Trucks" tables that has your truck details, along with your "Truck_ID".
Create a Form from that (we'll call it "Trucks_Form").

Then, create a form from your "Wipers" table (I prefer a Continuous or Datasheet form in this instance). We'll call this our "Wipers" form.

Then go back to your "Trucks_Form", and create a Subform, placing your "Wipers" form on this "Trucks_Form", linking on the "Truck_ID" field.

[dgaletar]

OK JoeM, take a deep breath... I think it's gonna be OK. I probably shouldn't tell you that I am a mechanic, just trying to make our garage a little more organized. lol

Anyway, I zipped it here for you to take a look at. I'm sure that there are many things that I probably could have done smarter, but all in all I don't think it's that bad.

Take a look and let me know.

As for the *^#% Wipers situation, you will see that I added the DS, PS & Rear Wiper options to the "SERVICE_SPECS" table. I left the "Wipers" table alone, as I am not sure that I am finished with it yet, and I left the "qryWiper_Count" query alone also for the same reasons.

Any thoughts, however brutal, would be GREATLY appreciated!

dgaletar

Vehicles.zip

[JoeM]

Unfortunately, I am not able to download files off the internet at my current location (corporate policy), and probably will not be at a place where I can do so until at least tomorrow night.

However, I think I have something for you that might work with the data structure you currently have (your table example you posted yesterday).

If you create a new query, go to SQL View, and paste this code in the resulting window, it should take the data you have and make it look like the table I posted:

SELECT Wipers.ID, "DS" as Loc,Wipers.DS as Wiper_Size
FROM Wipers
UNION
SELECT Wipers.ID, "PS" as Loc,Wipers.PS as Wiper_Size
FROM Wipers
UNION 
SELECT Wipers.ID, "Rear" as Loc,Wipers.Rear as Wiper_Size
FROM Wipers;

Give this query a name (we'll call it "tmpWipers" for this example).

Now create another new query, using the "tmpWipers" query as the source of this new query.
- Add the "Wiper_Size" and ID fields to the query fields to return.
- Click on the Aggregate (Totals) button (looks like a Sigma) in the Query Builder.
- This will add a Totals Row with the phrase "Group By" under each field.
- Under the "ID" field, change "Group By" to "Count"

Now, if you view your results, it should show you the total count of each wiper size.

[dgaletar]

No () I'm pretty sure that I did everything right, but it looks like it is just counting the number of entries for each of the ID's. Example: (TABLE) ID#1= DS[16], PS[16], Rear[16] (QUERY RESULTS) Wiper Size[16], CountofID[3].

I haven't even put any wiper sizes into the form yet!?!

[JoeM]

Maybe I misunderstood what you are looking for.

Is your data structured like the table you posted in post #3?
What would your expected output from that sample look like?

[dgaletar]

(I hope that this works...) Here is a screenshot of the "Wipers" table as posted in post #3:

It shows the table setup exactly like it is in post #3 (except that we added a size; 12).

The sample that you showed me in post #5 would work fine, if we could make that work. Ultimately, I need to be able to view the specs for the vehicle, in which the wiper sizes for that vehicle are shown, ALONG with a report that can show us how many 16" wiper blades we use, so that we know how many to keep in stock.

[JoeM]

I do not see an example of your expected output, all three of your attachments looks the same and appear to be your data sample.

Ultimately, I need to be able to view the specs for the vehicle, in which the wiper sizes for that vehicle are shown,

Do you have some other specs that you want to see? In another table perhaps? If so, you should be able to link the tables on your vehicle ID in a query, and use that query as the basis of a Form or Report.

ALONG with a report that can show us how many 16" wiper blades we use, so that we know how many to keep in stock.

That is what I was showing you in my previous post, a count all of total number of each sized wipers being used.

[dgaletar]

I do not see an example of your expected output, all three of your attachments looks the same and appear to be your data sample.

Yes. That is correct. I do not have any output. That's what I'm trying to get to.

Do you have some other specs that you want to see? In another table perhaps? If so, you should be able to link the tables on your vehicle ID in a query, and use that query as the basis of a Form or Report.

Yes. This is already finished. I have about 20 tables or so with data in them. My boss just asked me to add these damn windshield wipers to the existing info.

That is what I was showing you in my previous post, a count all of total number of each sized wipers being used.

I know, but I didn't get any totals, except for a total of ID's. I'm pretty sure my table is setup all wrong. If we were doing this in a blank database, how would you go about doing it? We have truck numbers, several different wiper sizes, and three potential positions on each vehicle. And... go!

[JoeM]

Sorry, I have been away for a few days.

Yes. That is correct. I do not have any output. That's what I'm trying to get to.

I know you do not have any output. What I am asking you is based on the sample data you posted, what SHOULD your output look like?
If I can see what you are expecting to see, it may help clarify exactly what is it that you are trying to do.