Re: Advanced query issue

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: Advanced query issue
    ... To answer your questions the unique identifier of each table is jointly ... Note the second select is the query about finding unmatched ... I also used UNION ALL, ...
    (microsoft.public.access.queries)
  • Re: Advanced query issue
    ... To answer your questions the unique identifier of each table is jointly ... Note the second select is the query about finding unmatched ... I also used UNION ALL, ...
    (microsoft.public.access.queries)
  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... The list in each case has to have the same number & types of fields (for example, if the first Query begins with a Date/Time field, the second one should do so as well). ... I like to keep my Union Queries short and simple and do the rest of the work elsewhere. ... tblMentors comprises Subject Mentors and Professional Mentors and the Placement subform has a combo for each - the Subject Mentor combo puts the chosen MentorID in the SubjectMentorID field and the Professional Mentor combo puts the chosen MentorID in the ProfessionalMentorID field. ...
    (microsoft.public.access.queries)
  • Re: Graphical Union-Query Builder?
    ... If you are limiting the input in each of the sub-queries in your Union ... Use the filtered queries as the input to your Union query: ... Most "functional IT users" will not know anything about SQL. ... I didn't know that fields of subsequent queries in a Union could have ...
    (microsoft.public.access.queries)
  • Re: Help! Union Query has started crashing!
    ... It's tblSupport on the RLR_SUPPORT_INFOTERRA.mdb database. ... I think I have tracked the problem down to the query ... I also tried a UNION ALL, ... the actual structure of the queries as they have been running fine for weeks. ...
    (microsoft.public.access.queries)