By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,347 Members | 2,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,347 IT Pros & Developers. It's quick & easy.

Please Help with database upate. New to database

P: n/a
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_Status 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_status
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
Jul 18 '06 #1
Share this Question
Share on Google+
17 Replies


P: n/a
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.

Jul 19 '06 #2

P: n/a
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********@hotmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.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.

Jul 19 '06 #3

P: n/a
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.

Jul 19 '06 #4

P: n/a
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_MatchesFound.
003. tbl_MatchesFound 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_MatchesFound 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********@hotmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.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.

Jul 20 '06 #5

P: n/a

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_MatchesFound.
003. tbl_MatchesFound 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_MatchesFound 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=tblB.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.

Jul 20 '06 #6

P: n/a
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********@hotmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.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_MatchesFound.
003. tbl_MatchesFound 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_MatchesFound 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=tblB.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.

Jul 21 '06 #7

P: n/a

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 ImportFilesInDirectory(ByVal strExtension As String)
Dim strOriginalPath As String
Dim strFinalPath As String

Dim strFinalFile As String
Dim myFile As String

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

myFile = Dir(strOriginalPath & "\*." & 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.TransferText acImportDelim, "MySpecificationName",
"DestinationTable", myFile, False
'Name "c:\origfile.txt" As "c:\newfile.txt" <---NEEDS
REPAIR
ElseIf strExtension = "xls" Then
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel9, "DestinationTable", myFile, True
End If

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

myFile = Dir
Loop
End Sub

Jul 21 '06 #8

P: n/a
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_SearchMemNum
04. File Name --c:\dir\folder\MemNum.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***@goneloose.comwrote in message
news:9p*************@tornado.texas.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_Status 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_status
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


Jul 21 '06 #9

P: n/a
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...

Jul 22 '06 #10

P: n/a
When I do the following, it keeps giving me an error

ALTER TABLE tbl_MemNumSearch
ALTER COLUMN F1 to Mem_Num

I keep getting the following error message
"Syntax error in ALTER TABLE statement"

Then I tried the following with the same error message"

ALTER TABLE tbl_MemNumSearch
RENAME COLUMN F1 to Mem_Num

When I hit the help button on the error msg box, it givees me"
Syntax error in ALTER TABLE statement. (Error 3293)
You entered an SQL statement that includes an invalid ALTER TABLE statement.
Possible causes:
a.. A reserved word or argument name is misspelled or missing.
b.. Punctuation is incorrect.
What does that mean

<pi********@hotmail.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
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...

Jul 22 '06 #11

P: n/a

OdAwG wrote:
When I do the following, it keeps giving me an error

ALTER TABLE tbl_MemNumSearch
ALTER COLUMN F1 to Mem_Num
Why are you altering the structure of your tables on the fly? This is
usually not a good idea. that's why I said to create an empty table
with your fields defined and an import specification. If you don't
know yet what your fields are going to be, it might be time to get out
that pencil and paper and figure out your structure before going too
much further. A little pain now is a lot better than a lot of pain
later.

Jul 22 '06 #12

P: n/a
build your tables _first_, and then import. altering table structures
on the fly is really dodgy.

Jul 22 '06 #13

P: n/a
okay, I have a blank table called tbl_test_MemNum with only one field
called MemNum.
When i manually go through the process of importing the text file into my
database it works fine. The following steps were used:

001. Click on FILE
002. Click on GET EXTERNAL DATA
003. Click on IMPORT
004. Select your file
005. IMPORT TEXT WIZARD is launched
006. You know the rest

Now when I try to do the same thing via a macro, I can't do the same thing.
So how can I import the text file into my existing database automatically?
How can I do this programmatically (SQL) or using a macro. Understand, I am
a newbie at this and reading alot of books, internet, and your responses is
greatly appreciated and very helpful.

<pi********@hotmail.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...
>
OdAwG wrote:
When I do the following, it keeps giving me an error

ALTER TABLE tbl_MemNumSearch
ALTER COLUMN F1 to Mem_Num

Why are you altering the structure of your tables on the fly? This is
usually not a good idea. that's why I said to create an empty table
with your fields defined and an import specification. If you don't
know yet what your fields are going to be, it might be time to get out
that pencil and paper and figure out your structure before going too
much further. A little pain now is a lot better than a lot of pain
later.

Jul 22 '06 #14

P: n/a
With table name tbl_testMemNum and only one field, MemNum, how would I do
this programmatically via SQL.
<pi********@hotmail.comwrote in message
news:11*********************@p79g2000cwp.googlegro ups.com...
build your tables _first_, and then import. altering table structures
on the fly is really dodgy.

Jul 22 '06 #15

P: n/a

OdAwG wrote:
With table name tbl_testMemNum and only one field, MemNum, how would I do
this programmatically via SQL.
<pi********@hotmail.comwrote in message
news:11*********************@p79g2000cwp.googlegro ups.com...
build your tables _first_, and then import. altering table structures
on the fly is really dodgy.
do _what_?

Jul 22 '06 #16

P: n/a
how do i do the import automatically

<pi********@hotmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
>
OdAwG wrote:
With table name tbl_testMemNum and only one field, MemNum, how would I
do
this programmatically via SQL.
<pi********@hotmail.comwrote in message
news:11*********************@p79g2000cwp.googlegro ups.com...
build your tables _first_, and then import. altering table structures
on the fly is really dodgy.
>
do _what_?

Jul 22 '06 #17

P: n/a

OdAwG wrote:
how do i do the import automatically
Create an unbound form, set the TimerInterval property to the number of
milliseconds you want to wait before checking for new files.

In the On Timer event, call the code to check the folder for files, and
if they exist, then import them, then move the text files to another
folder, so you don't import the same one again.

Have a dig through the NG. There are zillions of examples of how to do
all this. Otherwise, check the shrinking helpfile for:

OnTimer event of a form.
Dir
transfertext, transferspreadsheet, import specification

that should give you what you need.

Jul 23 '06 #18

This discussion thread is closed

Replies have been disabled for this discussion.