Counting Across Rows ?? 2

[cdogstu99]

I have a table as follows:

Company Car1 Car2 Car3 Car 4 Cat1 Cat 1P Cat2
JONES 1 1P 1 1
SMITH 2 1 2 2
TERRY 2 1 3 3

I've created the last three fields in my table to represent the values in the table for each Car. How do I get a count of the values summing across for each record? For example for JONES, Cat1= 3, Cat1P = 1, Cat2= 0. , etc, etc.

Thanks!

 

[dhookom]

Are you sure you want to store a value that can be calculated? This is generally not a good idea. Also, is it possible to normalize your table structure? I wouldn't do any more work on this until the tables were in a better, more flexible structure.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cdogstu99]

ok, then instead of creating the last three fields, would it be possible to run a query to calculate the count of each category for each particular company?

 

[lespaul]

First, as Duane mentioned, you should really think about normalizing your table structure (see Fundamentals of Relational Database Design for more details).

A second option would be to normalize this table in a query:

SELECT Company, 'Car1' As CarNumber, Car1 As CarValue FROM tblName
UNION
SELECT Company, 'Car2', Car2 From tblName
UNION
SELECT Company, 'Car3', Car3 From tblName
UNION
SELECT Company, 'Car4', Car4 From tblName

Save this query.

Now this result of this query will look like:

Company      CarNumber        CarValue
  JONES        CAR1              1
  JONES        CAR2              1P
  JONES        CAR3              1
  JONES        CAR4              1
  SMITH        CAR1              2
  SMITH        CAR2              1
  SMITH        CAR3              2
  SMITH        CAR4              2
  TERRY        CAR1              2
  TERRY        CAR2              1
  TERRY        CAR3              3
  TERRY        CAR4              3

now that the data is normalized you can just use a COUNT function and group the data as appropriate.


It's hard to figure out exactly what you want the results to be, you may need to add an IIF clause to determine if the CarNumber should be something different if there is a P in the value, something like this:

SELECT Company, IIF(InStr('P', Car1) > 0, 'Car1P', 'Car1') As CarNumber, VAL(Car1) As CarValue FROM tblName
UNION
SELECT Company, IIF(InStr('P', Car2) > 0, 'Car2P', 'Car2'), VAL(Car2) From tblName
UNION
SELECT Company, IIF(InStr('P', Car3) > 0, 'Car3P', 'Car3'), VAL(Car3) From tblName
UNION
SELECT Company, IIF(InStr('P', Car4) > 0, 'Car4P', 'Car4'), VAL(Car4) From tblName

this would result in a data set that looks like:

Company      CarNumber        CarValue
  JONES        CAR1              1
  JONES        CAR2P             1
  JONES        CAR3              1
  JONES        CAR4              1
  SMITH        CAR1              2
  SMITH        CAR2              1
  SMITH        CAR3              2
  SMITH        CAR4              2
  TERRY        CAR1              2
  TERRY        CAR2              1
  TERRY        CAR3              3
  TERRY        CAR4              3

Leslie

 

[dhookom]

I would create a union query (quniCars) to normalize your table:
SELECT Company, 1 as CarNum, [Car1] as Cat
FROM tblAsFollows
WHERE [Car1] is not Null
UNION ALL
SELECT Company, 2, [Car2]
FROM tblAsFollows
WHERE [Car2] is not Null
UNION ALL
SELECT Company, 3, [Car3]
FROM tblAsFollows
WHERE [Car3] is not Null
UNION ALL
SELECT Company, 4, [Car4]
FROM tblAsFollows
WHERE [Car4] is not Null;

Then, create a query like:
SELECT Company, Cat, Count(*) as NumOf
FROM quniCars
GROUP BY Company, Cat;

If you need these fields across, then create a crosstab query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

great minds think alike!!!

Leslie

 

[cdogstu99]

wow, thank you guys! you helped avoid a lot of headache. I understand normalization; unfortunately the data I inherited is a mess, and it would probably take years to fix it properly. Once again, this place amazes me; great work!

 

[dhookom]

cdogstu99,
Glad to be of assistance. I think Leslie and I have been at this too long that we are hitting the submit button about the same time. Apparently he has the time to be a bit more thorough than I do (that's a good thing).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

(it's SHE!!) All those years doing data entry before I got to do the "real" computer work!!

les


Leslie

 

[dhookom]

Sorry about that! I guess seeing the "Paul" part of the name confused my gender recognition confused. Or can I suggest my confusion springs from a bit of knowledge of the "Les Paul" guitar?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

no problem, but i thought I'd clear it up!!

have a nice day!

les

 

[shaoul]

thank you to lespaul and dhookom for responding to this post - i've got a highly denormalized (or really a never-normalized database) for a client on a quick job and needed a fast answer to get them some reports generated. (their budget doesn't support a full normalization rework at the moment)

thanks to both of your tips it is now possible to get them what they need.

many bows.