Querying off multiple columns

  • Thread starter Thread starter Fatz
  • Start date Start date
F

Fatz

I have a form that provides 10 entries for fund numbers. These
entries are stored in 10 different columns in a table. Some entries
into the form may have only one fund #...and some may have up to 10
fund #'s. So let's say record number one has a listing for fund
#12345 entered in column #1 of the table. And let's say record #5 has
a listing for fund #12345 but it was entered in the second column. If
I want a query that lists all records that have a listing for fund
#12345, how do I create that given that the fund # could be listing in
any of the ten columns??

I need the query to pull the whole record associated with the fund #
entered into a text box by a user.

THANKS!

Chris
 
You did not follow standard design principles. You should have a separate
table with two fields. One would be the key (client number?) and the other
would be the fund number. You would then add multiple records to the table
for each client. A client with three funding numbers would have three
entries. A client with 5 funding numbers would have fivve entries, etc.

You should fix your basic design and then your task will become much easier.

That being said, to work with what you have you would need to build a query
with all ten columns. In the criteria enter "12345" for column one. One
the next row, enter the same criteria in column two, etc. Entries on
different rows in the query builder are treated as "or" statements.

If you want the user to specify the number being located, enter something
like...
[EnterDesiredFundingNumber]
in place of the "12345" mentioned above.


Rick B


I have a form that provides 10 entries for fund numbers. These
entries are stored in 10 different columns in a table. Some entries
into the form may have only one fund #...and some may have up to 10
fund #'s. So let's say record number one has a listing for fund
#12345 entered in column #1 of the table. And let's say record #5 has
a listing for fund #12345 but it was entered in the second column. If
I want a query that lists all records that have a listing for fund
#12345, how do I create that given that the fund # could be listing in
any of the ten columns??

I need the query to pull the whole record associated with the fund #
entered into a text box by a user.

THANKS!

Chris
 
Rick-

Thanks for the help. I understand your point about the seperate table
but this is a strange database. It logs entries by CUSIP number and
there is no primary key. Trust me...I know this is the wrong way to
go about this but this is what they want! The main query will show
all entries with the same CUSIP number and the person will go off the
date entered and the specific broker tied to the trade. It's really a
bad setup all the way around to be honest.

Thanks Again,
Chris
 
Chris:

Did you get what you needed out of my answer? Did it work for you?

Rick


Rick-

Thanks for the help. I understand your point about the seperate table
but this is a strange database. It logs entries by CUSIP number and
there is no primary key. Trust me...I know this is the wrong way to
go about this but this is what they want! The main query will show
all entries with the same CUSIP number and the person will go off the
date entered and the specific broker tied to the trade. It's really a
bad setup all the way around to be honest.

Thanks Again,
Chris
 
You are in the position of the mechanic in the local garage, with a customer
saying: "just fix my car so it has 5 wheels and uses an aircraft jet engine
for power".

Good luck.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
Fatz said:
Rick-

Thanks for the help. I understand your point about the seperate table
but this is a strange database. It logs entries by CUSIP number and
there is no primary key. Trust me...I know this is the wrong way to
go about this but this is what they want! The main query will show
all entries with the same CUSIP number and the person will go off the
date entered and the specific broker tied to the trade. It's really a
bad setup all the way around to be honest.

Thanks Again,
Chris


"Rick B" <[email protected]> wrote in message
You did not follow standard design principles. You should have a separate
table with two fields. One would be the key (client number?) and the other
would be the fund number. You would then add multiple records to the table
for each client. A client with three funding numbers would have three
entries. A client with 5 funding numbers would have fivve entries, etc.

You should fix your basic design and then your task will become much easier.

That being said, to work with what you have you would need to build a query
with all ten columns. In the criteria enter "12345" for column one. One
the next row, enter the same criteria in column two, etc. Entries on
different rows in the query builder are treated as "or" statements.

If you want the user to specify the number being located, enter something
like...
[EnterDesiredFundingNumber]
in place of the "12345" mentioned above.


Rick B


I have a form that provides 10 entries for fund numbers. These
entries are stored in 10 different columns in a table. Some entries
into the form may have only one fund #...and some may have up to 10
fund #'s. So let's say record number one has a listing for fund
#12345 entered in column #1 of the table. And let's say record #5 has
a listing for fund #12345 but it was entered in the second column. If
I want a query that lists all records that have a listing for fund
#12345, how do I create that given that the fund # could be listing in
any of the ten columns??

I need the query to pull the whole record associated with the fund #
entered into a text box by a user.

THANKS!

Chris
Click to expand...
Click to expand...
 
Back
Top