Criteria count

[gpurger]

Hi to everyone

I have 2 tables, one holds info about a pc user and what type of pc they have (laptop/ desktop) as a primary unit. The othet table lists the users other pc's if they have any.

What I want is to count the number of laptops and the number of desktops in total.

How and where would i do this?

Cheers

 

[Len Boorman]

Why not have 2 tables.

One for people
One for PC's they have

Would be even better with 3 tables

One for people
One for PC's
One linking People to PC

This is a correctly normalised design

You can then query the PC's table quite easily
If a field in included called type (i.e laptop, desktop etc)

Something like

SELECT tbl_PC.[PC_Type], Count(tbl_PC[PC_Type]) AS [CountOfPC_Type]
FROM tbl_PC
GROUP BY tbl_PC.[PC_Type];

should do the trick

Len

 

[gpurger]

Thanks for that, that has helped alot.

Gordon

 

[Len Boorman]

Have you been introduced to Normalisation. If not then it is something that you should read up on.

The tables and relationships that I suggested would enable you to track all sorts of stuff. If you give each piece of equipment a unique number then the table that links people to PC's would actually link a person to any piece of equipment that was in the PC table. So if the PC table contained printers, docking stations, etc you could easily set up a system to keep track of lots of different bits of kit.

So the tables could become
Tbl_People
Tbl_Equipment
Tbl_People_Equipment

Len