Query 2 databases

  • Thread starter Thread starter JohnB
  • Start date Start date
J

JohnB

First, I have very little experience with Access queries, so I'm not even
sure if this is possible.

I have 2 databases. Both contain a field called Stock Number. One by one,
I need to read the stock number in Database-A and search for it in
Database-B. If it is in Database-B, do nothing. If the stock number from
Database-A is not found in Database-B, write that stock number to a file,
either Access or Excel.

How would I do this? Do I need a script?

TIA
 
A database is a collection of tables and the data is in a table, not
directly in the database (well, the database owns the table which owns the
data...). So, I assume you meant TWO tables inside ONE database. (If the
second table is in a second database, IMPORT it with ( through a table -
link ) to the first database).

Next, start a new query, using the wizard to find unmatched records. Follow
the wizard instructions.


Use that generated query to transfer its result, if it is ever required.


Vanderghast, Access MVP
 
I gave it a try, but something didn't work.

The second table is in second database. So I linked the table.
I created a query, selecting the appropriate tables/fields. But then on the
next screen I was told a relationship didn't exist, and the relationship
screen popped up. I created the realtionship between the 2 tables/fields
and saved that. When clicked on Finish, it said it couldn't open the query,
it would have to open it in Design view. After closign that (I got
nothing), and opening it again, I get a message saying "type mismatch".

Where did I mess up?
 
A type mismatch occurs when you compare two fields of incompatible data type
(both must be text, or both must be number, as example).

Did you created a new query using the query wizard: "Find Unmatched Query
Wizard" ?


If so, can you see the data from the linked table itself? It is possible
that the linked table has some data which is not valid, mainly if the linked
table is not from Access, but, as example, for Excel.


Vanderghast, Access MVP
 
See inline...

Michel Walsh said:
A type mismatch occurs when you compare two fields of incompatible data
type (both must be text, or both must be number, as example).
Click to expand...
I just checked, and both are text
Did you created a new query using the query wizard: "Find Unmatched Query
Wizard" ?
Click to expand...
No. How do you do that?
 
Ok, I found how to do the "Find Unmatched Query Wizard". But for some
reason the results are ALL records in Database-A.
 
If you compare strings, it is possible that they differ due to space
padding: "Hello " is not the same as "Hello" due to the extra spaces.
That is how 'dumb' a comparison can be, with computer.

Edit the query in its SQL view. Locate the keyword ON. You should have
something like:

.... ON tableA.fieldA = tableB.fieldB ...


change it to

.... ON REPLACE(tableA.fieldA, " ", "" ) = REPLACE(tableB.fieldB, " ", "" )
....


where the "" is two double quotes.


