Re-arrange data query

[smally]

Hi, I've got a table with the following fields:
EmployeeID; Date; ProvidedBy; Result; Observations

The table holds fit for work medical information for each employee.

Each record is a medical. An employee may have more than one medical.

What I'd like to do is setup a query that will show all employees in the first column, and multiple columns to the right indicating what number medical they have had

So the query would return:
EmployeeID; Medical1; Medical2; Medical3; Medical4 ...
and each record would show the date of the medical.

There is a better example in the attached spreadsheet.

 

[plog]

You will need to create a subquery to assign a medical number to each row, then you would use that in a cross-tab query to generate the resulting format you want.

To assign a medical number, you basically need to create a running total query. Search this forum for that term (running total query). You need to assign a number to each visit to determine what visit number that is. This will require a DCount. Again, search this forum for a running total query.

 

[smally]

I understand how to do the crosstab. But I can't manage to get DCount to work

If currently got Tot: DCount("EmployeeID","tblMedicals","EmployeeID=" & [EmployeeID])
But this seems to display the total, not a running total.

 

[plog]

Here 90% there. The DCount needs to take into account the order of each record (1st medical, 2nd medical). That means you need another criteria, specifically the Date (which is a poor choice for a field name because its a reserved word).

Your criteria needs to count just the ones that came before that record's date.

 

[smally]

Sorry I'm still not getting it right. I've tried switching and changing all the parameters, but nothing I'm guessing with is working.

I don't understand it. My best guess is:
Tot: DCount("[EmployeeID]","tblMedicals","[EmployeeID]=" & [EmployeeID] & "And [OnDate]<=" & [OnDate])

 

[plog]

I hate writing date criteria (which is why I didn't initially), I always forget how to escape it. Now, I took the time to get it right:

Tot: DCount("[EmployeeID]","tblMedicals","[EmployeeID]='" & [EmployeeID] & "' And [OnDate]<=#" & [OnDate] & "#")

I also added single quotes around EmployeeID because it was a text field in your sample data. If that's incorrect and its numeric, remove those.

 

[smally]

Ah. I was under the impression that access does date calculations like they are integers, similar to excel, so I didn't think # were needed for something like this.

I've attached my database that I'm using for testing as the formula isn't working properly.
Somehow the very first Running count total is always set to 0

 

[plog]

It looks good to me. When I run qryRuntot the smallest value in Tot is 1.

 

[smally]

I feel like I'm never going to run out of problems

This is what I get:

 

[plog]

I don't know what to tell you, when I run that query I get 1 for that record. Maybe instead of the OnDate field use the ID field. Based on the results you want to achieve this should work.

Tot: DCount("[EmployeeID]","tblMedicals","[EmployeeID]=" & [EmployeeID] & "And [ID]<=" & [ID])

 

[smally]

I'd prefer it to lookup dates, just incase records are inputted not in date order.

The problem seems to lie in what value is in the OnDate field. Some dates work, some don't.
I was going to say the 1st of every month doesn't work, but I now found some more

I'm in the UK therefore work on dd/mm/yyyy. Is this why our results differ?

And is there an alternative way to what I'm trying to achieve

 

[plog]

There's no reason not to use the id instead of dates. Your final output had nothing to do with an order. As long as your records are numbered sequentially and uniquely you can achieve the results you want.

 

[AlexN]

I'd prefer it to lookup dates, just incase records are inputted not in date order.

The problem seems to lie in what value is in the OnDate field. Some dates work, some don't.
I was going to say the 1st of every month doesn't work, but I now found some more

I'm in the UK therefore work on dd/mm/yyyy. Is this why our results differ?

And is there an alternative way to what I'm trying to achieve

Try something:

Change this

Tot: DCount("[EmployeeID]","tblMedicals","[EmployeeID]='" & [EmployeeID] & "' And [OnDate]<=#" & [OnDate] & "#")

to that

Tot: DCount("[EmployeeID]","tblMedicals","[EmployeeID]='" & [EmployeeID] & "' And [OnDate]<=#" & Format([OnDate];"yyyy\/mm\/dd") & "#")

 

[smally]

Thank, that works now.

Is this actually the only way to create a running count for each person?
And access seems to be poorly designed to not run with the same regional settings and the computer, I don't undertand that.

Also why does DCount return the number as text and not a numerical value?

 

[plog]

What you created was a running count for each person. Tom Hanks has 3 visits, each numbered sequentially and uniquely.

Nobody knows why DCount returns a string instead of a number. There's a number of functions that do that (DSum, DAvg) --just one of the cute things Access does that goes against expectations.

 

[CJ_London]

Is this actually the only way to create a running count for each person?

No, you can also use subqueries and in reports there is a running sum facility (from 2007)

a subquery would look like this, but note that they can't be used with crosstabs

TotSELECT Count(*) FROM tblMedicals AS T WHERE [EmployeeID]=tblMedicals.[EmployeeID] And [OnDate]<=tblMedicals.[OnDate])

 

[smally]

Thanks for everyone's help, I've certainly learned a few things.

Can anyone explain why I had to change the format of the dates for this to work? Is it a VBA problem or SQL problem

My database I'm working on is all about employee records and managing dates is a significant part of it, so I'm a bit paranoid that there may be times that I won't spot errors

 

[CJ_London]

the #'s tells sql that the text value (from your textbox control) is to be treated as a date

the standard for sql is that short dates are presented in US format of mm/dd/yyyy

As you can see from the subquery example, if referencing a date field (not control) then the #'s are not required.

date field types are actually a special form of double data type - the part to the left of the decimal point represents the date (42233 is today, 42234 is tomorrow) and the fractional part represents time expressed as number of seconds divided by the number of seconds in the day (so 42233.5 is 12 noon today, 42233.5649305556 is 13:33:30 today).

 

[smally]

All the data/information I use is stored on multiple spreadsheets using Excel. So my knowledge of how dates work comes from that.

Before AlexN provided his DCount formula I was experimenting and tried creating another field using CDbl([OnDate]), which I was initially under the assumption that Access viewed all dates as a number so thought this wasn't needed.

I've just tried a bit more experimenting, and made a basic select query with criteria Between #20/01/14# And #31/12/14# and I've now noticed that viewing the SQL code, it has converted this to mm/dd/yyyy for me.

So am I correct in saying that If I were to pass dates from VBA code to SQL, or use a VBA function for use in SQL, then Access doesn't notice this and doesn't convert it for me?

 

[CJ_London]

Access will convert those dates because there is not a month 20 or 31. But try the same thing for say 8th September or 10th January and it won't convert it