Database for departmental rota (1 Viewer)

lbs

Registered User.
Local time
Today, 07:39
Joined
Dec 22, 2019
Messages
109
Hi, I was asked to take over departmental rota in a hospital. My predecessor did it in Excel, I want to do in in access. I posted a question about this project in another thread and received suggestions that prompted me that my approach to the database itself may be wrong. Our rota is done for 6 weeks. It covers consultants activities and trainees only. There are mainly 4 types of activities including MDT meetings, absences, supervision of trainees, specialised work allocation, other activities. I have to keep these activities in separate tables for registration because they would have different fields and different number of fields.
My question is: Do i need to start with the the first and main parent table Date register where I will define all the dates first? Or may be another first parent table for Rotas id and then second parent for dates?
Any suggestions please? Has anyone did a similar job?
 

vba_php

Forum Troll
Local time
Today, 02:39
Joined
Oct 6, 2019
Messages
2,884
My question is: Do i need to start with the the first and main parent table Date register where I will define all the dates first? Or may be another first parent table for Rotas id and then second parent for dates?
Any suggestions please? Has anyone did a similar job?
lbs,

you could have continued asking this stuff in your other thread:

https://www.access-programmers.co.uk/forums/showthread.php?t=308562

but to address what you're now asking...you can set it up in more ways than one. I gave you a suggestion in your last thread and so did other people. your top level parent *could* be DATES, and it also could be ROTAS. If you want my opinion, it really doesn't matter. dbGuy told you in the other thread that duplicating records is not a good idea. That is true. In a situation like yours, it is very easy to set up a structure that is comprised of nothing more than one-to-many relationships. that's what you should end up with when you're done with the setup.
 

plog

Banishment Pending
Local time
Today, 02:39
Joined
May 11, 2011
Messages
11,613
First, learn and keep using his spreadsheet. Presumably, it works and encapsulates the whole process.

Next, use his spreadsheet to model your database tables. make sure every piece of data in the spreadsheet is captured in a field in your tables. Set up the relationships in your database and make sure everything looks kosher. Then, move some sample data into your database, ideally you would double enter real data--one into the spreadsheet and one into the database. Then in your database build the reports and queries you will need to output data. Make sure your database output matches the excel output. Once it does you can work on forms.

When those are done, I still suggest double running data until you are comfortable that the database system duplicates the Excel system. Once comfortable you can discontinue the spreadsheet.
 

lbs

Registered User.
Local time
Today, 07:39
Joined
Dec 22, 2019
Messages
109
Make sure your database output matches the excel output. Once it does you can work on forms.
That will be the most difficult task as I will have to incorporate at least three separate reports into one report. That will be a real challenge for me.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:39
Joined
Oct 29, 2018
Messages
21,358
My predecessor did it in Excel, I want to do in in access.
First thing to realize here is Excel and Access are used for different purposes. Where you have a working system with only one spreadsheet is involved, it could mean one or two or more tables in Access. You need to familiarize yourself with the principles of Normalization.


My question is: Do i need to start with the the first and main parent table Date register where I will define all the dates first? Or may be another first parent table for Rotas id and then second parent for dates?
Any suggestions please? Has anyone did a similar job?
Can you post a sample copy of the Excel spreadsheet, so we can give you some guidance on what tables you might be looking at creating? Here's one example of a scheduling data model, just in case it helps visualize what you might need to create.
 

lbs

Registered User.
Local time
Today, 07:39
Joined
Dec 22, 2019
Messages
109
I keep truing to built my dream departmental rota database. I have achieved something. No I need to do a trick.
I have a query with the name q_r_11_cons_abs It shows consultants absent for each day:
Date abs Cons_abs_gull_am_pm
10/01/2020 NC
10/01/2020 CK
10/01/2020 SM
11/01/2020 CK am
11/01/2020 LS
11/01/2020 AW
11/01/2020 NC pm

I need to have this:
Date abs Cons_abs_gull_am_pm
10/01/2020 NC, CK, SM
10/01/2020 CK am, LS, AW, NC pm


I am sorry I could not achieve the right design to show you what I want. I mean separate filed for Date_abs and separate field Cons_abs_gull_am_pm

