count each occurrence matching another field 1

[EdmCath]

I'm new to this whole access thing. So, please bear with me.
I am putting together a database for a school board. And what I have is a table of portable classrooms and their current location and a table of every school in the district. What I need to do, is make a query as to how many portables are located at one school. I have tried to do it using dcount, but I dont know how to reference the school name from the school table. Here is what I have been trying, maybe somebody can tell me what I am doing wrong.

DCount("[Location]","Portables","[Location]=[School Name]")

Where location is a field in portables table and school name is a field in schools table.

I have gotten it to work this way;

DCount("[Location]","Portables","[Location]='School1'")

Where School1 is a school name in schools table.

Any help in this would be greatly appreciated

 

[PHV]

Have you tried this ?
DCount("[Location]","Portables","[Location]='" & [School Name] & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[EdmCath]

Thank you PHV, you get a gold star and the title of "Freakin' Genius". If you dont mind my asking, what do the ampersands and parenthesis do to the school name to make access recognize it?

 

[PHV]

[School Name]" is a literal constant
.." & [School Name] & ".. means concatenate the value of the variable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[EdmCath]

Ok, here's another one for you. Two of my schools have apostrophes in there names, O'Brien and O'Leary. The count comes up with an error for these two because of improper use of quotations. What can I do to overcome this?

 

[PHV]

Either this:
DCount("[Location]","Portables","[Location]='" & Replace([School Name], "'", "''") & "'")
Or this:
DCount("[Location]","Portables","[Location]=""" & [School Name] & """")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[EdmCath]

DUDE, YOU ROCK!!!!! 2 Stars for you!!!!!

 

[eislander]

Thank you PHV! Your posts helped to solve the first hurdle of my problem.

In a similar vein, could someone answer me this ...

Say I have a table called [T_Transactions] with a field called [F_Customer_Number]. Many records in this table have the same customer number. I have another table [T_Distinct_Customers] which I created by selecting all the distinct values of [F_Customer_Number]. So for example, if my tables were:

T_Transactions
F_Customer_Number ...
----------------- ---
001
002
002
001
003
004
004
004

And [T_Distinct_Customers] is
F_Customer_Number
-----------------
001
002
003
004

I would like the output to be
001 2
002 2
003 1
004 3

So I made the query as so:
SELECT [T_Transactions]![F_Customer_Number], DCount("[T_Transactions]![F_Customer_Number]", "[T_Transactions]", "[T_Transactions]![F_Customer_Number] = '" & [T_Distinct_Customers]![F_Customer_Number] & "'") AS [Count]
FROM [T_Transactions], [T_Distinct_Customers];

The output I get is:
001 2
001 2
001 1
001 3
002 2
002 2
002 1
002 3
...

Any advice would be greatly appreciated.

 

[lespaul]

You shouldn't need the distinct table. Does this work for you?:

Select [T_Transactions]![F_Customer_Number], Count([T_Transactions]![F_Customer_Number]) FROM [T_Transactions] GROUP BY [T_Transactions]]![F_Customer_Number]

Leslie

 

[eislander]

It did. That was quite a brain fart.

Thanks Leslie.

 

[lespaul]

Well, I'm glad I wasn't there to HEAR it!!

glad it worked.

les