Selecting columns from a query by using a form

Hi Daryl,

This is how I finally got the whole thing wokring. The Dcount function does
not permit DISTINCT and I really need that since cclients can be visited more
than ones. Making two query where one selects all clients based on criteria
and the next seleccts those clients, GROUPED BY works around this.

In the first query I have all the columns I want to count. With your help I
was able to compile an Iif statement in the criteria for each column, using
OR-OR-OR,
The Iif checks a combobox for being appropiate to the specific column,
returning the value in the fieldds of the columns as criterium as a TRUE
sstatement and "*" as a false statement. This way the combobox with seleted
issuees in a way switches the criteria on and off.

ie: for columns coughing (YES/NO) the statement in the criterium field would
be:
LIKE IIF ("["&Combo13&"]".column(3)="Coughing"; YES; "*") and for Shingles:
LIKE IIF ("["&combo13&"]".column(3)="Shingles";YES;"*") etc etc

So whatever you choose, it only applies to one columns and the rest are
FALSE, giving a wildcard in the criterium. Then SELECT DISTINCT IdClient from
this query. COUNT in the last query.

Thanks a lot for the help on all the syntaxis and the impossibilities you
pointed out. I know its not elegant but it works.

"Daryl S" wrote:

Mafukufuku -

When you get a handle on things, and are ready for more help, open a new post.

As for VBA, SQL, and Query Design...

SQL is a language for talking to relational databases. For the most part,
we think of it as a tool for getting data into and out of a database, mostly
reporting on data in a database. Query Design is a great tool for making
many of the queries simple to produce. You can create the queries in Query
Design and then switch to SQL View to see the SQL text. Now there are some
types of queries that can only be done in SQL View, such as UNION queries and
Pass-thru queries, but most of the basic types of queries can be done in
Query Design, so use it as a leaning tool.

VBA on the other hand is an application development language. It cannot
talk to relational databases itself, but it can pass SQL statement to a
database and receive the results back. VBA also manages the user interface
(e.g. forms), and can perform calculations and make decisions based on
information provided by the end user or other events. It can be used to open
up some other applications (e.g. Excel or Word) and to 'talk' to the
operating system.

While VBA itself does not manipulate relational databases, it has the
capability to send SQL statement and to receive back results. The SQL
statements it sends can be from queries we have already saved in Access, or
we can build the SQL text within the code, as VBA allows us to manipulate
string variables. Of course we (the developers) have to know how to build
these statements as we have to use the VBA language to assemble the parts.
We can use VBA to pull information off of our open forms or from other
sources and then put them into a SQL statement. It's pretty awesome, really.

Anyway, good luck on your project...
--
Daryl S


"Mafukufuku" wrote:

Thank you it does,

to a certain extent.

I have two accept 2 things:

1: that have not yet mastered access
2: that it seems that the interchangeability between SQL, Designmode and VBA
seems limited, whereas I thought Designmode was the poor mans SQL, which was
the poor mans VBA.

Give me some time to sort your last tip out, if "Computer says:"No!"" I will
get back to you. Thanks again.

"Daryl S" wrote:

Mafukufuku -

I think I may have mis-stated a piece earlier. You can build this query
using the information from the combobox on the forms, but I think you want to
create the SQL in the form. You can't evaulate an expression within query
builder to come up with the table or field names. You can build the SQL
statement in code on a button on the form (which I think is where we
started). You can also save this SQL as a query that you can look at in
query design mode, but anything in query design mode will have to have the
table and fields defined.

Does that make sense?

--
Daryl S


"Mafukufuku" wrote:

SELECT Forms![frm1inserttable]![Combo29].Column(2) AS Expr1,
Forms![frm1inserttable]![Combo29].Column(1) AS Expr2;


Do I select the tables involved (via de combobox) from the field list?

I get a Undefined function error for Forms![frm1inserttable]![combo29].column.

So he doesnt list the (2) from behind Column in this error?

What I would want is to select fieldnames into this query that are
references to a column in another query.

Query 1 selects [IdClient] of all patients visited in a certain period in
the Database from Dtblclients
Query 2 selects [Idclient] of only OVC's (Orphans and Vulnerable Children,
clients who meet specific criteria) visited in a certain period from
DtblClient

Query 3 finds for either group the actions and issues. Whether this is
group 1 or group2 is to be decided by the user. So a listbox/combobox to
select query1 or query2 to be part of query 3.

I gather if I get this to work with a table, as in the above trial
(Dtblissuehealth) I can get it to work work with querys as well.

Thanks