Trouble Counting/Separating Records

[dgaletar]

I am new to access (I am using Access 2010 version) and I am getting crazy. Consider also that I am a novice with DB in general, so now I am at the point that I am very confused... please help.

I have a database that keeps track of all of our work trucks. So far it has worked perfectly. Now my boss wants us to track how many of each size of windshield wipers we use. Now I know that this doesn't sound difficult, but it has driven me nuts for over a week now!!!

The problem is that each truck can have up to 3 windshield wipers on it... (1) DS (Drivers side), (1) PS (Passenger side), and (1) Rear.

The only information that I need to capture is the total quantity of each size wiper. Example, truck #1 uses a 18" wiper on the DS, a 16" wiper on the PS, and none on the rear. While truck #2 uses a 18" wiper on the DS, a 18" wiper on the PS, and a 12" wiper on the rear. That means that we use (1) 16" wiper, (3) 18" wipers, and (1) 12" wiper.

Seems simple, but for the life of me I can't figure out how to do this!!!

I have attached a zip of the database for your review.

**On the Navigation Page, under "General Details:", select the "- View and/or Edit All Vehicle Records" link. In the bottom right hand corner, in the "SERVICE SPECS" subform is where the wipers input fields are located. The dropdowns get their information from the "Wipers" table, and the wipers information is collected in the "SERVICE_SPECS" table.

Good luck and THANK YOU!!!

dgaletar

[dgaletar]

BTW, I just found this code in another forum. Does anybody think that I am on the right track by trying to use this???

[Seth Schrock]

I don't believe that Allen Browne's code is what you are looking for. Try looking into crosstab queries. I believe that is more along the line of what you are looking for. Crosstab Queries should help you along. Let us know if you have trouble with it.

[zmbd]

Dgaletar:
Welcome back.

I would go with Seth on this and offer the option of a pivot table as well.

>
Just a reminder, most of us will not open an unsolicited attachment as a general precation. It should not be taken as a personal attack - just that people will often be infected without any knowledge.

You should also remember from your past experience, simply stating that your code "doesn't work," posting code or a database without explaining what, if any, troubleshooting has been performed, and expecting someone to help doesn't usually result in much of an answer and may result in your thread being deleted.

Instead, please tell us what you were expecting to happen, what actually happened, for each error: the EXACT title, error number, and descriptions that occurred and at what line in your posted code the error occurred. These are the minimum requirements for posting a question of this nature.

[dgaletar]

Hey Seth, thanks for the info. Unfortunately I am still pretty stuck. I guess that my issue is with the layout of the tables.

The way that I have it set up now is (1) table for the list of wiper sizes (titled "Wiper_Sizes"), and three fields added to the "SERVICE_SPEC" table. The three fields are labeled "DS", "PS" & "Rear" respectively.

When I try to create a crosstab query, I get the following issues:

1. If I use the "Wiper_Sizes" table for the crosstab query results, I get an error saying that "the table doesn't have enough fields".
2. If instead I use the "SERVICE_SPEC" table, I choose "DS", "RS" & "Rear" as the Row Headings, then I don't know what to use as the Column headings.

How can I get the "Wiper_Sizes" as the Row Headings, and the "DS", "RS" & "Rear" fields as the results (counted)?

PS - I also tried creating 4 different tables, "Wiper_Sizes", "DS", "PS" & "Rear", and that didn't work. And I tried creating 2 different tables, "Wiper_Sizes", and "Wiper_Loc" (which contained the "DS", "PS" & "Rear" fields), and that also didn't work.

[zmbd]

Would you go back into the query design and try again using each method you listed and post the SQL for them?
Very hard to help when all we get is the basic - it doesn't work.

Even if it gives you an error when ran, you can still switch to the SQL view and <ctrl><c>&<p> the underlying SQL.

You might also read thru: Crosstab-query-techniques I have a few other tutorial links for CTQ that might be easier to follow.....

[Seth Schrock]

You wouldn't want the four different tables, but the two would probably work. However, since there would be a many to many relationship between those two tables, you would need a join table between them. You would then run the crosstab query on the join table.

[dgaletar]

OK, hang in there! I'm trying to do what zmbd asked. I'll be right back...

[dgaletar]

OK, I (we) did it!!! Using a crosstab query I was able to total the sizes for each field seperatly; “DS”, “PS” & “Rear”. In other words, when I run the crosstab query now I get the following:

(See screenshot 1 [top table] below)

…which is correct as to the test data that I entered into the table.

The only issue that I now have is to be able to add up the sizes in total. In other words, like this:

(See screenshot 1 [second table] below)

I await your directions…

[Seth Schrock]

As Z stated before, it is hard to help without the SQL. Post what you have inside code tags. The [CODE/] button will insert them for you. Just place your code between the start and end tags.

[dgaletar]

Sorry 'bout that! I keep forgetting that I can do that...

TRANSFORM Count(SERVICE_SPECS.[CUA#]) AS [CountOfCUA#]
SELECT SERVICE_SPECS.DS, Count(SERVICE_SPECS.DS) AS [Total Of DS], SERVICE_SPECS.PS, Count(SERVICE_SPECS.PS) AS [Total Of PS], SERVICE_SPECS.Rear, Count(SERVICE_SPECS.Rear) AS [Total Of Rear]
FROM SERVICE_SPECS
GROUP BY SERVICE_SPECS.DS, SERVICE_SPECS.PS, SERVICE_SPECS.Rear
ORDER BY SERVICE_SPECS.DS, SERVICE_SPECS.PS, SERVICE_SPECS.Rear
PIVOT SERVICE_SPECS.[CUA#];
 

[zmbd]

The link I posted tells you how...

[dgaletar]

Right, the paragraph that says"To show the total of all the columns in the row, just add the value field again as a Row Heading"

But the issue is that the columns that display the sizes (12, 14, 18, etc.) are not in the same order. So if I add a "value field" to the query, it won't be adding the right totals. Right?