Report on Total Population changes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff - 23 Dec 2005 23:57 GMT

I have request to find out changes in total population. I have a database
with mutiple clients, each of these clients have an Acuity every so often
based on a time period, so the are going to multiple records for each client.
Let's say, I need to find out how many client went up or down from there
last Acuity on housing needs. Each record has an Acuity Date as a primery
field.

I think this can be done if an Iff statement, just not sure how to do it.
Can you help me?

Signature

Thanks
Jeff

Reply to this Message

Allen Browne - 24 Dec 2005 03:06 GMT

Sounds like you need to compare the one record to the value in the previous
one.

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504/en-us

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have request to find out changes in total population. I have a database
> with mutiple clients, each of these clients have an Acuity every so often
[quoted text clipped - 7 lines]
> I think this can be done if an Iff statement, just not sure how to do it.
> Can you help me?

Reply to this Message

Jeff - 24 Dec 2005 17:12 GMT

Thanks Allen, I will give this a try to see if it gives me the result that I
need.

Signature

Thanks
Jeff

> Sounds like you need to compare the one record to the value in the previous
> one.
[quoted text clipped - 15 lines]
> > I think this can be done if an Iff statement, just not sure how to do it.
> > Can you help me?

Reply to this Message

Jeff - 26 Dec 2005 22:38 GMT

Hi Allen,

I am still stuck. When I did the DLookUp, all I get are nulls. The client
ID, which is a replicated ID begins and ends with { }. Is that the cause of
the nulls? Or is housing field, which may include nulls because the case
manager did not answer that particular item? The client ID is a Primary
field so it does not have any null values.

Signature

Thanks
Jeff

> Thanks Allen, I will give this a try to see if it gives me the result that I
> need.
[quoted text clipped - 18 lines]
> > > I think this can be done if an Iff statement, just not sure how to do it.
> > > Can you help me?

Reply to this Message

Jeff - 26 Dec 2005 22:49 GMT

I forgot one other question. I have not tried the Using Code method yet but
if I did, are the KeyValue and FieldNameToGet the same? It appears that
would be the case.

Signature

Thanks
Jeff

> Hi Allen,
>
[quoted text clipped - 26 lines]
> > > > I think this can be done if an Iff statement, just not sure how to do it.
> > > > Can you help me?

Reply to this Message

Allen Browne - 27 Dec 2005 01:51 GMT

If you are using a primary key that consists of a Replication ID field, the
values will not be sequential, so you cannot use this field to determine
which is the "previous" record.

You will need another field, such as a date/time type field, to determine
when the record was added. For any record you can the retrieve the value of
the most recent record (based on the date time field) that matches the
client.

You may need to use this extended version of DLookup() to achieve that:
http://allenbrowne.com/ser-42.html

The expression to type into a fresh column in the Field row of your query
would be something like this:

PriorAcuity: ELookup("Acuity", "Table1", "([ClientID] = " & [ClientID] &
") AND ([EntryDateTime] > " & Format([EntryDateTime],
"\#mm\/dd\/yyyy hh\:nn\:ss\#") & ")", "[EntryDateTime] DESC")

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi Allen,
>
[quoted text clipped - 34 lines]
>> > > it.
>> > > Can you help me?

Reply to this Message

Jeff - 27 Dec 2005 02:58 GMT

Hi Allen,

Thanks for spending some time with me. I just want to be clear and I will
use the following to and I will use a real senerio:
Function: Find changes in total populations changes for the field Housing.
Client ID is a replicated number which is also Primary
AcDate: is Date of Acuity
Housing: is field to I want to see how many change increased and decreased
on Total client population

