two tables ... distinct data = me stumped

[benwatson]

Hi,

I would like to, eventually, create one master list using several tables, the
problem i have is that there is multiple entries for some of the information
which is needed. see example below.

I want to somehow compare the data in table 2 against the data in table 1, i
need to show all of the info in one table in the same order omitting any
missing rows as the other table. i have tried using a distinct query but it
returns 2 occurances for each row. the query needs to be built around title
1 on each table matching but also including the different data in Title 3, in
otherwords i need 2 occasions of every number that appears in title 1.

(just read this and it sounds confusing easiest way i could put it)

Table 1

Title 1 Title 2 Title 3 Title 4 Title 5
12345 227 1234 A1 abc
12345 227 4321 A2 def
54321 227 1234 A1 ghi
54321 227 4321 A2 jkl
67890 228 9876 A9 mno
67890 228 6789 B1 prq
09876 228 9876 A9 stu
09876 228 6789 B1 vwx

column named title 1 needs to contain more that one occurance due to data in
table 3 being slightly different (I need to analize both instances)

Table 2

Title 1 Title 2 Title 3 Title 4 Title 5 Title 6
12345 227 1234 A1 zyx 99
12345 227 4321 A2 wvu 93
54321 227 1234 A1 tsr 94
54321 227 4321 A2 qpo 94
67890 228 9876 A9 nml 84
67890 228 6789 B1 kji 34
09876 228 9876 A9 hgf 04
09876 228 6789 B1 edc 99

please let me know if anyone can help, im stumped.

thanks
Ben

 

[Bob Barrows]

Hi,

I would like to, eventually, create one master list using several
tables, the problem i have is that there is multiple entries for some
of the information which is needed. see example below.

I want to somehow compare the data in table 2 against the data in
table 1, i need to show all of the info in one table in the same
order omitting any missing rows as the other table. i have tried
using a distinct query but it returns 2 occurances for each row. the
query needs to be built around title 1 on each table matching but
also including the different data in Title 3, in otherwords i need 2
occasions of every number that appears in title 1.

(just read this and it sounds confusing easiest way i could put it)

Table 1

Title 1 Title 2 Title 3 Title 4 Title 5
12345 227 1234 A1 abc
12345 227 4321 A2 def
54321 227 1234 A1 ghi
54321 227 4321 A2 jkl
67890 228 9876 A9 mno
67890 228 6789 B1 prq
09876 228 9876 A9 stu
09876 228 6789 B1 vwx

column named title 1 needs to contain more that one occurance due to
data in table 3 being slightly different (I need to analize both
instances)

Table 2

Title 1 Title 2 Title 3 Title 4 Title 5 Title 6
12345 227 1234 A1 zyx 99
12345 227 4321 A2 wvu 93
54321 227 1234 A1 tsr 94
54321 227 4321 A2 qpo 94
67890 228 9876 A9 nml 84
67890 228 6789 B1 kji 34
09876 228 9876 A9 hgf 04
09876 228 6789 B1 edc 99

please let me know if anyone can help, im stumped.

"(just read this and it sounds confusing easiest way i could put it)"
A picture is worth a thousand words: _show_ us the data you want your
query to return from this sample data. use the same format you used for
your sample data.

 

[Allen Browne]

Ben, I think you have given yourself an impossible task here.

Your tables do not appear to have any primary key. Therefore there is no way
to distinguish between duplicates even within one table, and the concept of
duplicate/non-duplicate across multiple tables is meaningless.

Similarly there does not seem to be any field the data is sorted on within a
table, and so the concept of merging and retaining the same order is
meaningless.

In relational database theory, every table must have a primary key, and the
data has no physical ordering (i.e. you must provide a way to sort it.)
Your example data does not appear to meet these requirements.

 

[benwatson]

Hi,

[quoted text clipped - 41 lines]

please let me know if anyone can help, im stumped.

"(just read this and it sounds confusing easiest way i could put it)"
A picture is worth a thousand words: _show_ us the data you want your
query to return from this sample data. use the same format you used for
your sample data.

Thanks for replying. Below is the sample data. i have split it by a
description in the middle. I think it is a bit of an impossible task as
already mentioned in the above post but you may have an answer.

