Mass DCount on single form

[craigprice]

Hi,

I am producing a form for a rota system, within the rota I want it to look at staff on the day/night shift and check their skills and count them, the counts then populate a footer item with numbers so we can work out where and what cover is needed, this is probably me being a novice and I assume it is because of the vast amount of actions I'm asking it to perform but it is extremely slow.

Here is an example of the code;

windowscountday1 = DCount("[ID]", "qrySHIFTAJAN", "[01day] = 'D' AND windows = 1") + DCount("[ID]", "qrySHIFTAJAN", "[01day] = 'D*' AND windows = 1")

This is repeated for windowscountday1-31 and windowscountnight1-31 along with about 5/6 other skills e.g skill2countday1 skill3countday1 etc etc.

This means there are about 310 calculations, these calculate and then populate the relevant text box (e.g windowscountday1)

I am sure there will be a quicker and simpler way to do this, I've spent a good few hours just naming boxes to end up with this result...

I'm not sure if this is something I could potentially do with a query? Just not sure how I'd go about writing it and can I have the query run on each of these days/nights/skills?

Thanks in advance.

 

[Ranman256]

DCount IS very slow.
Use sum queries if you want to count multiple things.
Dcount is for 1 single calc.

 

[craigprice]

Ok so how would I go about doing that, do I create a second query to count the entries in each row?

 

[Ranman256]

If you are using a form ,put a textbox in the footer, with Dcount.
If you are using queries, set summation on.

 

[plog]

Your code set off my Improperly-Structured-Database alarms.

1. qrySHIFTAJAN - Does 'SHIFTA' represent the shift of the query and 'JAN' represent the month? Meaning you have SHIFTX* 12 queries for this data? If so that's not right.

2. [01day] - Does that mean you have 31 of those fields--one for each day? Again, not right.

The final solution for this probably is a DCount, but just a single DCount run once. I think this issue is a symptom of your larger problem--improperly sturctured database tables.

could you post the underlying structure of your tables? Perhaps a screenshot of the relationship tool?

 

[craigprice]

That's good to hear! I really want to learn but feel I keep falling down.

qrySHIFTAJAN was the old query, I saw the error of my ways and have changed this to simply look for the shift referenced in a textbox on the Rota form now, same for month. Basically under rota in the query I used [Forms].[frmFORMNAME].[ShiftToView] and [Forms].[frmFORMNAME].[MonthToView]

Regarding the 01day thing, yeah.... I have 31 of these and another 31 for nights...

Relationships are something I struggled to get my head around.

Basically I want the skills to pull from a users table but as I can't figure this out I'm having the skills duplicated for each relevant users line.

The tables basically consist of tblROTA

Colleague Name, Colleague ID, Shift, Month, Skill 1, Skill 2, Skill 3, Day01, Night01, Day02, Night02 etc etc etc.

