Query

[Christina]

Good day
I have a query that reads from a single table with two criteria. I would
like to extract from 1 or more districts at the same time choosing one or
more programmes. If possible I would like the info on what to put in the
fields of the query on the criteria line, as I am not familiar with SQL.


Thanks

 

[John W. Vinson]

Good day
I have a query that reads from a single table with two criteria. I would
like to extract from 1 or more districts at the same time choosing one or
more programmes. If possible I would like the info on what to put in the
fields of the query on the criteria line, as I am not familiar with SQL.


Thanks

You don't give us much to go on: where in your table are the districts and/or
programmes stored? What are their datatypes? Could you post the current SQL
view of the query? The query grid is NOT the query, it's just a tool to build
SQL, and if you post the SQL we'll be able to understand it (and give you an
answer which might not require editing SQL).

AT A WILD GUESS, not knowing anything about your data, you could use criteria
such as

IN("SoHo", "Tenderloin", "Greenwich Village")

as a criterion on District, and

IN("Street cleaning", "Beautification")

on Programme.

 

[Christina]

What I gave was an example. See text of my SQL. I want to be able to
choose in any combination, one or all of month or Activity.


SELECT [VCB Payments].[Cost Centre/Item], [VCB Payments].Month, [VCB
Payments].Amount, [Vendor ID].[Vendor Name], [VCB Payments].Name, [VCB
Payments].Programme, [VCB Payments].Activity, [VCB Payments].[Smart Stream
No], [VCB Payments].[Purchase Order No], [VCB Payments].Date, [VCB
Payments].Remarks, [Enter Month] AS Expr1
FROM [Vendor ID] RIGHT JOIN [VCB Payments] ON [Vendor ID].[Vendor ID] = [VCB
Payments].Name
WHERE ((([VCB Payments].Month)=[Enter Month]) AND (([VCB
Payments].Activity)="1662")) OR ((([Enter Month]) Is Null));

Thanks

 

[John W. Vinson]

What I gave was an example. See text of my SQL. I want to be able to
choose in any combination, one or all of month or Activity.


SELECT [VCB Payments].[Cost Centre/Item], [VCB Payments].Month, [VCB
Payments].Amount, [Vendor ID].[Vendor Name], [VCB Payments].Name, [VCB
Payments].Programme, [VCB Payments].Activity, [VCB Payments].[Smart Stream
No], [VCB Payments].[Purchase Order No], [VCB Payments].Date, [VCB
Payments].Remarks, [Enter Month] AS Expr1
FROM [Vendor ID] RIGHT JOIN [VCB Payments] ON [Vendor ID].[Vendor ID] = [VCB
Payments].Name
WHERE ((([VCB Payments].Month)=[Enter Month]) AND (([VCB
Payments].Activity)="1662")) OR ((([Enter Month]) Is Null));

First off, don't use Month as a fieldname: it's a reserved word. If you must
do so, then be sure to always use [Month] in square brackets.

Secondly, if you are applying criteria to the VCB Payments table, then I don't
think the Right Join gets you anything - it will only apply to records where
there is a field value in both tables.

Finally, I think you may want to use a Form to collect the critera, rather
than prompts, and you may want to actually construct the SQL string from
scratch based on what's on the form. There's a sample database example on
Allen Browne's website
http://allenbrowne.com/ser-62.html

Your original post said that you might "one or more" of both criteria; this
makes the problem a good bit more complex. How will you be entering the
criteria? From a multiselect listbox, freehand typed values, or what?