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 |
#1
|
|||
|
|||
Count Records in multiple tables
I am trying to get a count of Appointments from multiple tables using this
statement - when I try to run it I get the unspecified expression error. Appointments: DCount("*",[dbo_REFERRAL_ORDER.APPT_DT] is not Null Or [dbo_ORDER_LOOKUP3.APPT_DT]is not null) The APPT_DT field in each table is a date field. The tables are joined by Orner Number, but the appointment date will populate in the different tables based on the data entry source. However, there could be data for the same order in both tables, so I cannot count each one indepentently & sum the result. |
#2
|
|||
|
|||
Count Records in multiple tables
On Mon, 15 Mar 2010 12:13:10 -0700, Jeff
wrote: I am trying to get a count of Appointments from multiple tables using this statement - when I try to run it I get the unspecified expression error. Appointments: DCount("*",[dbo_REFERRAL_ORDER.APPT_DT] is not Null Or [dbo_ORDER_LOOKUP3.APPT_DT]is not null) The APPT_DT field in each table is a date field. The tables are joined by Orner Number, but the appointment date will populate in the different tables based on the data entry source. However, there could be data for the same order in both tables, so I cannot count each one indepentently & sum the result. As Bob says, you're misinterpreting how domain functions work. The first argument is what you want to count (or sum, or look up, or whatever); the second is a text string specifying the name of the table or query; the third is another text string with a valid SQL WHERE clause (without the word WHERE) providing the criteria. Try Appointments: DCount("*","[dbo_REFERRAL_ORDER]", "[APPT_DT] is not Null") Unless you create a Query appropriatly joining or unioning the two tables dbo_REFERRAL_ORDER and dbo_ORDER_LOOKUP3 you will not be able to reference both tables in the same lookup. My guess is that a UNION query would be in order, but I don't know anything about the structures or relationships. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Count Records in multiple tables
I was indeed misunderstanding what I needed to use DCOUNT.
I was able to solve this using a sub-query to identify Non-Null Appointment Dates in each table and add the number of appointment dates for each order. Then, in the main query, I summed the number of non-zero results for each person's orders to get their actual appointment count. "John W. Vinson" wrote: On Mon, 15 Mar 2010 12:13:10 -0700, Jeff wrote: I am trying to get a count of Appointments from multiple tables using this statement - when I try to run it I get the unspecified expression error. Appointments: DCount("*",[dbo_REFERRAL_ORDER.APPT_DT] is not Null Or [dbo_ORDER_LOOKUP3.APPT_DT]is not null) The APPT_DT field in each table is a date field. The tables are joined by Orner Number, but the appointment date will populate in the different tables based on the data entry source. However, there could be data for the same order in both tables, so I cannot count each one indepentently & sum the result. As Bob says, you're misinterpreting how domain functions work. The first argument is what you want to count (or sum, or look up, or whatever); the second is a text string specifying the name of the table or query; the third is another text string with a valid SQL WHERE clause (without the word WHERE) providing the criteria. Try Appointments: DCount("*","[dbo_REFERRAL_ORDER]", "[APPT_DT] is not Null") Unless you create a Query appropriatly joining or unioning the two tables dbo_REFERRAL_ORDER and dbo_ORDER_LOOKUP3 you will not be able to reference both tables in the same lookup. My guess is that a UNION query would be in order, but I don't know anything about the structures or relationships. -- John W. Vinson [MVP] . |
Thread Tools | |
Display Modes | |
|
|