472,801 Members | 1,152 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,801 software developers and data experts.

Search multiple fields (80!) in a query

(Access 2000)

Two issues:
Within a query, I need to return a field name as data (see eg. below).

I need to search within 80 fields (same criteria) - is there a way to
avoid 80 separate expressions (the 80 field names are stored in a
table - can I get the query to look each of these up?)?

Here's an example:

CURRENT DATA SAMPLE:
(Field names:) Field 1 Field 2 Field 3
(Data:) 14
15
16a

RESULTS DESIRED:
(Field names:) Field Name Data
(Data:) Field 1 14
Field 2 15
Field 3 16a

Note that 'Field 1', formerly the name of the field, is now desired as
tabular data. This seems, from my very basic research, to be the
reverse of a crosstab query.

Any ideas are greatly appreciated - thanks!!!

Martin Lacoste
Nov 13 '05 #1
4 5974
Martin, your "results desired" is exactly what the table needs to look like.
It should have two fields:
WhatType containing your current field name
WhatData containing your current data
and possibly another field as primary key.

1. Create that table.

2. Make a query into your "current data sample".

3. Drag Field1 into the grid, and in the Critiera row under it, enter:
Not Null

4. Change the query to an Append query (Append on Query menu).

5. Run the query.

6. Change "Field1" to "Field2", and run it again. Repeat for your other
fields.

You now have a normalized table that can be queried easily.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Martin Lacoste" <ma*******@rogers.com> wrote in message
news:d4**************************@posting.google.c om...
(Access 2000)

Two issues:
Within a query, I need to return a field name as data (see eg. below).

I need to search within 80 fields (same criteria) - is there a way to
avoid 80 separate expressions (the 80 field names are stored in a
table - can I get the query to look each of these up?)?

Here's an example:

CURRENT DATA SAMPLE:
(Field names:) Field 1 Field 2 Field 3
(Data:) 14
15
16a

RESULTS DESIRED:
(Field names:) Field Name Data
(Data:) Field 1 14
Field 2 15
Field 3 16a

Note that 'Field 1', formerly the name of the field, is now desired as
tabular data. This seems, from my very basic research, to be the
reverse of a crosstab query.

Any ideas are greatly appreciated - thanks!!!

Martin Lacoste

Nov 13 '05 #2
Hi Martin,
From your examples, I can't think of a way to do this in a query.

I think this may be possible using 2 tables, 2 recordsets.
Read the data in from recordset1 which is based on your existing table, then
write it back out using recordset2 to append it to the new table.
However ... at 80 fields per record x how many records are in your table ...
you're going to end up with a whole crapload of records in your new table!
I can only wonder why you want to do this, but hey ... it your database!
:-)

Here is my code, (tested and working) but you may have to inspect the
numeric value of your "tblOldTable's" AutoNumber field's .Attribute.
************************************************** ***
Private Sub Command1_Click()

Dim MyDB As DAO.Database
Set MyDB = CurrentDb

Dim rst1 As DAO.Recordset
Set rst1 = MyDB.OpenRecordset("tblOldTable", dbOpenTable)

Dim rst2 As DAO.Recordset
Set rst2 = MyDB.OpenRecordset("tblNewTable", dbOpenDynaset)

Dim fld As Field
Dim strFld As String
Dim varData
Dim MyID As Long

With rst1
.MoveLast
.MoveFirst
Do Until .EOF
For Each fld In .Fields
strFld = fld.Name
varData = .Fields(strFld)
'Debug.Print strFld
'Debug.Print fld.Attributes
If fld.Attributes = 49 Then 'This is the Attribute of my
AutoNumber Field
MyID = varData
GoTo NextField
End If

With rst2
.AddNew
!RecordID = MyID
!FieldName = strFld
!FieldData = varData
.Update
End With
NextField:
Next fld

If Not .EOF Then
.MoveNext
End If
Loop
End With

