parameter query

  • Thread starter Thread starter LP
  • Start date Start date
L

LP

hi there
i want to create a parameter query which lets the user first select what
field on the db they need to query, before entering their own parameters for
that field. can this be done in a single query? how would you go about it?
regards
LP
 
Post your table structure indicating field names and datatype.
How many fields are you want to select from? Which ones?
 
Thanks for your reply.
at the moment i just want to refer to 2 tables.
table1 has 8 fields users could query: FirstName (text), Surname (text),
Gender text), Ethnicity (text), FirstLanguage (text), DOB (date), Loop (Y/N),
DialARide (Y/N).
Table2 has 16 fields users could query: MembershipType (text), Date Joined
(date), LastUpdated (date), Executive (Y/N), MailoutFormat (text), Receive
Mailouts (Y/N), Address1 (text), Address2 (text), Address3 (text), Address4
(text), Postcode (text), Ward (text), Telephone (text), Fax (text), Email
(text), Wheelchair User (Y/N).
 
there is a 121 relationship type between table1 and table2, table 1 being the
related table. table2 has a primary key (MemID) with autonumber as Data Type.
table 1 has a primary key (MemId) with number as Data Type.
 
Apparently you did not understand the question about how the tables are
related.
What field is common to both? When you have a manufactor and product table
one-to-many you need a field in the product table that matches a field for
manufactor.
I do not see such fields in your two tables. Table 1 is people and Table 2
is memberships. What fields connects the two?
 
Create an unbound form with unbound list box and text box. Populate list
with field names.

Create query in design view by adding all fields to the grid.

Use a query like this –
SELECT [Forms]![YourForm]![ListBox] AS Expr1, LPtable1.FirstName,
[Forms]![YourForm]![ListBox] AS Expr2, LPtable1.Surname,
[Forms]![YourForm]![ListBox] AS Expr3, LPtable1.Gender,
[Forms]![YourForm]![ListBox] AS Expr4, LPtable1.DOB, LPtable1.Ethnicity,
LPtable1.FirstLanguage, LPtable1.Loop, LPtable1.DialARide,
LPtable2.MembershipType, LPtable2.[Date Joined], LPtable2.LastUpdated,
LPtable2.Ethnicity, LPtable2.Executive, LPtable2.MailoutFormat,
LPtable2.[Receive Mailouts], LPtable2.Address1, LPtable2.Loop,
LPtable2.Address2, LPtable2.Address3, LPtable2.Address4, LPtable2.Postcode,
LPtable2.Ward, LPtable2.Telephone, LPtable2.Telephone, LPtable2.Fax,
LPtable2.Email, LPtable2.[Wheelchair User], LPtable2.[Wheelchair User]
FROM LPtable1 LEFT JOIN LPtable2 ON LPtable1.MemID = LPtable2.MemID
WHERE ((([Forms]![YourForm]![ListBox])="FirstName") AND
((LPtable1.FirstName) Like [Forms]![YourForm]![ListBox] & "*")) OR
((([Forms]![YourForm]![ListBox])="Surname") AND ((LPtable1.Surname) Like
[Forms]![YourForm]![ListBox] & "*")) OR
((([Forms]![YourForm]![ListBox])="Gender") AND ((LPtable1.Gender) Like
[Forms]![YourForm]![ListBox] & "*")) OR
((([Forms]![YourForm]![ListBox])="DOB") AND
((LPtable1.DOB)=[Forms]![YourForm]![ListBox]));

Your tables should be like this –
Table Field DataType Size
Member Address1 Text 255
Member Address2 Text 255
Member Address3 Text 255
Member Address4 Text 255
Member DialARide Yes/No 1
Member DOB Date/Time 8
Member Email Text 255
Member Ethnicity Text 255
Member Fax Text 255
Member FirstLanguage Text 255
Member FirstName Text 255
Member Gender Text 255
Member MemID Text 255 Primary Key
Member Postcode Text 255
Member Surname Text 255
Member Telephone Text 255
Member Ward Text 255
Member Wheelchair User Yes/No 1
Membership Executive Yes/No 1 X
Membership LastUpdated Date/Time 8 X
Membership Date Joined Date/Time 8
Membership Loop Yes/No 1
Membership MailoutFormat Text 255
Membership MembershipType Text 255
Membership MemID Text 255 Foreign Key
Membership Receive Mailouts Yes/No 1
 
Back
Top