Please Help with database upate. New to database

[OdAwG]

Just some questions regarding tables. I am new Access Database and need a
little help. I have the following data listed below
01. I have a table called tbl_Customer with the following information
listed below
02. In that table I have three columns that have numeric data in it (City,
State, Marital_Status)
03. I need to convert the numeric data in table tbl_customer (City, State,
Marital_Status) to alpha numeric data
04. using the following tables to help convert the numeric data to alpha
numeric
05. tbl_City, tbl_State, and tbl_Marital_Sta tus has the following data
listed below.

Example:

Tbl_Customer
Lname Fname DOB Address City State
Marital_Status
Doe John 01/01/1942 123 Olson Dr 036 042 04
Tbl_City
City_CD City_Name
036 Santa Monica
037 Dallas
038 New York
Tbl_State
State_CD State
042 Texas
051 Kansas

Tbl_marital_sta tus
Marital_CD marital_status
01 Married
02 Single
03 Widowed
04 Divorced
Someone had suggested to me that I could possibley use an update query to
perform this task. can anyone provide an example on how to do this. Any
and all help in regards to this is greatly appreciated.

Thanks,

Argus

[pietlinden]

Personally, I think you're going normalization crazy. Been there, done
that. =)
Use a query. Just join Customer to City table, State table, and
Marital status table and then show the fields you want. And you're
done.

[OdAwG]

Thanks for the advice, it worked very well.I do have another question to ask
if you don't mind,

I have a table called tbl_memberSSN. In this table it only has a list of
member SSN. How can I search the master query table to see if those member
SSN are in the master query table and do the following:

1. If it finds the member SSN then create a new table called tbl_Match with
all the memberSSN that it found with all of the data in the master query.

2. If it doesn't find the memberSSN in it, then create a new table called
tbl_NoMatch with only the memberSSN that it did not find in the master
query table.

Any and all help is greatly appreciated.

Argus
<pi********@hot mail.comwrote in message
news:11******** **************@ m79g2000cwm.goo glegroups.com.. .

Personally, I think you're going normalization crazy. Been there, done
that. =)
Use a query. Just join Customer to City table, State table, and
Marital status table and then show the fields you want. And you're
done.

[pietlinden]

I have a table called tbl_memberSSN. In this table it only has a list of

member SSN. How can I search the master query table to see if those member
SSN are in the master query table and do the following:

1. If it finds the member SSN then create a new table called tbl_Match with
all the memberSSN that it found with all of the data in the master query.

2. If it doesn't find the memberSSN in it, then create a new table called
tbl_NoMatch with only the memberSSN that it did not find in the master
query table.

Any and all help is greatly appreciated.

Argus

I'm puzzled. Why do you need a distinct table of SSNs? To implement
data security? Then force the users to access your tables through
views. I mean, I could answer your question, but I get the feeling
that it may not be helping.

Why are you creating lots of tables? Will queries not suffice for what
you need to do? (What are you trying to do, in business terms?)

you can do #1 with an inner join. and #2 with an outer join or the
"Find Unmatched" query wizard.

But again, what are you trying to achieve? I suspect you're making
things much harder than they need to be.

[OdAwG]

I didn't do a good job of explaining on what it is I am trying to do. I am
new to databases and doing alot of reading and OJT.

This is what I am trying to do: I need to search a table or query for
specific information. I am given a list to use as my search criteria to
search that master query. The list is given to me in a text file. I import
that text file into my database as tbl_UserSearch and I want to do the
following:

001. I want to use tbl_UserSearch to search the Master_query
002. Find all matches and create a new table called tbl_MatchesFoun d.
003. tbl_MatchesFoun d is created and used to export the information to
Excel to be used by others.
004. The other items not found is ported to a new table called
tbl_NoMatches
005. tbl_NoMatches is used to inform the requestors that those items not
found need to be worked.

tbl_MatchesFoun d and tbl_NoMatches do not have to be tables, I guess they
could be queries as long as I can export that data into excel.

again, all help is greatly appreciated.

Argus
<pi********@hot mail.comwrote in message
news:11******** **************@ h48g2000cwc.goo glegroups.com.. .

I have a table called tbl_memberSSN. In this table it only has a list

of

member SSN. How can I search the master query table to see if those

member

SSN are in the master query table and do the following:

1. If it finds the member SSN then create a new table called tbl_Match

with

all the memberSSN that it found with all of the data in the master

query.

2. If it doesn't find the memberSSN in it, then create a new table

called

tbl_NoMatch with only the memberSSN that it did not find in the master
query table.

