matching text records between 2 tables, and dividing by a count of text record and another column

[rwhite]

Hi,

I've been looking around this forum and have tried a few things but I'm unable to get a query working.

I have a form that is pulling in data from an api into a table. This form generates a new row for each entry. I'm trying to match the data from the form to a reference list to work out a percentage of how many people have filled in the form from a certain location.

here's the example:

Form Table columns

Entry ID | Location

1 | London

2 | New York

3 | London

4 | Singapore

Reference Table columns

Location | total number of people

London | 30

New York | 10

Singapore | 5

What I'd like to be able to do is show the percentage for each e.g

London | 6.66%

New York | 10%

Singapore | 20%

I've tried creating a relationship between the Location columns but when 2 duplicate location values are added from the form the relationship breaks.

[rwhite · Accepted solution]

I've got this working now it was a picnic error I had a duplicate value in my reference sheet

[jthomson]

Maybe try making a bridging table that combines the location columns from each table and hacks out all the duplicates?