Question Query: tables w/ "same key" but different detail info

[matticool]

Hi, im kinda new in access so please pardon my noob-ness! i've got trouble creating queries with the my keys.

so these are the information.

I've 2 fields call Market code and Market description (market table),eg.
Market Code Market description
855 AUS
819 UK
901 HKD
321 SGP
654 USA

And i've got any other field called market code too (data table), eg.
Market code
85512
85510
81944
81935
etc,etc

I want to count the number of 855** regardless of the last 2 numbers and show the market descriptions (market table).
Market description Counts
AUS (855) 2
UK(819) 2
HKD(901) 0
SGP
USA
the issue is that i wanna use only the first 3 numbers of market code (data table) to link with Market code (market table).

Can anyone help me with these? your help is much needed! Thanks so much!

 

[PaulO]

Hi, im kinda new in access so please pardon my noob-ness! i've got trouble creating queries with the my keys.

so these are the information.

I've 2 fields call Market code and Market description (market table),eg.
Market Code Market description
855 AUS
819 UK
901 HKD
321 SGP
654 USA

And i've got any other field called market code too (data table), eg.
Market code
85512
85510
81944
81935
etc,etc

I want to count the number of 855** regardless of the last 2 numbers and show the market descriptions (market table).
Market description Counts
AUS (855) 2
UK(819) 2
HKD(901) 0
SGP
USA
the issue is that i wanna use only the first 3 numbers of market code (data table) to link with Market code (market table).

Can anyone help me with these? your help is much needed! Thanks so much!

You need to start by creating a SELECT query on the DATA table and use =trim(left([Market Code],3)) expression to truncate all Market Codes to just three characters only. The "trim" element will help eliminate any leading spaces in the Market Code.

This Query can then be added into a fresh SUM Query, together with the MARKET table, linking via Market Code. Now you will be able to build the count expression you are seeking.

Good luck

 

[matticool]

Thanks so much Paulo! It worked great for me!