Set rst2 = Nothing
Set rst1 = Nothing
Set MyDB = Nothing

End Sub

--
HTH,
Don
=============================
E-Mail (if you must)
My*****@Telus.net

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================


"Martin Lacoste" <ma*******@rogers.com> wrote in message
news:d4**************************@posting.google.c om...
(Access 2000)

Two issues:
Within a query, I need to return a field name as data (see eg. below).

I need to search within 80 fields (same criteria) - is there a way to
avoid 80 separate expressions (the 80 field names are stored in a
table - can I get the query to look each of these up?)?

Here's an example:

CURRENT DATA SAMPLE:
(Field names:) Field 1 Field 2 Field 3
(Data:) 14
15
16a

RESULTS DESIRED:
(Field names:) Field Name Data
(Data:) Field 1 14
Field 2 15
Field 3 16a

Note that 'Field 1', formerly the name of the field, is now desired as
tabular data. This seems, from my very basic research, to be the
reverse of a crosstab query.

Any ideas are greatly appreciated - thanks!!!

Martin Lacoste

Nov 13 '05 #3
Hi again,

Ooops, sorry.
I meant to add a check to skip Null fields. Revise the code that I posted
earlier with:
******************************
....
If Not IsNull(varData) Then
With rst2
.AddNew
!RecordID = MyID
!FieldName = strFld
!FieldData = varData
.Update
End With
End If
NextField:

....
******************************
Don
Nov 13 '05 #4
Hi Don!
Holy moly! I so appreciate you taking the time to do this. I've
pasted this and will work with it to see if it does what I think I
want it to do. Many thanks for your help!!

Martin Lacoste

"Don Leverton" <le****************@telusplanet.net> wrote in message news:<7ApFc.41500$l6.39941@clgrps12>...
Hi again,

Ooops, sorry.
I meant to add a check to skip Null fields. Revise the code that I posted
earlier with:
******************************
...
If Not IsNull(varData) Then
With rst2
.AddNew
!RecordID = MyID
!FieldName = strFld
!FieldData = varData
.Update
End With
End If
NextField:

...
******************************
Don

Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Rizyak | last post by:
******************** alt.php.sql,comp databases.ms-sqlserver microsoft.public.sqlserver.programming *********************************** Why doesn't this work: SELECT * FROM 'Events'
5
by: JP SIngh | last post by:
Hi All This is a complicated one, not for the faint hearted :) :) :) Please help if you can how to achieve this search. We have a freetext search entry box to allow users to search the...
3
by: mkjets | last post by:
I have worked for hours on trying to find a solution and have not figured it out. I am working in Access 2003. I need to create a query that takes values from 1 table and displays them in...
3
by: IP This | last post by:
Good Day Good People... I have a table with various fields, a number of fields relate to the same data type, i.e. Language1, Language2, Language3, Language4, - I want to be able to search all of...
0
by: Alex Pavluck | last post by:
Hello. I need to search 6 different fields for a key word. The way I normally query data is in a single field with the critera: Like '*' & & '*'. Is there a way to enter only 1 key word but...
10
by: Eugenio | last post by:
Hi there, I'm returning to this forum for the second time and I would like to say thanks for the great help provided. I've encountered a new problem now and hope that you will be able to help me...
482
by: bonneylake | last post by:
Hey Everyone, Well i am not sure if this is more of a coldfusion problem or a javscript problem. So if i asked my question in the wrong section let me know an all move it to the correct place. ...
1
by: Lyn DeMaio | last post by:
I've been tasked with taking two unrealted tables (Providers and Patients, no common field) and allow users, on one form (or form/subform) to search multiple fields in each table, select a record...
6
by: Steve Zalinski | last post by:
This returns only txt1. Any thoughts? Columns are (varchar (250),Null). select distinct employee_name, tran_date, billed_hrs, (txt1 + txt2 + txt3 + txt4 +txt5 + txt6) as Narrative
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
How does React native implement an English player?
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.