I am using MS Access 2007.
I am collecting data of different types of businesses within a town area, arranged by streets, the business.
In TableA, I have a list of different specific streets, no duplicates, field: Street; and another, field: NumOfBusinesses.
In TableB, I have a list of different specific businesses, Business; and what street they are on, Street.
eg
TableA STREET..................NUMOFBUSINESSES High Street Sheep Street Silver Street Gold Street TableB BUSINESS................STREET McD's High Street Hair Dresser High Street Shoeshop Gold Street
What I want to be able to do, is count the amount of business per street from TableB and add that into the TableA, corresponding to each street.
I have over 600 streets, so I don't want to have to manually go through each individual count and add it in manually.
Is there a type of query I can use, or even code I could put in as the default value of the "Num...sses" field, so it dynamically changes the query depending on the relevant street in the record?
So in essence, I want TableA to look like this automatically
TableA STREET..................NUMOFBUSINESSES High Street 2 Sheep Street 0 Silver Street 0 Gold Street 1
You can get the results from a query, so do you really need to update a field?
SELECT Tablea.Street, Count(Business)
FROM TableA
INNER JOIN Tableb
ON TableA.Street = TableB.Street
GROUP BY Tablea.Street
It is not usually recommended that you store calculated data.
If you really must store the count, you can say:
UPDATE tablea
SET numofbusinesses=
DCount("*","tableb","street='" & Replace(street,"'","''") & "'")
WHERE Street Is Not Null