Counting Caesarean Sections in Twins

[pivottable]

Hi all,

I'm a non-developer (clinician) that is trying to design a database for capturing delivery of babies.

I have a one to many relationship between tblDeliveries and tblBabies, because there can be twins, triplets etc.

I want to count the number of Caesarean Sections but as a proportion of deliveries instead of babies.

This gets tricky if a multiple birth has different modes of delivery, for example twin 1 has a normal delivery, then a problem arises and twin 2 requires a C Section. I would want to count this as a C Section.

A stripped down version of tables and fields which I think I need to use to achieve this:

tblDeliveries
DeliveriesPK
DeliveryDate

tblBabies
BabiesPK
DeliveriesFK
DeliveryTime
DelMode

There is a LEFT JOIN between tblDeliveries!DeliveriesPK and tblBabies!DeliveriesFK in the query I have created, where I am trying to do the counting.

I want to count the DelMode of the youngest baby for any given DeliveriesFK. This is because it is not possible for a normal delivery to follow a C Section during one delivery.

Any suggestions on how to build the right expression for Expression Builder would be great!

 

[plog]

First, [DeliveryTime] needs to be a Date/Time field and capture both the date and the time. Does it do that or does it just capture time? If you capture just time, then if one is born before midnight and one after, the one born after will appear younger than the one born before.

Assuming [DeliveryTime] captures both date and time, you will need a subquery to achieve what you want. This would be that subquery:

SELECT DelivieriesFK, MAX(DeliveryTime) AS LastBabyTime
FROM tblBabies
GROUP BY DeliveriesFK;

That gets you the earliest baby of every delivery. Now to get the Mode of delivery you build another query, using tblBabies and the above query. Link DeliveriesFK to DeliveriesFK and LastBabyTime to DeliveryTime and bring down the fields you want.

Again, DeliveryTime must contain both date and time.

 

[pivottable]

First, [DeliveryTime] needs to be a Date/Time field and capture both the date and the time. Does it do that or does it just capture time? If you capture just time, then if one is born before midnight and one after, the one born after will appear younger than the one born before.

Thanks plog! You've been really helpful with suggestions as I work my way through this design!

At present, [DeliveryTime] only captures time.

You have also made me realise that I should remove [DeliveryDate] from tblDeliveries, and have [DeliveryDateTime] in tblBabies, for the very reason you state above, the before and after midnight deliveries!

Just to clarify, MAX(DeliveryTime) would get the youngest baby, and MIN(DeliveryTime) the oldest?

 

[plog]

You have also made me realise that I should remove [DeliveryDate] from tblDeliveries, and have [DeliveryDateTime] in tblBabies, for the very reason you state above, the before and after midnight deliveries!

There may be some value in having date/time value in tblDeliveries. I don't know how much control over what data is captured or what your endgame is, but you could capture date/time when the mother entered the delivery room. With that you could determine how long each woman was there before a baby was born. You could also capture when she came out of the Delivery room to know things like average delivery length, etc.

Just to clarify, MAX(DeliveryTime) would get the youngest baby, and MIN(DeliveryTime) the oldest?

As long as DeliveryTime is a Date/Time field. Here's an example:

tblBabies
DeliveriesFK, DeliveryTime
12, 3/10/2011 8:00 pm
12, 3/10/2011 9:00 pm
29, 4/11/2011 11:00 pm
29 4/12/2011 12:31 am

SELECT DeliveriesFK, MAX(DeliveryTime) AS LastBabyTime FROM tblDeliveries

Will return this

DeliveriesFK, LastBabyTime
12, 3/10/2011 9:00 pm
29 4/12/2011 12:31 am

I believe that's what you want

 

[pivottable]

I have formatted [DeliveryDateTime] to be dd/mm/yyyy;hh:nn.

When I view this control in a form, and the field in a table, only the Short Date is displayed unless my cursor is actually in the control or field.

Can I get it to display the time by default?

 

[plog]

Sorry, I'm not the best with forms and formatting.

 

[vbaInet]

I have formatted [DeliveryDateTime] to be dd/mm/yyyy;hh:nn.

Can I get it to display the time by default?

The part in red is the format for Time. Otherwise select one of the Time formats in the Format property of your textbox.

NB: Just answering that one question, not necessarily following the thread.

 

[pivottable]

The part in red is the format for Time. Otherwise select one of the Time formats in the Format property of your textbox.

NB: Just answering that one question, not necessarily following the thread.

I want to record the date and time of an event (baby's delivery) in this field. In the form's textbox, I set the Format property to dd/mm/yyyy;hh:nn so both Date and Time are captured. If I move on to the next control in the form, the display reverts to only dd/mm/yyyy, only changing to show dd/mm/yyyy;hh:nn if the cursor is in the textbox.

If I set the Format property to Short Time, then the date won't be captured. The date is necessary for the query as suggested by plog.

 

[vbaInet]

Two textboxes, one displaying the time and another hidden textbox which you'll save the date and time to. Both bound to the same field.
Like I said, I'm only answering this bit so I'll let you and plog plod along.