I assume you are using Access 2002 or later (else, the function REPLACE
won't exist).


Vanderghast, Access MVP
 
I get "Syntax Error" on this:
ON REPLACE(00 Veh Inventory.Stock Number, " ", "" ) =
REPLACE(Products.ProductStock, " ", "" )
 
Ok, not a real problem, it is just that your table name has space in it and
it is an 'illegal' name. It is easy to make it run again:

ON REPLACE([00 Veh Inventory].[Stock Number], " ", "" ) =
REPLACE(Products.ProductStock, " ", "" )



note the use of [ ] around the illegal names (table and field). Be sure
you don't write:

[00 Veh Inventory.Stock Number]


ie.: stop the table name with ] and start the field name with [, letting
the dot well in place between the two.



Vanderghast, Access MVP
 
It accepted that. But I still get all the records from the original table
in the resulting table. Something else must be wrong. The only records
that should show up in the resulting table are those from Table-B that
aren't in Table-A



Michel Walsh said:
Ok, not a real problem, it is just that your table name has space in it
and it is an 'illegal' name. It is easy to make it run again:

ON REPLACE([00 Veh Inventory].[Stock Number], " ", "" ) =
REPLACE(Products.ProductStock, " ", "" )



note the use of [ ] around the illegal names (table and field). Be sure
you don't write:

[00 Veh Inventory.Stock Number]


ie.: stop the table name with ] and start the field name with [, letting
the dot well in place between the two.



Vanderghast, Access MVP



JohnB said:
I get "Syntax Error" on this:
ON REPLACE(00 Veh Inventory.Stock Number, " ", "" ) =
REPLACE(Products.ProductStock, " ", "" )
Click to expand...
Click to expand...
 
Oh, maybe you should exchange the role of tableA and tableB, in your query
(in your answers to the wizard)? ... Although I doubt that no records in
tableA is in tableB... Maybe it is the fields you use which are not the
correct ones? Is [Stock Number] a kind of stockID while ProductStock a
quantity, as example? so, if that is the case, one of the fields will be the
wrong one. Other than that, I can't say. The query works fine, for that
purpose, when only one field (per table) is to be used as 'criteria' for
finding un-matched records.


Vanderghast, Access MVP


JohnB said:
It accepted that. But I still get all the records from the original table
in the resulting table. Something else must be wrong. The only records
that should show up in the resulting table are those from Table-B that
aren't in Table-A



Michel Walsh said:
Ok, not a real problem, it is just that your table name has space in it
and it is an 'illegal' name. It is easy to make it run again:

ON REPLACE([00 Veh Inventory].[Stock Number], " ", "" ) =
REPLACE(Products.ProductStock, " ", "" )



note the use of [ ] around the illegal names (table and field). Be sure
you don't write:

[00 Veh Inventory.Stock Number]


ie.: stop the table name with ] and start the field name with [,
letting the dot well in place between the two.



Vanderghast, Access MVP



JohnB said:
I get "Syntax Error" on this:
ON REPLACE(00 Veh Inventory.Stock Number, " ", "" ) =
REPLACE(Products.ProductStock, " ", "" )



If you compare strings, it is possible that they differ due to space
padding: "Hello " is not the same as "Hello" due to the extra
spaces. That is how 'dumb' a comparison can be, with computer.

Edit the query in its SQL view. Locate the keyword ON. You should have
something like:

... ON tableA.fieldA = tableB.fieldB ...


change it to

... ON REPLACE(tableA.fieldA, " ", "" ) = REPLACE(tableB.fieldB, " ",
"" ) ...


where the "" is two double quotes.


I assume you are using Access 2002 or later (else, the function REPLACE
won't exist).


Vanderghast, Access MVP


Ok, I found how to do the "Find Unmatched Query Wizard". But for some
reason the results are ALL records in Database-A.



A type mismatch occurs when you compare two fields of incompatible
data type (both must be text, or both must be number, as example).

Did you created a new query using the query wizard: "Find Unmatched
Query Wizard" ?


If so, can you see the data from the linked table itself? It is
possible that the linked table has some data which is not valid,
mainly if the linked table is not from Access, but, as example, for
Excel.


Vanderghast, Access MVP


I gave it a try, but something didn't work.

The second table is in second database. So I linked the table.
I created a query, selecting the appropriate tables/fields. But
then on the next screen I was told a relationship didn't exist, and
the relationship screen popped up. I created the realtionship
between the 2 tables/fields and saved that. When clicked on Finish,
it said it couldn't open the query, it would have to open it in
Design view. After closign that (I got nothing), and opening it
again, I get a message saying "type mismatch".

Where did I mess up?




message A database is a collection of tables and the data is in a table, not
directly in the database (well, the database owns the table which
owns the data...). So, I assume you meant TWO tables inside ONE
database. (If the second table is in a second database, IMPORT it
with ( through a table - link ) to the first database).

Next, start a new query, using the wizard to find unmatched
records. Follow the wizard instructions.


Use that generated query to transfer its result, if it is ever
required.


Vanderghast, Access MVP


First, I have very little experience with Access queries, so I'm
not even sure if this is possible.

I have 2 databases. Both contain a field called Stock Number.
One by one, I need to read the stock number in Database-A and
search for it in Database-B. If it is in Database-B, do nothing.
If the stock number from Database-A is not found in Database-B,
write that stock number to a file, either Access or Excel.

How would I do this? Do I need a script?

TIA
Click to expand...
Click to expand...
Click to expand...
 
After looking at this some more, I think you're right, the information in
that field, for the 2 tables, is probably not the same.

But thanks for helping me get headed in the right direction.



Michel Walsh said:
Oh, maybe you should exchange the role of tableA and tableB, in your query
(in your answers to the wizard)? ... Although I doubt that no records in
tableA is in tableB... Maybe it is the fields you use which are not the
correct ones? Is [Stock Number] a kind of stockID while ProductStock a
quantity, as example? so, if that is the case, one of the fields will be
the wrong one. Other than that, I can't say. The query works fine, for
that purpose, when only one field (per table) is to be used as 'criteria'
for finding un-matched records.


Vanderghast, Access MVP


JohnB said:
It accepted that. But I still get all the records from the original
table in the resulting table. Something else must be wrong. The only
records that should show up in the resulting table are those from Table-B
that aren't in Table-A



Michel Walsh said:
Ok, not a real problem, it is just that your table name has space in it
and it is an 'illegal' name. It is easy to make it run again:

ON REPLACE([00 Veh Inventory].[Stock Number], " ", "" ) =
REPLACE(Products.ProductStock, " ", "" )



note the use of [ ] around the illegal names (table and field). Be
sure you don't write:

[00 Veh Inventory.Stock Number]


ie.: stop the table name with ] and start the field name with [,
letting the dot well in place between the two.



Vanderghast, Access MVP



I get "Syntax Error" on this:
ON REPLACE(00 Veh Inventory.Stock Number, " ", "" ) =
REPLACE(Products.ProductStock, " ", "" )



If you compare strings, it is possible that they differ due to space
padding: "Hello " is not the same as "Hello" due to the extra
spaces. That is how 'dumb' a comparison can be, with computer.

Edit the query in its SQL view. Locate the keyword ON. You should have
something like:

... ON tableA.fieldA = tableB.fieldB ...


change it to

... ON REPLACE(tableA.fieldA, " ", "" ) = REPLACE(tableB.fieldB, " ",
"" ) ...


where the "" is two double quotes.


I assume you are using Access 2002 or later (else, the function
REPLACE won't exist).


Vanderghast, Access MVP


Ok, I found how to do the "Find Unmatched Query Wizard". But for
some reason the results are ALL records in Database-A.



A type mismatch occurs when you compare two fields of incompatible
data type (both must be text, or both must be number, as example).

Did you created a new query using the query wizard: "Find Unmatched
Query Wizard" ?


If so, can you see the data from the linked table itself? It is
possible that the linked table has some data which is not valid,
mainly if the linked table is not from Access, but, as example, for
Excel.


Vanderghast, Access MVP


I gave it a try, but something didn't work.

The second table is in second database. So I linked the table.
I created a query, selecting the appropriate tables/fields. But
then on the next screen I was told a relationship didn't exist, and
the relationship screen popped up. I created the realtionship
between the 2 tables/fields and saved that. When clicked on
Finish, it said it couldn't open the query, it would have to open
it in Design view. After closign that (I got nothing), and opening
it again, I get a message saying "type mismatch".

Where did I mess up?




message A database is a collection of tables and the data is in a table,
not directly in the database (well, the database owns the table
which owns the data...). So, I assume you meant TWO tables inside
ONE database. (If the second table is in a second database, IMPORT
it with ( through a table - link ) to the first database).

Next, start a new query, using the wizard to find unmatched
records. Follow the wizard instructions.


Use that generated query to transfer its result, if it is ever
required.


Vanderghast, Access MVP


First, I have very little experience with Access queries, so I'm
not even sure if this is possible.

I have 2 databases. Both contain a field called Stock Number.
One by one, I need to read the stock number in Database-A and
search for it in Database-B. If it is in Database-B, do nothing.
If the stock number from Database-A is not found in Database-B,
write that stock number to a file, either Access or Excel.

How would I do this? Do I need a script?

TIA
Click to expand...
Click to expand...
Click to expand...
 
Back
Top