Select query

G

gmenon100

Dear friends,

I have a tblContact with 60 colums. I need to extract say column 3 based on
value(202) in column 4, and 7 based on value (202) in colum 8 and column 11
based on value (202) in colum 12 -- into a new table.

Is this possible and if so could I acheive this.

My table is as under

field1 field2 field3 field4 field5 field6 field7 field8
field9 field10 field11 field12
John 8888 4500 202 Keith Tom Dental 503 NY
queens 6589 202
Patty 9287 7666 449 Jerry Kay heart 202 NJ
Jer Cit 8733 543
Gale 8376 7555 202 Kelly Nath Ear 202 NJ
Bronx 3300 122


Since the value to be extracted is (202)

the data should be :

field1 field2 field3 field4 field5 field6 field9 field10
John 8888 4500 202 Keith Tom NY queens
John 8888 6589 202 Keith Tom NY queens
Patty 9287 heart 202 Jerrt Kay NJ Jer Cit
Gale 8376 7555 202 Kelly Nath NJ Bronx
Gale 8376 Ear 202 Kelly Nath NJ Bronx


Can anyone please help.

Thank you in advance.

Gimen
 
J

Jeff Boyce

A table with "60 columns" is probably ... a spreadsheet!

Access is a relational database ... you won't get easy use of Access'
features/functions if you feed it 'sheet data.

Based on your description, your current tblContact!Field3 can contain
numbers (4500, 6589, 7555) or text (heart, ear).

This is not a well-normalized design.

Since it all starts with the data, you may need to clean up your data before
you can (easily) use Access' query features.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
G

gmenon100 via AccessMonster.com

Hi Jeff,

Thanks for your reply. We have a billing software which can export only in
text format and the numbers below are infact text. What I am trying to do is
create a new table extracting the information from this text imorted into
access. There after this 60 column table will be eliminated.

Thks,
Gimen

Jeff said:
A table with "60 columns" is probably ... a spreadsheet!

Access is a relational database ... you won't get easy use of Access'
features/functions if you feed it 'sheet data.

Based on your description, your current tblContact!Field3 can contain
numbers (4500, 6589, 7555) or text (heart, ear).

This is not a well-normalized design.

Since it all starts with the data, you may need to clean up your data before
you can (easily) use Access' query features.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Dear friends,
Click to expand...
[quoted text clipped - 33 lines]
Gimen
Click to expand...
Click to expand...
 
J

John W. Vinson

Dear friends,

I have a tblContact with 60 colums. I need to extract say column 3 based on
value(202) in column 4, and 7 based on value (202) in colum 8 and column 11
based on value (202) in colum 12 -- into a new table.

Is this possible and if so could I acheive this.

My table is as under

field1 field2 field3 field4 field5 field6 field7 field8
field9 field10 field11 field12
John 8888 4500 202 Keith Tom Dental 503 NY
queens 6589 202
Patty 9287 7666 449 Jerry Kay heart 202 NJ
Jer Cit 8733 543
Gale 8376 7555 202 Kelly Nath Ear 202 NJ
Bronx 3300 122


Since the value to be extracted is (202)

the data should be :

field1 field2 field3 field4 field5 field6 field9 field10
John 8888 4500 202 Keith Tom NY queens
John 8888 6589 202 Keith Tom NY queens
Patty 9287 heart 202 Jerrt Kay NJ Jer Cit
Gale 8376 7555 202 Kelly Nath NJ Bronx
Gale 8376 Ear 202 Kelly Nath NJ Bronx
Click to expand...

I really don't understand the logic of your tblContact. I gather that you're
trying to normalize it, but I don't understand how the 202 in Field4 and the
202 in ... what, Field12? word wrap obscured it... relate to the rest of the
data. Is there internal structure within this 60 field record?
 
G

gmenon100 via AccessMonster.com

Thanks for your reply. Actually. It is a Salesmans report and every 4th
column has a text(Unique) which is based on the following column.

I was trying to bring the every 4th column to a new table based on condition
Dear friends,
Click to expand...
[quoted text clipped - 25 lines]
Gale 8376 7555 202 Kelly Nath NJ Bronx
Gale 8376 Ear 202 Kelly Nath NJ Bronx
Click to expand...

I really don't understand the logic of your tblContact. I gather that you're
trying to normalize it, but I don't understand how the 202 in Field4 and the
202 in ... what, Field12? word wrap obscured it... relate to the rest of the
data. Is there internal structure within this 60 field record?
Click to expand...
 
Top