Compare multiple fields in one table (1 Viewer)

[amberjen]

Hello everyone,

I'm a novice with Access. I been searching this forum and YouTube to help with this project I'm trying to complete for work but hit :banghead::banghead: BIG time. I hope someone can HELP ME..

Lots of ideas is going in my head and did a several test query getting very frustrated that I can't get it to work.

So here goes...

Need to compare 3 fields to make sure the numeric number match.

Field 1 ; Field 2 ; Field3
Chase#7865-8876 ; 7865-8876 ; xxxx-8876
BOA#RTU987663 ; RTU987663 ; xxxxxx663
TD#35-908768 ; 35-908768 ; 35xxxxxx

Suggestion...

THanks.

 

[plog]

Your tables are not properly set up.

1. You shouldn't have multiple fields that essentially store the same data. It looks like you have 3 fields to store account number. That's incorrect. Instead you should store 3 account numbers in 3 different records. Tables should accomodate data vertically (with more rows) and not horizontally (with more fields).

2. Discrete pieces of data should be stored discreately. You don't jam somebodies full address into 1 field. Instead you store each piece of the address into each own field (e.g. StreetAddress, City, State, Zip). Since your data has 2 pertinent pieces of data to it--prefix and number, it should be stored as such.

Can you give a big picture view of what this data is for? Also, explain the purpose of what you are trying to accomplish.

 

[amberjen]

@plog - I created a query to generate the 3 fields from 3 different tables (that's was the easy part). These 3 tables have the account number listed as I shown in the fields, I need to make sure they match to field 1. I was thinking doing a string to have query match any right of # from field 1 to field 2 etc.. I hope this make sense.

 

[plog]

Again, I would need to see the big picture of what you are trying to accomplish. Can you demonstrate it with data? Provide 2 sets of data:

A. Starting sample data from your tables. Include table and field names and enough sample records to cover all cases.

B. Expected results from A. Show me what you hope to end up with when you feed in the data from A.

I don't care about your existing query, so show me data from the tables you have and then the data you expect to end up with.

 

[Pat Hartman]

Again, you wouldn't have this problem if you hadn't stored the same data three times so once you validate it, you should fix the tables to eliminate the duplicates and store ONLY the foreign key value. Then you can define what ever your logic is to format the second two fields in queries where they need to be displayed.

Comparing the first two, it seems like you can use the split() function to split the first field on #. Then compare the second item in the array to the second field. The third is more problematic. You are going to have to define how you determine what format the third field is and then write the code to format the first field as the third field and compare the formatted field to the stored field.

If you need to do this in a query, then the code i described above needs to be written as a function.