Does anyone know how to do that? I searched the web and found a ConcatRelated () function. I tried it but I can't figure out how to have the resulting String to show what is exactly for particular date. When I remove the third argument from the function it shows all for al dates, but when I try to fiddle with the argument, it shows either nothing or error.
 
Last edited:

lbs

Registered User.
Local time
Today, 07:39
Joined
Dec 22, 2019
Messages
109
The second record should be 11/01/2020 ?

Use Allen Browne's Concatenate function to do what you want.

HTH

Yes it was my mistake it should be 11/01\20 of course

And yes I am trying to use that function but I can't get what I want. I can't understand how to set the 3rd argument in the function to define filter. If I dont set this argument then it returns all entries of consultants for all dates.
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:39
Joined
Sep 21, 2011
Messages
14,050
Firstly try as the the parameter
Code:
"Date_Abs = #" & [Date_Abs] & "#"

if that does not work then try
Code:
"Date_Abs = #" & Format([Date_Abs],"mm/dd/yyyy") & "#"

Here is one I use for a report control

Code:
=ConcatRelated("CrewName","QryCrewStudy","QryCrewStudy.Rank = """ & [Rank] & """ AND QryCrewStudy.On_Date = #" & Format([Dated],"mm/dd/yyyy") & "#","CrewNameRev")
HTH
 

lbs

Registered User.
Local time
Today, 07:39
Joined
Dec 22, 2019
Messages
109
Firstly try as the the parameter


if that does not work then try
HTH

The first parameter worked for some dates but left blank for other dates. The second parameter worked perfectly, exactly what I wanted. Many thanks Gasman!
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:39
Joined
Sep 21, 2011
Messages
14,050
The first parameter worked for some dates but left blank for other dates. The second parameter worked perfectly, exactly what I wanted. Many thanks Gasman!

My typing is getting atrocious these days. :mad:
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:39
Joined
Oct 29, 2018
Messages
21,358
Thanks

This is probably a good function, but how can I use it not in SQL but as a function in expression builder?
Hi. If you add the SimpleCSV() function into your project, it should become available as an option item from the Expression Builder. You just have to select the name of your project instead of "Built-In Functions."
 

lbs

Registered User.
Local time
Today, 07:39
Joined
Dec 22, 2019
Messages
109
If you add the SimpleCSV() function into your project

It works as well

So I solved this problem.

Now I am facing another more difficult problem. I need to stitch several reports into one. How do I do that? Is it possible at all. I need that all reports were stitched nicely and the row for each date should go in continuity from one report to another so that people could see all activities and absentees and meetings and everything for one day in one row. I am attaching two sample reports which I wanted to try to stitch by using one as subreport. Total fiasco. I am very disappointed and even don't believe that I will manage this.
 

Attachments

  • r_01_act.pdf
    36.3 KB · Views: 154
  • r_02_pm.pdf
    48.6 KB · Views: 174

Cronk

Registered User.
Local time
Today, 18:39
Joined
Jul 4, 2013
Messages
2,770
For a combined report showing non contiguous data, I make each report a sub report of a single parent report. Alternatively, where output is to be formatted and set out in a particular row, I will look to outputting data using automation to an Excel spreadsheet.
 

lbs

Registered User.
Local time
Today, 07:39
Joined
Dec 22, 2019
Messages
109
For a combined report showing non contiguous data, I make each report a sub report of a single parent report. Alternatively, where output is to be formatted and set out in a particular row, I will look to outputting data using automation to an Excel spreadsheet.

So, I need to create an empty report and put all my reports into that container as supports? Where should I put them, into the details section?

And your suggestion of outputting data to excel by automation looks an idea for me. I have never done it as I though it's too complex for my brain. But I will try it.
 

Cronk

Registered User.
Local time
Today, 18:39
Joined
Jul 4, 2013
Messages
2,770
Where should I put them, into the details section?


Sub reports can go in any section of the parent report but Details section is where I'd put unrelated subreports.


Automation to Excel is not difficult, more fiddly once you get the hang of it. There are plenty of code examples if you search. You can also put output on different excel worksheets.
 

Users who are viewing this thread

Top Bottom