tblUsers
Colleague Name, Colleague ID, Shift, Skill 1, Skill 2 (Planned to have these copy into the tblRota and if their skills are updated then do an update query to copy the new values over the old in tblRota.

A complete bodge I think....

 

[plog]

Yeah, its not the right structure (actually you do need a Colleagues table, but not like you proposed). Read up on normalization (https://en.wikipedia.org/wiki/Database_normalization), work through a few tutorials and then if you have questions post back here and we can answer them.

The biggie I see is numerated field names. When you start suffixing numbers to field names, its time for a new table. Let's use Skills as an example. I'm going to assume [Skill 1] is a skill of the colleague and not related to [Day01] or [Night01] despite sharing the same suffixed number. Also, I am assuming the 1 on [Skill 1] means nothing. It's just a way to differentiate the field from the other skills--[Skill 1] doesn't mean that skill is better/more important than any other Skill of that colleague. This is how you would handle skills:

You would get rid of all those [Skill N] fields in tblRota and create a Skills table structured like so:

Skills
skill_ID, autonumber, unique id of table
ID_Colleague, number, foreign key to Colleague table to identify what colleague has this skill
skill_Type, text, this would be the same value you are storing in all those number suffixed fields

That's it, that table with just 3 fields will now hold all the skills of everyone. You need to have a table like that for every set of fields you have numerated.

Read up on normalization, redo your structure, complete the Relationship tool in Access and then post a screenshot of it back here. You don't have to move your data to that structure, yet. Just set up the tables and let's make sure its right before you proceed.

 

[craigprice]

Hi, thanks for your response, still having trouble getting my head around relationships.

The skills can have multiple colleagues with each skill, do I just do this as a multi value lookup?

 

[plog]

To define a many to many relationship (1 skill can go to many colleagues and 1 collegue can have many skills) in a database you use Junction table (https://en.wikipedia.org/wiki/Associative_entity). It acts as a traffic cop directing which skill goes where.

Let's assume you have a Colleagues table where everyone in it has a unique ColleagueID and a Skills table where every skill has a unique SkillID. To create the relationship you would use a junction table:

ColleagueSkills
ColleagueID, SkillID
1, 14
1, 12
1, 31
2, 14
2, 15
3, 14
3, 15

So, every collegaue above has skill 14 (whatever that is in the SKills table). Colleague=2 and Colleague=3 both have skill 15. That's how you would establish a many-many relationship between skills and colleagues

 

[craigprice]

Now having another issue with this. While trying to build the rota I want to lookup a table to see if the colleague has booked off holiday, I came up with this.

 Nz(DLookUp("[Type]","tblAmendments","[Colleague_ID] =" & [ColleagueID]),Nz(DLookUp("[01day]","qryrotadefaults","[Shift] =" & [Shift]))) AS day1

I want to be able to look if they've booked for the DAY and MONTH the lookup is for to make sure I am not getting any old entry, I have a feeling I'm probably going about this the wrong way too!

Also just tried and failed with this;

TEST: Nz(DLookUp("[Type]","tblAmendments","[amddate] = #01/01/2016#" & "'" & "And & [Colleague_ID]=" & [ColleagueID]))

 

[plog]

There's no reason for a Dlookup in a query. You join the table appropriately and obtain the data that way.

I'm actually lost on what structure we are dealing with. So, if you could upload your database with sample data in it and also perhaps a spreadsheet of what your expected results are, then I could help more.

 

[craigprice]

This is my basic idea;

Rota system – picks up “default” shift pattern for each shift (1,2,3,4) this then looks at which shift the colleague is part of and then uses the specified defaults to list their days/nights to work, the exception being if they have time booked off in tblAmendments, I want this to also contain overtime so if they have an entry for overtime they would appear on the rota for the shift they are covering on, for example Colleague 1 works for Shift 1 but is doing overtime on Shift 2 – I don’t want it to show Shift 2’s rota for this person but to include the days overtime.

Holiday booking – Automatically calculate the holiday a colleague has remaining to take in the year by deducting the amount take (as H in tblAmendments) from their overall total.

Skills – Track which skills a colleague has – they’re able to go in and update their skill level based on a 0-3 answer to a question, the average is then calculated to give their “Skill Score” which is then used within the rota to make sure adequate cover is available

 

[plog]

What are we working on? I see your database (see some issues with the tables) but I don't know what you are trying to achieve.

Can you demonstrate with data what you hope to achieve?

 

[craigprice]

This is an example from excel, if you check tblrota form that shows the idea

My aim is to have a rota view of;

Shifts and Month

You can flick through shifts to look at all the shifts and the months for their shift etc etc.

I want it to tell me how many staff are on days or nights on that shift and also show if someone has holiday (in amendments). I also want to use the colleagues data to show what skills are covered for the day from the staff that are in (not out in amendments). If that makes sense?


TBLCOLLEAGUES
ID (PK)
StaffID
ColleagueFirstName
ColleagueSurname
Shift (SELECT [tblSHIFTS].[ID], [tblSHIFTS].[Shift] FROM tblSHIFTS ORDER BY [Shift]; as row source)
Grade (SELECT [tblGRADES].[ID], [tblGRADES].[Grade] FROM tblGRADES ORDER BY [Grade]; as row source)

These are the main parts needed for rota


TBL GRADES
ID (PK)
Grade


TBLSKILLS
ID (PK)
SkillName
Platform


TBLSHIFTS
ID (PK)
Shift
ShiftManager (qrySHIFTMANAGERS as rows - queries tblColleagues to find shift managers)
DeputyManager (qrySHIFTMANAGERS as rows - queries tblColleagues to find shift managers)
UWITL (qryUWITL as rows - queries tblColleagues to find UWI Team Leaders)
BMTL (qryBMTL as rows - queries tblColleagues to find BM Team Leaders)


TBLSKILLSCOLLEAGUES
ID (PK)
Skill (SELECT [tblSKILLS].[ID], [tblSKILLS].[SkillName] FROM tblSKILLS ORDER BY [SkillName]; as Row Source)
Colleague (qryCOLLEAGUESALL as row source)

TBLAMENDMENTS
ID (PK)
Colleague (qryCOLLEAGUESALL as Rows)
AmendedDate
HoursPerDay
Type

 

[plog]

Honestly, if that's the format you want, it might be best to use Excel to achieve it. The main issue I see is that you want to use dates as column headings--that generally takes a cross-tab query, which don't easily lend themselves to Access Reports.

If you want to give it a go, it would technically take 2 reports (1 main and 1 sub). The top portion (Colleague area) would be the main report and the bottom portion (Skill area) would be the sub-report.