Query with multiple fields

[Guest]

I have a table (a) which has 50 fields, all contain assets numbers, it
basically builds up a list of asset numbers used in each piece of work
(record on that table).

I am trying to build a query which has all the fields in it from table (a)
but i want to be able to search each field for a specific number, so I have a
combo box which list the asset number ona report form, and i want the query
to tell me every piece of work that has this asset in it.

My query lists all the fields, i have put in criteria on the first asset
field which looks at the asset number selected in the combo box, but then how
can I get the query to look up this in the second and subsequent fields even
if its not found in the first field, need something like an 'or' function,
is there such a thing in a query

hope this makes sense any ideas guys

 

[Guest]

Hi Matt,

First solution
That's a bit late probably for you to change your table structure, but the
one you have is a typically non-normalised and non-optimal one. In your
table, you should have, for one piece of work, as many records as there are
assets in that piece of work, with only one asset in each record. I.e. your
table should only have 2 fields: ID of piece of work, and asset number. Then
your life would be a LOT easier for any processing to be done...

2nd solution
Short of changing the structure, I *think* one can concatenate fields (at
least in certain DBs, don't know if Access suppports that) in a query, like
this:
.... where (field1 & field2 & field3 & field4...) LIKE '*<searched asset
number>*'
Just be aware, if that works, that this won't tell you which of the fields
was containing the searched asset number.

3rd solution (this is just a sketch of the solution)
- process each row in a loop
- Read the asset numbers of one row in an array
- loop on each index of the array and look for your asset number
- if found set a flag and exit loop
- if flag set, add the row to a temporary table
- report on your temporary table

4th solution
Edit directly the SQL of your query to say (aNo being the asset number
searched):
.... WHERE (field1 = aNo or field2 = aNo or field3 = aNo or ...)
Will be a long SQL statement, but that's the problem with denormalised DBs...

Regards
Balex

 

[John Vinson]

I have a table (a) which has 50 fields, all contain assets numbers,

Then you have a misdesigned table.

it basically builds up a list of asset numbers used in each piece of work
(record on that table).

If you have a many (piece of work) to many (assets) relationship, you
should use THREE tables: Work, Assets, AssetsUsed:

Work <your current table>
WorkID
<information about the piece of work itself>

Assets
AssetNumber
<description of the asset>

AssetsUsed
WorkID <link to your current table.
AssetNumber <the ID of an asset used in this piece of work>

"Fields are expensive, records are cheap". You are currently using
spreadsheet logic which does NOT work well with a relational databse.

I am trying to build a query which has all the fields in it from table (a)
but i want to be able to search each field for a specific number, so I have a
combo box which list the asset number ona report form, and i want the query
to tell me every piece of work that has this asset in it.

Then restructure your table - and query the ONE AssetNumber field in
AssetsUsed, and you'll get exactly that.

My query lists all the fields, i have put in criteria on the first asset
field which looks at the asset number selected in the combo box, but then how
can I get the query to look up this in the second and subsequent fields even
if its not found in the first field, need something like an 'or' function,
is there such a thing in a query

hope this makes sense any ideas guys

Hope the above helps.

John W. Vinson[MVP]