Any and all help is greatly appreciated.

Argus

I'm puzzled. Why do you need a distinct table of SSNs? To implement
data security? Then force the users to access your tables through
views. I mean, I could answer your question, but I get the feeling
that it may not be helping.

Why are you creating lots of tables? Will queries not suffice for what
you need to do? (What are you trying to do, in business terms?)

you can do #1 with an inner join. and #2 with an outer join or the
"Find Unmatched" query wizard.

But again, what are you trying to achieve? I suspect you're making
things much harder than they need to be.

[pietlinden]

OdAwG wrote:

I didn't do a good job of explaining on what it is I am trying to do. I am
new to databases and doing alot of reading and OJT.

This is what I am trying to do: I need to search a table or query for
specific information. I am given a list to use as my search criteria to
search that master query. The list is given to me in a text file. I import
that text file into my database as tbl_UserSearch and I want to do the
following:

001. I want to use tbl_UserSearch to search the Master_query
002. Find all matches and create a new table called tbl_MatchesFoun d.
003. tbl_MatchesFoun d is created and used to export the information to
Excel to be used by others.
004. The other items not found is ported to a new table called
tbl_NoMatches
005. tbl_NoMatches is used to inform the requestors that those items not
found need to be worked.

tbl_MatchesFoun d and tbl_NoMatches do not have to be tables, I guess they
could be queries as long as I can export that data into excel.

again, all help is greatly appreciated.

Argus

Oh, I get it now. So you essentially have a "temporary" dataset (your
newly imported data) and you have to see what it matches/does not
match? If that's the case, you can create queries to do all this for
you. If the records exist in two tables, you can just have a join
between them. Given tables A and B, in Oracle-speak, it would look
like this:

SELECT tblA.Field1, tblB.Field2 <whatever fields you want to see>
FROM tblA, tblB
WHERE tblA.Field1 = tblB.Field2; <---- these are your inner joins

If you want to find the records that are in one table but not in
another, just use an outer join (that's what the find unmatched query
wizard does).

It's something like:

SELECT tblA.Field1, ...
FROM tblA LEFT JOIN tblB ON tblA.Field1=tbl B.Field2
WHERE tblB.SomeField IS NULL;

The data that's not in table B will be Null, so you can find on that if
there are no matches.

So there's no need for any other table than your real data tables (the
"temp" one and your "existing records" one). You can export the
queries wherever you want. Just treat them like tables. I think the
only difference is that you specify the type as table instead of query.
Other than that, everything works the same.

[OdAwG]

Works like a charm, thanks for the expert advice.

I'm so sorry to ask you another question, you've been such a great help,
however, is there a way to automatically import text/excel files into Access
tables or query. what I would like to do is when I launch my database, it
would automatcially import the text files or excel files.

Argus

<pi********@hot mail.comwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.com.. .

>
OdAwG wrote:

I didn't do a good job of explaining on what it is I am trying to do. I

am

new to databases and doing alot of reading and OJT.

This is what I am trying to do: I need to search a table or query for
specific information. I am given a list to use as my search criteria to
search that master query. The list is given to me in a text file. I

import

that text file into my database as tbl_UserSearch and I want to do the
following:

001. I want to use tbl_UserSearch to search the Master_query
002. Find all matches and create a new table called tbl_MatchesFoun d.
003. tbl_MatchesFoun d is created and used to export the information to
Excel to be used by others.
004. The other items not found is ported to a new table called
tbl_NoMatches
005. tbl_NoMatches is used to inform the requestors that those items

not

found need to be worked.

tbl_MatchesFoun d and tbl_NoMatches do not have to be tables, I guess

they

could be queries as long as I can export that data into excel.

again, all help is greatly appreciated.

Argus

Oh, I get it now. So you essentially have a "temporary" dataset (your
newly imported data) and you have to see what it matches/does not
match? If that's the case, you can create queries to do all this for
you. If the records exist in two tables, you can just have a join
between them. Given tables A and B, in Oracle-speak, it would look
like this:

SELECT tblA.Field1, tblB.Field2 <whatever fields you want to see>
FROM tblA, tblB
WHERE tblA.Field1 = tblB.Field2; <---- these are your inner joins

If you want to find the records that are in one table but not in
another, just use an outer join (that's what the find unmatched query
wizard does).

It's something like:

SELECT tblA.Field1, ...
FROM tblA LEFT JOIN tblB ON tblA.Field1=tbl B.Field2
WHERE tblB.SomeField IS NULL;

The data that's not in table B will be Null, so you can find on that if
there are no matches.

