Duplicate records with change of date (1 Viewer)

lbs · Dec 22, 2019

Hi
I am developing a database for departmental rota of a hospital. There are going to be many many problems and challenges ahead. At the moment I've this one. I need to go and register meetings day by day and week by week. I was thinking if it is possible to duplicate one or preferably multiple records for one particular day but in the duplicated records I want to have the day 1 week ahead. Is it possible at all?

theDBguy · Dec 22, 2019

Hi. Duplicating a record is doable, but storing duplicate data is against Normalization rules. If the only difference is the date, then you just need a child table with a foreign key to refer to the main data.

vba_php · Dec 22, 2019

theDBguy said:
Normalization rules.

ahhhh, the word that has brought so much controversy to this place...

lbs said:
I need to go and register meetings day by day and week by week. I was thinking if it is possible to duplicate one or preferably multiple records for one particular day but in the duplicated records I want to have the day 1 week ahead. Is it possible at all?

sure, of course it is. but you're not clear as to what you're doing. to expand on what guy said, maybe 2 tables?

1
========
meetingID
meeting_topics_to_be_covered
(anything else that applies all future meetings)
etc....

2=======
instanceID
instanceID (identifying the type of meeting that is being repeated)
instance_topic
instance_organizer
etc.....

lbs · Dec 22, 2019

theDBguy said:
Hi. Duplicating a record is doable, but storing duplicate data is against Normalization rules. If the only difference is the date, then you just need a child table with a foreign key to refer to the main data.

Thanks theDBguy. Unfortunately I am not that good to get how to do this. I also was not clear to explain what I need.
I am registering meetings which have pretty similar pattern. For example each Monday there is the same meeting at the same time. I wanted to make my life easy and set a button Duplicate record so that the date is already there exactly a week ahead of the date in the current record which is duplicated. And I only will have to change the consultant who will be doing that meeting.

theDBguy · Dec 22, 2019

lbs said:
Thanks theDBguy. Unfortunately I am not that good to get how to do this. I also was not clear to explain what I need.
I am registering meetings which have pretty similar pattern. For example each Monday there is the same meeting at the same time. I wanted to make my life easy and set a button Duplicate record so that the date is already there exactly a week ahead of the date in the current record which is duplicated. And I only will have to change the consultant who will be doing that meeting.

Hi. Not a problem. If it's for a recurring meeting, then the main data for all the meeting should be the same, correct? If so, this information should be in a parent table. Then, all the other non-duplicate information, such as date and time of the meeting, list of attendees, etc., should be in a child table. There's no need to duplicate all the main data; otherwise, you'll have a non-normalized table structure.

lbs · Dec 22, 2019

theDBguy said:
There's no need to duplicate all the main data; otherwise, you'll have a non-normalized table structure.

The thing is yes I will need to change one filed in each record - the consultants who are doing the meetings will be varying. So I cant create a parent table, although I do have parent tables for Types of meetings and Consultants. The only thing I want that duplicating a record I already had the Date field filled with the date one week ahead

lbs · Dec 22, 2019

vba_php said:
sure, of course it is. but you're not clear as to what you're doing. to expand on what guy said, maybe 2 tables?

I am sorry I was not clear.
So, I am registering MDT meetings. The fields are: Date; Time; Week number (calculated); Consultant who covers meeting.
Consultants and Meetings have parent tables for further references.

There are several meetings per day. If I duplicate each meeting for the next week it will take a lot of time. I wanted to have some kind of efficiency and have the dates populated week ahead automatically

Micron · Dec 22, 2019

I posted here with a link but don't see it. Being moderated perhaps? I'll wait to see...

vba_php · Dec 22, 2019

