Autonumber Order Data by Client

[Davrini]

Hi Guys!

I've created a database used for Researching in a Counselling Environment. Our clients are asked to fill out forms.

My question is: Is there a way so that Access automatically knows, by date, the order of which the form the client has filled out by numbering it.

For example:

ClientID   Date          Number
CL0003     20/01/2012    1
CL0003     27/01/2012    2
CL0012     27/01/2012    1
CL0003     10/02/2012    4
CL0003     03/02/2012    3
CL0012     03/02/2012    2

As above, access would know that the 10/2/2012 is after 3/2/2012, and therefore put a 4 instead of a 3 next to it.

Anyway to achieve this?

Access 2007/2010, GMT dates.

 

[MarkK]

Data in a table doesn't really support the concept of an ordered list. Like, at the current time it might be true that if you sort this list by date...

#  Date
1  1/1/2010
2  1/1/2011
3  1/1/2012

... then the sequential numbering is correct, but look what happens if I delete 1/1/2011 ...

#  Date
1  1/1/2010
3  1/1/2012

... so if you want to maintain some kind of sequential numbering scheme in your data, you have to make sure you apply those numbering rules--and effectively renumber your whole list--every time you might have added, deleted or edited and value. That will be a headache, and there is a big efficiency hit to do this because it will always require that you sort the list by, in this case, the date field, and then check each record individually to ensure the sequential numbering scheme is valid.

What I would do first of all, if possible, is drop the requirement of sequentially numbering anything in a database. I don't believe it adds much value to a list to show a 1) in front of the first item, a 2) in front of the second, and so on. Like, who cares?

And if someone does care, calculate and apply that sequential numbering scheme only when you retrieve the data.

My 2c,
Cheers,

 

[Davrini]

Cheers for the reply, allow me to elaborate why I want them sequenced...

As a Research clinic, we need to put out reports of the data we collect - One such thing for example, is whether or not over time a person's Depression decreases with therapy. This is measured on a paper form called Beck's Depression Index - 21 questions that have a value from 0 to 3. This is just one of the forms that we have (6 in total) and not all forms are suitable to all clients, and not every week they fill out the same form.

As such, when it comes to writing the reports and Research articles for the data we gather. we need to represent that some how (At the moment by using the Graphs Access can produce is fine). But the reason we need the sequence is so that we know which was their first form, their second, their third, etc, so that the Graph can display the Form # relevant to each client (this way we can average all clients via 1st BDI, 2nd BDI, 3rd BDI, etc) and be able to track their progress this way.

The reason I'm thinking of doing it this way is that if we had dates across the X Axis, then the dates would be different for all clients and it does not make for a pretty graph. If there was a query that could automatically number the order of BDI's completed relevant to the ClientID and DateCompleted, then we could plot the graphs better.

It's purely for the Reports, but at the same time was wondering if there is a way to do this via a query (and then base the report on the query)

 

[jdraw]

If someone is going to fill out possibly 6 Forms over some time period, and you wish to know which form was completed when, then add a Date/Time Field to record when the data was collected and a FormID to identify which Form.

Once you have all the facts you can use it to produce whatever you want, AND you do not necessarily have to show the Date/Time nor the FormID if it doesn't apply.

 

[Davrini]

I've already done that, but I can't have one table with the data for 6 different forms. I have given each form it's own table - and we will only be drawing data from one table at a time.

The clinic is open on Tuesday and Wednesday. David attends Tuesdays and George attends Wednesdays. Both have 8 sessions and both fill out 8 BDIs (for argument sake). The BDIs are in paper form, and a Research inputs the data at some point down the line.

Now, I want to track David and George's scores. I then want to average their scores, but I have no common factor to compare them, because David completes his on Tuesdays and George does his on Wednesdays - The dates do not match. What I need is to order the query via dates for each client, so that is applys a number based on order of the dates. That way, David's first BDI form will be the earliest date, as will George's. Only then can I compare their scores and average it out to track the effectiveness of our service.

I won't use dates in the report, but I need to order their sessions based on the dates. Another complication is the fact that the Researcher who inputs the data only does this once a month, therefore the data that she puts in, I cannot guarentee will be in order - hense why I need something that automatically calculates the date order to give us a "This is their fourth completed BDI", eventhough it may be inputted after their sixth.

 

[jdraw]

What do your tables and relationships look like? Perhaps you can post a jpg.

Why not add an admin section to your Forms?
Form#, Candidate, DateCompletedByCandidate, DateEnteredElectronically, DataEnteredBy

Adjust your tables to accept/store these new fields and values, then you can sort etc.
As lagbolt said there is not inherent order in a relational table. If you want it ordered, then add some fields and run a query that includes an Order By.

 

[Davrini]

I feel as though I'm not explaining myself clearly enough :/

I am not bothered about how the data is stored - that I have covered and how it is saved is fine.

What I need is a query function that will automatically number table entries by ClientID depending on the order of the Date.

That's all.

So if I have:

Date         ClientID   BDINumber
12/10/2012   CL0034     1
13/10/2012   CL0034     2
14/10/2012   CL0077     1
14/10/2012   CL0034     3
15/10/2012   CL0077     2
16/10/2012   CL0077     3
16/10/2012   CL0034     4

If, for whatever reason, I deleted a data point (say the third CL0034 entry) then I want it to automatically show:

Date         ClientID   BDINumber
12/10/2012   CL0034     1
13/10/2012   CL0034     2
14/10/2012   CL0077     1
15/10/2012   CL0077     2
16/10/2012   CL0077     3
16/10/2012   CL0034     3

Notice how the 16/10/2012 for CL0034 changes to 3 (because it then becomes the third set of data for this client). But if I added an entry for CL0077 for the 12/10/2012, then it would show:

Date         ClientID   BDINumber
12/10/2012   CL0034     1
13/10/2012   CL0034     2
14/10/2012   CL0077     2
14/10/2012   CL0034     3
15/10/2012   CL0077     3
16/10/2012   CL0077     4
16/10/2012   CL0034     4
12/10/2012   CL0077     1

And automatically bump all the others in the list up one that belong to that client because they are AFTER the new entry date, irrespective of where they are in the main table.

This is what I am after. I'm terrified of human error, and the reason I want to avoid entering this stuff manually is data inconsistency in published reports. Not good for funding, etc...

 

[jdraw]

Yes it seems to be communication ( a lot of that going around).

I think what you're telling us is that a Client may fill out the same form up to 6 times, but that's just my guess.

Just an aside, just because there a fields on a form that a client fills in, you often need additional data (Admin is typically the name) to identify things like who ran the test, who entered the data , when was the data entered...

You're concerned with human error, but how would this

If, for whatever reason, I deleted a data point (say the third CL0034 entry)

materialize or be prevented?

Many databases do not have physical deletes (actual removal of a record). They have an additional field eg DeletedFlag Boolean that is set to True to indicate a LOGICAL delete. Such records are excluded from queries/reports.

I'm not trying to be difficult. You say you're happy with the way things are stored, yet you can't manipulate what you have to do what you want. I think you need some additional fields to support the processing you want to do.

Good luck with your project.