Re: Advanced query issue

It looks to me then that you cannot actually link the tables as they are
since the keys are different.

It you do have a direct one to one match, I'd be sorely tempted to cheat!!

Add another "long" field to each table.

Use two initial queries to put the tables in the ordder you want (A, B, C by
the looks of it) as the recordset in a bit of code that just puts numbers in
the new field. (Increasing by one each time). You may even get away with just
adding an autonumber field.
Do this with both tables, and then write a query linking just by your new
field.

(Once the tables are in the right order, you really want to link by
sequential record number if I understand properly.)


OR

have a translation table which effectively links the text (if it can be done).
So that the text from table A is in one field (1) , and that from B - field
2 (Table c)
then you want
a.A = b.A, a.B = b.B, a.C = C.1, b.C = C.2

Though this only works if the text is itself unique within each table, OR if
the translation is always the same. So probably fails due to several empty C
fields in table a.

OR

write some code to give you a 'proper'unique key.
again, write queries to put the two tables in the order you want, and add
your NewKey field.

then set rsA for the table a query, and rsB for the table b one.

from there on in a simple loop can add the same key to both tables
counter = 1

do until rsA.Eof

rsA.MoveFirst
rsB.MoveFirst
rsA.Edit
rsA![NewKey] = counter
rsA.update
rsB.Edit
rsB![newKey] = counter
rsB.Update
rsA.MoveNext
rsB.MoveNext
counter = counter + 1
Loop






Any good??



"Alexankius" wrote:

the first query results in the right number of rows but a.c is empty.

the second query results in a huge number of rows where either a.c or b.c
have a value. Should i "group/distinct" this to get distinct rows with both
a.c and b.c values?

"Michel Walsh" wrote:

Hi,



SELECT b.a, b.b, a.c, b.c
FROM tablea As a RIGHT JOIN tableb as b
ON a.a=b.a AND a.b=b.b


should do. That assumes tableb has all the possible occurrence of the two
fields being compared. If not, have:


SELECT a, b FROM tableA
UNION ALL
SELECT a, b FROM tableB


saved as queryC, then


SELECT c.a, c.b, a.c, b.c
FROM (queryC as c LEFT JOIN tablea as a ON c.a=a.a AND c.b=a.b)
LEFT JOIN tableb as b ON c.a=b.a AND c.b=b.b



Does the result sounds good?

Vanderghast, Access MVP

"Alexankius" <Alexankius@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B91226E6-D1F7-472E-83ED-925B42F385C3@xxxxxxxxxxxxxxxx
yep, table A could also have
1,1,"oranges"

quick example:
Table A
PrimaryKey1a, 1,1,"oranges"
PrimaryKey1b,1,1,"Whatever"

Table B
Primarykey2a,1,1,"oranges"
Primarykey2b,1,1,"whateverr"

we want to link PrimaryKey1 to PrimaryKey2
in this case
PrimaryKey1a=PrimaryKey2a and
PrimaryKey1b=PrimaryKey2b

"DavidAtCaspian" wrote:

So Table A could also have
1,1, "Oranges"

or/ and 1,1, "Whateverr" ???



"Alexankius" wrote:

Hi,
thanks for the reply.
To answer your questions the unique identifier of each table is jointly
columns a,b and c.

Whereas columns a and b are numbers and therefore are 'matchable'
column c
contains words. For example we may have the following:

A, B, C -- These are Columns
1,1,"Whatever" --Table A
1,1,"Whateverr" -- Table B

I cant link the above as the two words are not identical.

So, I want a new table with columns a.a, a.b, a.c, b.c

To get this the only way i can think of is to group by A,B then sort
the
C's. At this stage presumably as the C's are in alphabetical order they
should match each other on a one to one basis. And at this stage create
a
table with a.a , a.b, a.c and b.c. In excel this is very easy but if
you have
more than 65k rows it becomes an issue.

The issue really is how to match words that are not exactly spelt the
same
way...

If you need an example pls let me know.


"Michel Walsh" wrote:

Hi,


Can you define what you mean by "linking" the two tables, in this
context?
Do you mean to get, as result, a table with 3 columns, A, B, and C,
with
records from table a, plus the records in table b not in table a? If
any
merge of that kind is involved, you need a UNION query. If this is as
I
described, then


SELECT A, B, C FROM tablea
UNION ALL
SELECT b.A, b.B, b.C FROM tableb As b LEFT JOIN tablea As a ON
b.A=a.A WHERE
a.A is NULL


would do. Note the second select is the query about finding unmatched
records. I also used UNION ALL, since there is no dup. UNION ALL is
faster
than UNION since UNION removes dup, but that is at the cost of extra
work.


Hoping it may help,
Vanderghast, Access MVP


"alex" <alex@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CAA97DC8-97F5-4FBD-9C6A-8266BFB164BF@xxxxxxxxxxxxxxxx
Hail all,

Suppose we have 2 tables (20 rows each) as follows:
Table a with Columns A, B and C and an Index
Table b with Columns A, B and C and an Index

Columns A & B contain numbers. A ranges from 1 to 50 while B ranges
from 1
to 1000. Column C contains words.

Each record in Table A matches uniquely another record in Table B.
At the
moment there is no link between the tables.

The question is how to link the 2 tables.

If we run a select query (per table) Grouping by A & B and Count
the C's,
this count is identical to both tables. i.e. the data essentialy is
the
same.

So the query that i though of is Group by A & B and sort
Alphabetically
the
C's. Then copy one index to the other, thus linking the two tables.

But i can't make it happen..

any suggestions much appreciated.






.

Relevant Pages

  • Re: Problem with Dual subselect
    ... So I've constructed a UNION QUERY as follows. ... FROM TABLEA AS A ... B.STATUS_DATE FROM [TABLEB] As B ... A Lookup property on the foreign-key field ...
    (microsoft.public.access.queries)
  • Re: use a result as a FIELD in the design grid
    ... I have a key TableA that maps each station's flow (and ... Station: FlowField: TankLevel: PumpRun: ... OK, now from the previous query, I know that the FlowField ... So in a new query grid where TableA is linked to TableB ...
    (microsoft.public.access.queries)
  • Re: Advanced query issue
    ... FROM tablea As a RIGHT JOIN tableb as b ... That assumes tableb has all the possible occurrence of the two ... I also used UNION ALL, ...
    (microsoft.public.access.queries)
  • Re: Finding matching records
    ... >> An inner join in a query will return only records where the join ... Suppose you have two tables, TableA and TableB, with these fields: ... A_ShouldMatch has a match in the B_ShouldMatch field in TableB. ... create a new query in Design View and add both TableA and TableB to the ...
    (microsoft.public.access.modulesdaovba)
  • Re: Help with INSERT
    ... If tableA has three records, a, b, and c, while tableB as four records, 1, ... from a SELECT query, no obligation to necessary use a table). ... > I'm very new to Access, and trying to learn SQL at the same time. ...
    (microsoft.public.access.queries)