Crystal- That won't work because he potentially has multiple rows in tblApplication for each applicant. See my earlier reply. John Viescas, author Microsoft...
99162
John Viescas
jlviescas
May 27, 2012 5:35 am
Robin- Unless the current rate is already stored in tServiceProjects, you won't get any rows. Here's how I meant for you to use DLookup: SELECT...
99163
Robin Chapple
d9790
May 27, 2012 7:58 am
Thanks again John, I apologise for my lack of ability. There is a value stored in tServiceRate. I have a problem with this new suggestion. It is so complicated...
99164
Robin Chapple
d9790
May 27, 2012 8:03 am
Thanks again John, I did not understand the use of the expression. I have used your suggestion and I have another long error which is the second image here: ...
99165
John Viescas
jlviescas
May 27, 2012 8:32 am
Robin- The two queries aren't the same. The SQL uses your old IIF, and the second ... Because you're directly comparing with what I assume is a Date/Time...
99166
Robin Chapple
d9790
May 27, 2012 11:16 am
John, I have used your new suggestion to include the rate and it worked. I then used a modified version of the AD suggestion and I have the result I need. Many...
99167
Art Lorenzini
dbalorenzini
May 27, 2012 12:31 pm
OK. I tried this: SELECT tblApplicant.ApplicantID, Avg(DateDiff("yyyy", [DOB], Now())+ Int( Â Format(now(), "mmdd") < Format( [DOB], "mmdd") )) As AvgAge ...
99168
Art
dbalorenzini
May 27, 2012 12:56 pm
I have to get a count of households that fall in the each poverty level and I am not sure of the logic: Here is the tblPovertyGuidelines: PersonInHousehold...
99169
John Viescas
jlviescas
May 27, 2012 1:45 pm
Art- Duh. Shouldn't include ApplicantID. SELECT Avg(DateDiff("yyyy", [DOB], Now())+ Int( Format(now(), "mmdd") < Format( [DOB], "mmdd") )) As AvgAge FROM...
99170
John Viescas
jlviescas
May 27, 2012 1:53 pm
Art- Are you sure that first query gets you the number of people in each household? That would seem to count the households that have all applied under the...
99171
saigonf7q5
May 27, 2012 2:45 pm
Thank you very much for your help Mr.Viescas. The `lbl50' and the `line50' won't display until I added the line "intG50 = False" after the Else statement....
99172
Art Lorenzini
dbalorenzini
May 27, 2012 2:53 pm
Yep!!! I duhhed it. THat works great.... ? What can I say, its Sunday and I am working. :) With warm regards, Arthur Lorenzini Sioux Falls, South Dakota ...
99173
candrope
May 27, 2012 3:14 pm
Hy Clive Thank you for your attention. I can't understand these conditions: "AND [nom_desc] = [tbl_med].[nom_desc] AND [nom_apres] = [tbl_med].[nom_apres] AND ...
99174
Art Lorenzini
dbalorenzini
May 27, 2012 3:17 pm
John, Those numbers are coming from my household member table. There is only one household per application. I have to include the application table so I can...
99175
John Viescas
jlviescas
May 27, 2012 3:39 pm
Phucon- It should work with the code I gave you, assuming intG50 is declared at the module level. Option Compare Database Dim intG50 As Integer Private Sub...
99176
John Viescas
jlviescas
May 27, 2012 3:46 pm
Candrope- No, they're simply a filter on the subquery. The WHERE Clause of the outer query has this subquery: WHERE tbl_med.[nom_lab] IN (SELECT [nom_lab] ...
99177
John Viescas
jlviescas
May 27, 2012 3:49 pm
Art- But is tblApplicationHousehold counting PEOPLE or Households? That's what is not clear to me. I would have thought HouseholdID is unique for a...
99178
Art Lorenzini
dbalorenzini
May 27, 2012 3:55 pm
The tblApplicationHousehold is counting individuals in the household. Â This is the structure for tblApplicationHousehold: Â HouseholdID ApplicationID ...
99179
Sigurd Andersen
sigurd1999
May 27, 2012 3:56 pm
I've just started working with a new client. They have a database created by self-taught users. It's been in use for many years. It's used by one person in the...
99180
John Viescas
jlviescas
May 27, 2012 4:47 pm
Sigurd- Replication has been "deprecated" – that means MSFT gave up supporting it. It's full of bugs, and isn't recommended at all. Which user has the...
99181
John Viescas
jlviescas
May 27, 2012 4:58 pm
Art- OK, I'm convinced. Let's start with your query to count members and add a calculation of the income amount: qryHouseCountIncome: SELECT...
99182
Art Lorenzini
dbalorenzini
May 27, 2012 5:14 pm
It's not finding Sum(tblApplicationHouseholdIncome.IncomeAmount*12). Doesn't the tblApplicationHouseholdIncome need to be joined? With warm regards, Arthur...
99183
Art Lorenzini
dbalorenzini
May 27, 2012 5:14 pm
It's not finding Sum(tblApplicationHouseholdIncome.IncomeAmount*12). Doesn't the tblApplicationHouseholdIncome need to be joined? With warm regards, Arthur...
99184
Art Lorenzini
dbalorenzini
May 27, 2012 5:19 pm
I made the join and the qryHouseholdIncome is working. I do have another question. When I run the query I see a few records that are over $100,000 and I am...
99185
John Viescas
jlviescas
May 27, 2012 5:32 pm
Art- Tough to say. You could decide on a threshhold amount, perhaps $2000, and avoid the multiply. What's the SQL of your "fixed" query? It would take an...
99186
Art Lorenzini
dbalorenzini
May 27, 2012 7:30 pm
John, this is the SQL now: Â SELECT tblApplicationHousehold.ApplicationID, Count(tblApplicationHousehold.HouseholdID) AS NoPersons,...
99187
John Viescas
jlviescas
May 27, 2012 7:57 pm
Art- It's a simple matter to insert the expression I gave you: SELECT tblApplicationHousehold.ApplicationID, Count(tblApplicationHousehold.HouseholdID) AS...
99188
Art Lorenzini
dbalorenzini
May 27, 2012 8:25 pm
John, Perfect as Always. With warm regards, Arthur Lorenzini Sioux Falls, South Dakota ________________________________ From: John Viescas <JohnV@...> To:...
99189
Sigurd Andersen
sigurd1999
May 27, 2012 9:51 pm
I've only had preliminary conversations with the new client, but my understanding is that there's a "replication master" in Canada which is used to coordinate...
99190
John Viescas
jlviescas
May 28, 2012 5:23 am
Sigurd- If the Master is not corrupted, then rebuild two copies from it and give them to the two users should be the first step. You can sort out the rest...