PriorHousing: ELookup("Housing","Acuity","([czn_fk] = " & [czn_fk] & ") AND
([Acdate] > " & Format([Acdate],"\#mm\/dd\/yyyy hh\:nn\:ss\#") &
")","[Acdate] DESC")

I get an error as undefined function when it is typed as is in a report. I
tried placing it in both the report and in the SQL statement builder. When
in the report the error is in valid control source. Is this for a form only
or do I need to import or add a library for ELookUp. It also appear to be
missing a paran, 3 opens & 4 close.

Signature

Thanks
Jeff

> If you are using a primary key that consists of a Replication ID field, the
> values will not be sequential, so you cannot use this field to determine
[quoted text clipped - 53 lines]
> >> > > it.
> >> > > Can you help me?

Reply to this Message

Allen Browne - 27 Dec 2005 03:53 GMT

To use the ELookup() function, you need to copy the code from the web page
and paste it into a standard module in your database (through the Modules
tab of the Database window.

You need a reference to the DAO Library, which is there by default in all
versions of Access except 2000 and 2002. More info on references:
http://allenbrowne.com/ser-38.html

The request for a Parameter means that Access is unable to resolve one of
the names or arguments correctly. You need a field named Housing, in a table
named Acuity. If czn_fk is a Text type field, you need exta quotes:
"([czn_fk] = """ & [czn_fk] & """) AND

I have no idea what your Function contains, what its name is, what libraries
you need for it, or whether it is defined as Public in a standard module and
uniquely named so that you can call it in a query.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi Allen,
>
[quoted text clipped - 86 lines]
>> >> > > it.
>> >> > > Can you help me?

Reply to this Message

Jeff - 27 Dec 2005 04:45 GMT

Hi,
czn_fk is the replicated ID field (Primary Key) which is a text field. Do I
need to create a seperate Number ID field? If I have to do that, how does
the program know the difference between clients? I will give your suggestions
a try tomorrow.

Have a good night.

Signature

Thanks
Jeff

> To use the ELookup() function, you need to copy the code from the web page
> and paste it into a standard module in your database (through the Modules
[quoted text clipped - 103 lines]
> >> >> > > it.
> >> >> > > Can you help me?

Reply to this Message

Jeff - 27 Dec 2005 18:45 GMT

Hi Allen,

I found the ELookup procedure, created a standard module and save it as
ELookUp. I t is visible in the Modules section of the Database Window. In
the VB Window it said "Option Compare Database" and I pasted the information
below. Should it be title something different then "Option Compare Database"?
When I open a query builder window, looked under Functions, there are 2
folders, Built In and Acuity. ELookup appears in the Acuity folder. In the
Visual Basic Window I clicked Objects Browser, ELookUp does appear in the
global list for All Libraries. There is also a reference to the DAO
libraries.

ID Field [czn_fk] is text, example {97gj856kmd}. When I add the ELookUp to
my SQL Statement Builder:
PriorHousing: ELookup("Housing","Acuity","([czn_fk] = """ & [czn_fk] & """)
AND ([AcDate] > " & Format([AcDate],"\#mm\/dd\/yyyy hh\:nn\:ss\#") &
")","[AcDate] DESC")
I get an error message when I try to run it. "Undefined function 'ELookUp'
in expression"

What am I missing?

Thanks for being patient with me.

Signature

Thanks
Jeff

> Hi,
> czn_fk is the replicated ID field (Primary Key) which is a text field. Do I
[quoted text clipped - 111 lines]
> > >> >> > > it.
> > >> >> > > Can you help me?

Reply to this Message

Allen Browne - 28 Dec 2005 02:13 GMT

The module cannot have the same name as the function.
Rename it to something else, such as Module1.

Then test it by opening the Immediate Window (Ctrl+G), and enter something
like:
? ELookup("Housing","Acuity")

If that returns something (probably the value of Housing from the first row
of the table), you can then work on building up the 3rd argument a bit at a
time until you have each step working.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi Allen,
>
[quoted text clipped - 176 lines]
>> > >> >> > > it.
>> > >> >> > > Can you help me?

Reply to this Message

Jeff - 28 Dec 2005 16:56 GMT

Renaming the module worked!!!
We use a rating scale of 0,1,3 & 5.
So to find out how many 0 changes, 1 changes,etc., do I write an Iff
statement to count them. I am going to try later this afternoon but will
probably need help. I know how to do a simple count, but that just gives me
the total changes not by category.

Thanks again for your guidence and help!!!

Signature

Thanks
Jeff

> The module cannot have the same name as the function.
> Rename it to something else, such as Module1.
[quoted text clipped - 187 lines]
> >> > >> >> > > it.
> >> > >> >> > > Can you help me?

Reply to this Message

Jeff - 30 Dec 2005 03:51 GMT

Hi Allen,

I am still stuck. I tried different versions of DCount, but that just
produces a long list of title and zeros. I have attempted a few varieties of
Iff statements, without success. All I would like for a final report is
something like the following:

0 1 3
5
Housing (count#) (count#) (count#) (count#)
then next field with total change counts.

I will continue to try different things, though tomorrow I have a full day
already.

Thanks for your help.

Signature

Thanks
Jeff

> Renaming the module worked!!!
> We use a rating scale of 0,1,3 & 5.
[quoted text clipped - 196 lines]
> > >> > >> >> > > it.
> > >> > >> >> > > Can you help me?

Reply to this Message

Allen Browne - 30 Dec 2005 04:04 GMT

Post a new thread, Jeff. This question looks like a crosstab query would be
useful.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi Allen,
>
[quoted text clipped - 13 lines]
>
> Thanks for your help.

Reply to this Message