If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
A workaround:
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 |
Thread Tools | |
Display Modes | |
|
|