Search code examples
ms-accessdcount

Use of DCount in Access to produce group sequential column

I have a calculated column "TimeAfterClass" which uses DateDiff to calculate the number of days between the start of class attendance to an audit date. This works fine. Many of the results have the same number for "TimeAfterClass", and are also within the same category (such as "math" or "English")...I've been asked to modify the TimeAfterClass results to add or subtract .01 based on whether result is in first half or second half. So for example if table data is:

Table1

I need first two to become 30.98 and 30.99, third would stay 31 and then next two would be 31.01 and 31.02. This is so I can bring the data into Excel and create a Pivot Table without doing actual math on the Score since it will be evaluated by TimeAfterClass and the entries will then be unique.

I've been trying to break this into pieces for processing...so I figured my first step would be create a column of sequences so for example new table would look like:

Table2

I tried this code:

SELECT Subject_TAC, Subject, Scores_TAC_ID, "Time_" & DCount("TimeAfterClass","Test","Subject_TAC=" & [Subject_TAC] & "And Scores_TAC_ID <=" & [Scores_TAC_ID]) AS Time_Num FROM Test;

And I get this error:

Error Message

Since this is a fluctuating set of data (gets updated monthly) I need to have this calculated dynamically.

My next step after sequencing was going to be to use the median and then subtract .01 incrementally from first half and then add .01 incrementally to second half of results.

But I cannot even get past my first step to get sequence.

I've read through a lot of posts on this site and others but I haven't been able to crack this nut.

Any help or suggestions would be greatly appreciated.

Solution

  • It appears that the variables being used for the DLookup are numbers, if that is incorrect this will require some editing...

    SELECT Test.Subject_TAC, Test.Subject, Test.Scores_TAC_ID, 'Time_' & DCount("TimeAfterClass","Test","Subject_TAC=" & [Subject_TAC] & " And Scores_TAC_ID <=" & [Scores_TAC_ID] & "") AS Time_Num
FROM Test;

    From your response that subject is text, you must add single quotes around the variable if it is text:

    SELECT Test.Subject_TAC, Test.Subject, Test.Scores_TAC_ID, 'Time_' & DCount("TimeAfterClass","Test","Subject_TAC='" & [Subject_TAC] & "' And Scores_TAC_ID <=" & [Scores_TAC_ID] & "") AS Time_Num
FROM Test;