So there's no need for any other table than your real data tables (the
"temp" one and your "existing records" one). You can export the
queries wherever you want. Just treat them like tables. I think the
only difference is that you specify the type as table instead of query.
Other than that, everything works the same.

[pietlinden]

OdAwG wrote:

Works like a charm, thanks for the expert advice.

I'm so sorry to ask you another question, you've been such a great help,
however, is there a way to automatically import text/excel files into Access
tables or query. what I would like to do is when I launch my database, it
would automatcially import the text files or excel files.

Argus

the way I would handle it is something like this:
1. create import specifications for your text files.
2. Always put the files to be imported into the same folder.
'---code these---
3. loop through the contents of the folder (from step 2), and import
the files.
4. any files successfully imported should be moved to a different
folder. (use Rename) The failed ones should go in another folder.

Sorry, getting brain damage... this is most of it... I've commented out
the parts that aren't working yet. Oh, the BrowseFolder API... that's
here:

http://www.mvps.org/access/api/api0002.htm

Here's my code so far...

Public Sub ImportFilesInDi rectory(ByVal strExtension As String)
Dim strOriginalPath As String
Dim strFinalPath As String

Dim strFinalFile As String
Dim myFile As String

strOriginalPath = BrowseFolder("S elect a folder to process")
strFinalPath = BrowseFolder("S elect a folder to move processed
files to:")

myFile = Dir(strOriginal Path & "\*." & strExtension)
strFinalPath = strFinalPath & "\" & myFile

'---Loop through the files in the folder (strPath)
Do While myFile <""
'---handle Text and Excel files differently...
If strExtension = "txt" Then
DoCmd.TransferT ext acImportDelim, "MySpecificatio nName",
"DestinationTab le", myFile, False
'Name "c:\origfile.tx t" As "c:\newfile.txt " <---NEEDS
REPAIR
ElseIf strExtension = "xls" Then
DoCmd.TransferS preadsheet acImport,
acSpreadsheetTy peExcel9, "DestinationTab le", myFile, True
End If

'---run the "move file" code here...

myFile = Dir
Loop
End Sub

[OdAwG]

Hey,

is there a way when you do a macro to import a text file, that you can
create the field name. Example:
Inside the mem.txt file

There is no header

12345
12346
32165
32154
98756
98567

when i create the macro, I perform the following:
001. Click on MACROS
002. Clicked on New
003. Under ACTION select TransferText
004. Under Action Arguments selected the following:
01. Transfer Type --Importe Delimited
02. Specification Name --<blank>
03. Table Name --tbl_SearchMemNu m
04. File Name --c:\dir\folder\M emNum.txt
05. Has Field Names --NO
06. HTML Table Name --<blank>
07. Code Page --<blank>
005. Save the macro as M_Import_MemNum

When importing is done and I looked inside the table, it has as the field
name as F1 with all the numbers under it
When I change Line 004 step 05 to Yes, it puts the first member number in
the file as the Field Name.

How can I set the field name to what I need it to be during import. I will
have the same problem with the other text files that I will also be
importing.


"OdAwG" <Od***@goneloos e.comwrote in message
news:9p******** *****@tornado.t exas.rr.com...

Just some questions regarding tables. I am new Access Database and need

a

little help. I have the following data listed below
01. I have a table called tbl_Customer with the following information
listed below
02. In that table I have three columns that have numeric data in it

(City,

State, Marital_Status)
03. I need to convert the numeric data in table tbl_customer (City,

State,

Marital_Status) to alpha numeric data
04. using the following tables to help convert the numeric data to alpha
numeric
05. tbl_City, tbl_State, and tbl_Marital_Sta tus has the following data
listed below.

Example:

Tbl_Customer
Lname Fname DOB Address City State
Marital_Status
Doe John 01/01/1942 123 Olson Dr 036 042 04
Tbl_City
City_CD City_Name
036 Santa Monica
037 Dallas
038 New York
Tbl_State
State_CD State
042 Texas
051 Kansas

Tbl_marital_sta tus
Marital_CD marital_status
01 Married
02 Single
03 Widowed
04 Divorced
Someone had suggested to me that I could possibley use an update query to
perform this task. can anyone provide an example on how to do this. Any
and all help in regards to this is greatly appreciated.

Thanks,

Argus

[pietlinden]

Unless your first row contains field names, you can't. well, not with
a macro. If you wanted to rename the fields, you could do it in
ADO/DAO and probably regular old ALTER TABLE commands with SQL.

Are you importing files with an unkown number of fields or something?
Kinda weird, because databases depend on standardized structures...