Data matching

[htadis]

good day all !

I have a table. Also i have a text file which some of the fields are matching with my table fields ( lets say field A and B ). Now, i need to do compare of these A & B of my table against A & B of the text file and give a result as follows;

1. "Field A" not in the table but in the text file (un-matching data to be shown )
2. "Field A" not in the text file but in the table (un-matching data to be shown )
3. "field B" mismatches

Would appreciate lot your kind contribution.

brgds
Htadis.

 

[plog]

A little too generic for good specific advice. Most likely this is going to involve a UNION query that ties together a few LEFT JOIN queries.

Can you post some sample data from both data sources (include table and field names) and then what you expect your query to return based on that sample data? Be sure to post enough sample data to cover all cases.

 

[htadis]

Plog,

many thanks for your kind attention.

shall try to give some example data as follows;

Table : tblMain
Fields : ContainerNO, VoyageNO, size, POL, POD, Weight

XXXU1234567,225A,DV20,SGSIN,LKCMB,25.20
XXXU1234568,225A,DV20,SGSIN,LKCMB,12.50
XXXU1245788,225A,DV40,SGSIN,GBFXT,13.00
AAAU9999999,225A,HC40,MYPKG,NLRTM,25.00
BBBU1111111,225A,HC40,MYPKG,AEJEA,15.00
CCCU999999,225A,HC40,KRPUS,LKCMB,25.00
QQQU1122334,225A,HC40,SGSIN,LKCMB,17.00


Text file : loadlist.txt
Fields : ContainerNO, Vessel, VoyageNO, size, POL, POD, Weight, Yard, Status

XXXU1234567,GAA,225A,DV20,SGSIN,LKCMB,25.20,K83,L
XXXU1234568,GAA,225A,DV20,SGSIN,LKCMB,12.50,L12,L
XXXU1245788,GAA,225A,DV40,SGSIN,FRFOS,13.00,K12,T
AAAU9999999,GAA,225A,HC40,MYPKG,NLRTM,25.00,N15,T
BBBU1111111,GAA,225A,HC40,MYPKG,AEKLF,15.00,O14,T
CCCU999999,GAA,225A,HC40,KRPUS,LKCMB,25.00,P10,L
AAAA1234567,GAA,225A,HC40,SGSIN,LKCMB,12.00,K13,L
UUUU9999999,GAA,225A,DV20,MYPGU,INMAA,15.00,N14,T

Result should be as follows

1. In table but not in the text file.
QQQU1122334,225A,HC40,SGSIN,LKCMB,17.00

2. In text file but not in the table
AAAA1234567,GAA,225A,HC40,SGSIN,LKCMB,12.00,K13,L
UUUU9999999,GAA,225A,DV20,MYPGU,INMAA,15.00,N14,T

3. POD mismatch containers
XXXU1245788 in table = GBFXT, in text file = FRFOS
BBBU1111111 in table = AEJEA, in text file = AEKLF

Trust above will be clear enough. Appreciate your kind help.

brgds
htadis

 

[plog]

Excelllent job on the data. Now, because your results have different fields (#1 has 6, #2 has 9) this can't come together in 1 tidy query. Which is fine, so I will give you 3 queries--1 for each result set.

Step 1: Bring in loadlist.txt into Access and call the table it creates 'LoadList'.

Step 2: Use this SQL for Result 1:

SELECT tblMain.*
FROM tblMain
LEFT JOIN LoadList ON LoadList.ContainerNO = tblMain.ContainerNO
WHERE LoadList.ContainerNO IS NULL;

Now, that query assumes a match using only the ContainerNO field of both tables. If more fields need to match, let me know.

Step 3: Use this SQL for Result 2:

SELECT LoadList.*
FROM LoadList
LEFT JOIN tblMain ON LoadList.ContainerNO = tblMain.ContainerNO
WHERE tblMain.ContainerNO IS NULL;

Same thing with this query, it is only matching the ContainerNO between the two to find out which ones aren't in tblMain.

Step 4: Use this SQL for Result 3:

SELECT tblMain.ContainerNo, tblMain.POD, LoadList.POD
FROM tblMain
INNER JOIN LoadList on LoadList.ContainerNO = tblMain.ContainerNO
WHERE (tblMain.POD<>LoadList.POD);

This one, may not be exactly what you want. It again matches on ContainerNO, but then it compares the POD fields and only includes them if they are different. So, if the ContainerNO & POD fields match but another field doesn't (e.g. size, VoyageNo, etc.) it will not show on this result. If you want to include other fields, let me know and we can work through that.

Let me know any questions you have.

 

[htadis]

Plog,

many thanks ! You understood exactly what i wanted even-though i forgot to mentioned that the matching should be done basis the container number. What you given are exactly matching with my requirement.

I have a question.

1. where should i put this codes. ( sorry if it's look a silly question but i'm not too familiar with Access )

 

[plog]

For each piece of code do this:

Create->Query Design

Then close the dialog box that pops up showing all your tables. Next in the upper left hand corner, just below the File option there should be a button that says 'SQL', click on that and paste one piece of code there. The button that used to say 'SQL' will now show an icon and say 'Design' underneath it, click it and your results will appear.

Of course, you first have to load your .txt file into the LoadList table.

 

[htadis]

Many thanks Plog. Let me go through all your lessons and see the outcome. Shall revert.

thanks !

 

[htadis]

Plog,

It is working perfectly. Many many thanks.

Is there any way to run all these three simultaneously and get one single report ?

 

[plog]

You can build 4 reports--1 for each result set, then a master report that has the other 3 as sub-reports.

 

[htadis]

Wow !! Working nicely as i needed.......

many thanks Plog all the support rendered.