Linking data from rows in another table

[rowlandsfc]

hi so i am trying to link some data from 1 table to another



table 1

table 2

those are the 2 tables in question, table 1 shows all the wards in south wales and each ward is associated with a UA code, there is around 250 wards and 7 UA codes

the second table shows the CRIMEID and the WARD associated with that crimeid and there is over 150,000 records there

what im looking to do is creat a query that will show me the crimeid the ward associated with that crimeid but also insert a new column that will show the UA that would be associated with each record

any help is appreciated

[pbaldy]

Start a new query and add both tables to the design grid. If there isn't already a join line between the two tables on Ward, click/drag to create one. Then add both fields from the lower table and the UA field from the upper table and run the query.

[rowlandsfc]

Awesome thanks

[pbaldy]

Happy to help and welcome to the site!

[rowlandsfc]

so it seemed to work great but i have since starting to count up how many crimes each UA code has and i seem to have an extra 2000-3000 compared to the amount of entries there actually are for the CRIMEID

SELECT Ward_UA.UA, COUNT(IIF(UA = '00NX',1,NULL)) AS 'Crimes'
FROM Ward_UA INNER JOIN CrimeWardLUT ON Ward_UA.WARD = CrimeWardLUT.WARD
WHERE UA = '00NX'
GROUP BY Ward_UA.UA

UNION ALL

SELECT Ward_UA.UA, COUNT(IIF(UA = '00NZ',1,NULL)) AS 'Crimes'
FROM Ward_UA INNER JOIN CrimeWardLUT ON Ward_UA.WARD = CrimeWardLUT.WARD
WHERE UA = '00NZ'
GROUP BY Ward_UA.UA

UNION ALL

SELECT Ward_UA.UA, COUNT(IIF(UA = '00PB',1,NULL)) AS 'Crimes'
FROM Ward_UA INNER JOIN CrimeWardLUT ON Ward_UA.WARD = CrimeWardLUT.WARD
WHERE UA = '00PB'
GROUP BY Ward_UA.UA

Thats the code im using to count

[pbaldy]

Could there be wards in the first table more than once?

What does this produce?

SELECT Ward_UA.UA, COUNT(*) AS Crimes
FROM Ward_UA INNER JOIN CrimeWardLUT ON Ward_UA.WARD = CrimeWardLUT.WARD
GROUP BY Ward_UA.UA

[rowlandsfc]

Will have a look tomorrow evening when I give it a go
Thanks

[pbaldy]

No problem. You shouldn't need the UNION query to get 3 counts. That will give you all of the wards, but you could add a criteria like:

IN('00NX', '00NZ', '00PB')

to my query to limit it to just those 3.

[rowlandsfc]

No problem. You shouldn't need the UNION query to get 3 counts. That will give you all of the wards, but you could add a criteria like:

IN('00NX', '00NZ', '00PB')

to my query to limit it to just those 3.

UA	Crimes
00NX	45992
00NZ	14652
00PB	15088
00PD	10816
00PF	26571
00PH	8670
00PT	54354

thats what it returns but the amount is too much for the total of entries that are actually there

CRIMEID	WARD
157973	00NZMH
157974	00PTPK
157975	00PHMJ
157976	00PFQA
157977	00NXPS
157978	00PHMC
157979	00PTPB
157980	00PTPC

thats the last load of entries that there is but the query when i add those totals adds up to 176143

[pbaldy]

Can you attach the db here to play with?

[rowlandsfc]

it wont upload for some reason, nothing happens when i try adding the file

[pbaldy]

Try doing a compact/repair and then zipping. That will normally work.

[rowlandsfc]

still not working sadly

[pbaldy]

How big is it zipped? You can email it to me if it's too big to attach here (2MB).

pbaldy
gmail
com