I was going to let dbGuy (I still don't know his real name!) handle this cuz he was the 1st to answer, but...
===========================================================
well you can create future records a lot of ways in this app. but I think there are still more questions, at least in this guy's view:

Consultants and Meetings have parent tables for further references.

so how many tables do you currently have going on?

There are several meetings per day.

then my suggestion of 2 tables would work just fine I would think.

If I duplicate each meeting for the next week it will take a lot of time.

no doubt, which is why you should automate it. do you know how to do that?

I wanted to have some kind of efficiency and have the dates populated week ahead automatically

I doubt this would be difficult at all. you have to point to the current records (the current day's meetings), get the data and throw it into the appropriate table, either with custom code or a built-in VBA command provided by the app. but come to think of it, I don't think access provides a built-in for that purpose. however, I have an example duplicating a existing record and adding it as a new record with code. do you wanna see it?

if Micron ever gets issue solved, that might be helpful too...

lbs · Dec 22, 2019

vba_php said:
I doubt this would be difficult at all. you have to point to the current records (the current day's meetings), get the data and throw it into the appropriate table, either with custom code or a built-in VBA command provided by the app. but come to think of it, I don't think access provides a built-in for that purpose. however, I have an example duplicating a existing record and adding it as a new record with code. do you wanna see it?

Thanks o lot. And yes please can I have that code? I, however, probably don't have those skills to through data from one table to another. You need to create and run query or some kind of sql may be. I thought a simple code run by a button would do the job. The thing is, on Mondays there are certain meetings and on Tuesdays completely different meetings. So when I duplicate records for Monday the meetings would be automatically replicated with the times started and I only need to change a consultant.

lbs · Dec 22, 2019

vba_php said:
so how many tables do you currently have going on?

How I wanted to design is to have multiple tables. In the rota there will be MDT meetings scheduled with dates, times and consultants attending them (just one consultant each for meeting). There will be also register of other activities. Register of absentees. Register of trainee supervision
So at the moment I have tables:
Axillary (parent):
1 Rotas - with the fields rota id, rota start date, rota end date
2. Consultants - with fields Cons id, name, team (speciality)
3. MDTs - with fields mdt id, mdt name, day of week, typical starting time
4. Other activities - with fields activity id, active name
5. Tranees - id, tranee name

Registers (child tables)
5. MDT register - reg_id, rota_id, mdt_id, date, time, consultant
6. Other activities register - reg_id, rota_id, activity_id, date
7. Absentees register - reg_id, rota_id, date, full day or half day, consultant id
8. Supervision of tranees register - reg_id, date, consultant_id, tranee_id

That is just a frame. I started to do MDT meetings first and came across a lot of questions

theDBguy · Dec 22, 2019

lbs said:
Thanks o lot. And yes please can I have that code? I, however, probably don't have those skills to through data from one table to another. You need to create and run query or some kind of sql may be. I thought a simple code run by a button would do the job. The thing is, on Mondays there are certain meetings and on Tuesdays completely different meetings. So when I duplicate records for Monday the meetings would be automatically replicated with the times started and I only need to change a consultant.

Hi. As I said earlier, using code to duplicate a record is not a problem. The DoCmd object has a few methods you can look into like SelectRecord, CopyRecord, PasteAppend, and GoToRecord, to name a few. But if say, you have 10 fields and all of the records have the same data in those fields, except for one or two, then you have a structure problem.

So, using code is not always the right answer. It's like using an axe for a hammer to drive a nail, sort of.

lbs · Dec 22, 2019

theDBguy said:
So, using code is not always the right answer. It's like using an axe for a hammer to drive a nail, sort of.

Yes, I can now see the problem. I'll better leave it and just do copy multiple records then paste them and them change the dates manually

Thanks

theDBguy · Dec 22, 2019

lbs said:
Yes, I can now see the problem. I'll better leave it and just do copy multiple records then paste them and them change the dates manually

Thanks

Hi. I don't think you got my drift. The problem was not whether you use code to duplicate records or do it manually. My point was you shouldn't be duplicating records at all. You can duplicate a record and then modify most of the data, that's fine. But duplicating a record and only changing one or two fields is considered a bad design.

Duplicate records with change of date (1 Viewer)

lbs

Registered User.

theDBguy

I’m here to help

vba_php

Forum Troll

lbs

Registered User.

theDBguy

I’m here to help

lbs

Registered User.

lbs

Registered User.

Micron

AWF VIP

vba_php

Forum Troll

lbs

Registered User.

lbs

Registered User.

theDBguy

I’m here to help

lbs

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread