Match Query

  • Thread starter Thread starter accessuser via AccessMonster.com
  • Start date Start date
A

accessuser via AccessMonster.com

Hi,

I'm trying to create a database that will run a match against a text file
that I get everyday. I want to be able to run the text file information
against my database TblSensitiveCustomer and show only the ones that match.

Tables are called TblSensitiveCustomer, TblDailyimport, and TlbMatch. And I
have called QryMatch. I think I need the TlbMatch so it has a place to show
my results, right?

On my QryMatch, I linked SS#, First Name and Last Name from both TblSensitive
Customer and TblDailyimport. Can someone tell me what I have to do next to
complete this?


Thanks in advance!
 
Hi,

I'm trying to create a database that will run a match against a text file
that I get everyday. I want to be able to run the text file information
against my database TblSensitiveCustomer and show only the ones that match.

Tables are called TblSensitiveCustomer, TblDailyimport, and TlbMatch. And I
have called QryMatch. I think I need the TlbMatch so it has a place to show
my results, right?
Click to expand...

NO.

It's a common begginer's assumption that you must put data into a
table in order to report it, or even see it. This assumption is WRONG.

You can create a Query and view the query; base a form on the query;
base a report on the query; edit the query; export the query... it's
*rare* to need to copy the data into a table.
On my QryMatch, I linked SS#, First Name and Last Name from both TblSensitive
Customer and TblDailyimport. Can someone tell me what I have to do next to
complete this?
Click to expand...

This will miss cases where the first or last name is missing,
misspelled, or just spelled differently ("Dave Jones" vs. "David
Jones"). If you can count on the SSN being available and unique, just
join on it. I'd see the query being

SELECT tblDailyimport.SSN, tblDailyimport.FirstName,
tblDailyImport.LastName, <other fields as desired>
FRIM tblDailyimport INNER JOIN tblSensitiveCustomer
ON tblDailyimport.SSN = tblSensitiveCustomer.SSN;

Copy and paste this into the SQL view of a new Query and see if it
doesn't get you the result you need!

You can (and should) base a Form for onscreen viewing, and/or a Report
for printing, on this query; simply open the form to run the query and
see the results in one swell foop.

John W. Vinson[MVP]
 
John,

Thanks for your reply. I just found out that our company uses monarch
software and pull text files. So back to my question, how do i create a
database that will run against the monarch's text files. I have never used
or known anything about monarch, so if you can please give me as much
instructions as possible.

In my tables, I want to have fields such as SS#, CustomerName, DOB. I dont
know how this is going to work. What i want is to have the text files run
against my table list and pull up the exact match records by SS# and
CustomerName. Someone told me that monarch runs the query against the
database, is it true?? If yes, how? what are the codes that I have to write?
Please help me!!!

Thank you again!

John said:
Hi,
Click to expand...
[quoted text clipped - 5 lines]
have called QryMatch. I think I need the TlbMatch so it has a place to show
my results, right?
Click to expand...

NO.

It's a common begginer's assumption that you must put data into a
table in order to report it, or even see it. This assumption is WRONG.

You can create a Query and view the query; base a form on the query;
base a report on the query; edit the query; export the query... it's
*rare* to need to copy the data into a table.
On my QryMatch, I linked SS#, First Name and Last Name from both TblSensitive
Customer and TblDailyimport. Can someone tell me what I have to do next to
complete this?
Click to expand...

This will miss cases where the first or last name is missing,
misspelled, or just spelled differently ("Dave Jones" vs. "David
Jones"). If you can count on the SSN being available and unique, just
join on it. I'd see the query being

SELECT tblDailyimport.SSN, tblDailyimport.FirstName,
tblDailyImport.LastName, <other fields as desired>
FRIM tblDailyimport INNER JOIN tblSensitiveCustomer
ON tblDailyimport.SSN = tblSensitiveCustomer.SSN;

Copy and paste this into the SQL view of a new Query and see if it
doesn't get you the result you need!

You can (and should) base a Form for onscreen viewing, and/or a Report
for printing, on this query; simply open the form to run the query and
see the results in one swell foop.

John W. Vinson[MVP]
Click to expand...
 
John,

Thanks for your reply. I just found out that our company uses monarch
software and pull text files. So back to my question, how do i create a
database that will run against the monarch's text files. I have never used
or known anything about monarch, so if you can please give me as much
instructions as possible.
Click to expand...

I have never used Monarch either, so we're even.

All I can suggest is that you use either File... Get External Data...
Link to link to the text file, and base your match query on that
linked table; or File... Get External Data... Import to import it into
a local Access table.
In my tables, I want to have fields such as SS#, CustomerName, DOB. I dont
know how this is going to work. What i want is to have the text files run
against my table list and pull up the exact match records by SS# and
CustomerName. Someone told me that monarch runs the query against the
database, is it true?? If yes, how? what are the codes that I have to write?
Please help me!!!
Click to expand...

Since I do not have Monarch installed, nor have I ever used the
program, all I can suggest is that you contact the technical support
people from Monarch.

I guess I don't really understand the problem - wouldn't a SS#
uniquely identify a person? Are you concerned that you'll have to
match 444-55-6666, Jim Roberts, to 444-55-6666, Stephan Morowich, and
resolve the conflict, or what???

John W. Vinson[MVP]
 
One of the table information is base on internal information, the other one
from monarch tell us if they are making any transactions. if yes, then we
want to know,,otherwise, then no.

If anybody used monarch before, please help me.

Thanks for your help!


John said:
John,
Click to expand...
[quoted text clipped - 3 lines]
or known anything about monarch, so if you can please give me as much
instructions as possible.
Click to expand...

I have never used Monarch either, so we're even.

All I can suggest is that you use either File... Get External Data...
Link to link to the text file, and base your match query on that
linked table; or File... Get External Data... Import to import it into
a local Access table.
In my tables, I want to have fields such as SS#, CustomerName, DOB. I dont
know how this is going to work. What i want is to have the text files run
against my table list and pull up the exact match records by SS# and
CustomerName. Someone told me that monarch runs the query against the
database, is it true?? If yes, how? what are the codes that I have to write?
Please help me!!!
Click to expand...

Since I do not have Monarch installed, nor have I ever used the
program, all I can suggest is that you contact the technical support
people from Monarch.

I guess I don't really understand the problem - wouldn't a SS#
uniquely identify a person? Are you concerned that you'll have to
match 444-55-6666, Jim Roberts, to 444-55-6666, Stephan Morowich, and
resolve the conflict, or what???

John W. Vinson[MVP]
Click to expand...
 
One of the table information is base on internal information, the other one
from monarch tell us if they are making any transactions. if yes, then we
want to know,,otherwise, then no.
Click to expand...

If the Monarch file is a fixed-length or delimited Text file, you
should be able to use File... Get External Data... Import or Link to
connect to it; you should also be able to create a Query joining the
(linked or imported) file to your local table.

You might want to start a new thread mentioning Monarch in the subject
line, and/or find a support forum for Monarch (perhaps starting with
the company's website).

John W. Vinson[MVP]
 
Back
Top