counting the records

[icemonster]

hello.

jsut want to know the best approach or sample if possible, cause i have a report that contains schedules for patients, what i want to do is, on a unbound form, calculate the 13th and 19th visit of that particular record, anyway to do this? or anyone care to teach me how? thanks

 

[Beetle]

anyway to do this?

Probably, but we'll need a bit more info. Do you store both the Date and Time of each visit? Do you have some other way of uniquely identify each visit and their order?

calculate the 13th and 19th visit of that particular record

What do you mean by "particular record"? A particular record of a customer?

What do you mean by "13th and 19th visit"? The 13th and 19th visit based on their entire history? In a calendar month? Something else?

 

[icemonster]

i store the date, basically, each record also is tied to a certain, certification period, so each time they print this report out, it's only for that certification period.

so what i want to do is, on the report for the certification period, i want to count the 13th visit and 19 visit for that record. hope you understand me.

 

[The_Doc_Man]

From your description, it is not clear whether your layout is properly normalized. (Not saying it isn't, but we can't tell from what you've posted.) Normalization would help organize your data.

Be warned that to do what you want isn't possible unless your design takes into account that you need to count visits in a certain way. This is because in the raw table, there IS no 13th or 19th visit unless you have a compound primary key based on patient ID and date. In the absence of proper ordering information, tables have no order.

Therefore, your design must allow for this by including a way to order the tables and then assign an ordinal position to the records. Once you do that, you can determine the 13th and 19th records. You need dates to provide order and patient IDs to segregate the records into patient-specific subsets.

If you want to know which record is the 13th, you need to do something like this in more or less this order...

1. Include a yes/no field in the detail records.
2. Before you start, set all such fields to NO (an UPDATE query can do this pretty fast)
3. Use an update query to set the field to YES for each case where the count of records with earlier dates = 12.
4. Now select all marked records.
5. When done, repeat the process for count of earlier dates = 18.

Poke around in the forum search to see about assigning ordinal numbers to records. I've seen that topic a few times.

 

[icemonster]

sorry if i didn't explain it to well,

all i really want to do is count the 13th record for the report, and just show that record in an unbound text box.

so i don't really need to specify or assign a count on each record, just count the records as is.

e.g

i use count() to count all of the records, i just need something to say =13threcord.

 

[Beetle]

i use count() to count all of the records, i just need something to say =13threcord.

But the point is what constitutes the "13th" record. Are we going strictly by the date in ascending order? Descending order? What if there is more than one visit on the same date? Or is that not possible?

Once you determine the exact method, and fields, that will be used to determine the "order", then you can create a query that includes a calculated field that numbers the records in that "order". You can then retrieve the "13th" or "19th" or whatever record from that query based on this field. Right now, we don't know enough about your data base structure to tell you specifically how to create the query you need but you might find the following link helpful.

http://allenbrowne.com/ranking.html

 

[icemonster]

well, ascending and it is not possible to have more than 1 record for that date. what method (sample if possible) can i use to count the records? and how do i retrieve it?

 

[icemonster]

didnt see the allenbrowne there. my bad. but any direct tips though as to how to add a count and retrieve it?

 

[Beetle]

For the purposes of this example, let's assume you have the following;

A Customers table with fields like;

CustomerID
CustomerName

A Visits table with fields like;

VisitID
CustomerID
VisitDate

You could then create a query like the following;

Select tblCustomers.CustomerName, tblVisits.VisitDate, DCount("*","tblVisits","CustomerID=" & [tblVisits].[CustomerID] & " And VisitDate<#" & [tblVisits].[Visitdate] & "#")+1 As VisitNumber
From tblCustomers Inner Join tblVisits On tblCustomers.CustomerID = tblVisits.CustomerID;

Which would return results like the following;

CustomerName----VisitDate----VisitNumber
John--------------1/1/2011--------1
John--------------2/1/2011--------2
John--------------3/1/2011--------3
(and so on for John)
Fred--------------1/2/2011--------1
Fred--------------2/2/2011--------2
Fred--------------3/2/2011--------3
(and so on for Fred)

You could then create another query to extract data from this query based on the VisitNumber;

Select * From TheRankingQuery Where VisitNumber = 13 Or VisitNumber = 19;

 

[icemonster]

yeah it works, but (this is really stupid) what should i use for a number? cause am ranking them through the primary key, not the date so the # on that thing is not valid, cause am getting an error. thanks alot for the help btw.

 

[Beetle]

If your are using a numeric, instead of a Date/Time field, then you need to remove the date delimiters (#). It might look like;

Select tblCustomers.CustomerName, tblVisits.VisitDate, DCount("*","tblVisits","CustomerID=" & [tblVisits].[CustomerID] & " And VisitID<" & [tblVisits].[VisitID])+1 As VisitNumber
From tblCustomers Inner Join tblVisits On tblCustomers.CustomerID = tblVisits.CustomerID;

Keep in mind this will only work as long as the VisitID (or whatever the field is called) is always sequentially increasing, which is not guaranteed if you're using Autonumber. Autonumbers are only guaranteed to be unique, not sequential or even positive.