MPAN SSD SSC TPR Meter ID Reg MSID
1500151280911 13/03/1999 227 1359 H04L04939 01 27/08/2008
1500151280911 13/03/1999 227 1360 H04L04939 02 27/08/2008
1500151280949 13/03/1999 227 1359 H02L03654 A7 23/01/2003
1500151280949 13/03/1999 227 1360 H02L03654 A8 23/01/2003
1500151280958 13/03/1999 227 1359 H02L03700 A7 17/01/2003
1500151280958 13/03/1999 227 1360 H02L03700 A8 17/01/2003
1500151281135 11/05/2000 227 1360 H99L04049 A8 11/05/2000
1500151281135 11/05/2000 227 1359 H99L04049 A7 11/05/2000
1500151281144 27/05/2000 227 1360 H99L04050 A8 27/05/2000
1500151281144 27/05/2000 227 1359 H99L04050 A7 27/05/2000
1500151281473 06/08/1999 227 1359 H99L02783 A7 06/08/1999
1500151281473 06/08/1999 227 1360 H99L02783 A8 06/08/1999

This is a copy of the data i am using, the MPAN needs to be on multiple rows
due to there being 2 TPRs per 1 MPAN. although the tables above and below
both contain relatively the same data, there are more descrepancies further
down the list. I need to append the following columns from the table below
to the table above. SUPP, SSC, TPR, MET ID, REG ID and MSID. this is to
allow me to do more indepth comparisons between this data i have, there are
around 90K rows in each table and i have 4 tables in total, these just being
the first two.

MPAN Supp SSD SSC TPR METER_ID
REG MSID
1500151280911 NEEB 05/06/2008 227 1360 H04L04939 02 27/08/2008
1500151280911 NEEB 05/06/2008 227 1359 H04L04939 01 27/08/2008
1500151280949 NEEB 13/03/1999 227 1359 H02L03654 A7 23/01/2003
1500151280949 NEEB 13/03/1999 227 1360 H02L03654 A8 23/01/2003
1500151280958 NEEB 13/03/1999 227 1359 H02L03700 A7 17/01/2003
1500151280958 NEEB 13/03/1999 227 1360 H02L03700 A8 17/01/2003
1500151281135 NEEB 22/10/1999 227 1359 H99L04049 A7 11/05/2000
1500151281135 NEEB 22/10/1999 227 1360 H99L04049 A8 11/05/2000
1500151281144 NEEB 22/10/1999 227 1360 H99L04050 A8 27/05/2000
1500151281144 NEEB 22/10/1999 227 1359 H99L04050 A7 27/05/2000
1500151281473 NEEB 06/08/1999 227 1360 H99L02783 A8 06/08/1999
1500151281473 NEEB 06/08/1999 227 1359 H99L02783 A7 06/08/1999

 

[Bob Barrows]

Hi,

[quoted text clipped - 41 lines]

please let me know if anyone can help, im stumped.

"(just read this and it sounds confusing easiest way i could put
it)" A picture is worth a thousand words: _show_ us the data you
want your query to return from this sample data. use the same format
you used for your sample data.

Thanks for replying. Below is the sample data.

I don't understand. Is this a new set of sample data? I did ask for
sample _results_ based on the sample data provided.

i have split it by a
description in the middle. I think it is a bit of an impossible task
as already mentioned in the above post but you may have an answer.

<snip>

Assuming these two sets of data represent the data from your source
tables, I would like you now to show me what you want a query to display
given that your source tables contain only the sample data you showed.
OK? 3 sets of data in total: the first two being the source data (which
you have already shown) and the third being waht you want the query you
want to design to show.
Then explain what each row in the sample results is and why it needs to
be there.

 

[benwatson]

this will take me a while cos im not much use when it comes to access, will
be in touch!! thanks!!

 

[Bob Barrows]

this will take me a while cos im not much use when it comes to
access, will be in touch!! thanks!!

Don't even use Access. just look at the sample data and type out new
rows and columns of results based on the data you are looking at. Do it
in notepad
Start by deciding what column names need to be in the results and type
them in. Then add the rows of data.