Church DB Question regarding Hymn Check query 2

[pastorandy]

Hi
I am a Pastor of a church and have a database that I am creating to help me plan services much more quickly.
I have a small Access 2002 DB that generates an 'order of service' report each week.
I enter 4 hymns for the AM service and 4 hymns for the PM service (in the same service_id record for the date, as well as some other details that works ok thus far.
I have two tables. Hymn and Service.
The service table has fields such as: service_id, service_date, AM1 (these are just hymn_no's), AM2, AM3, AM4, PM1, PM2, PM3, PM4, etc.
The hymn table has the following fields: hymn_id, hymn_no, hymn_name
I do not have the tables joined. I just use the hymn table as a lookup.
The AM hymns are defined as AM1, AM2, AM3, and AM4 in the Service table.
The PM hymns are defined as PM1, PM2, PM3, and PM4,
I have a main form for data entry for the service.
The form is based on the service table with another query on hymn for the combi drop downs to select hymns in the service form.

What I am trying to do is have a check on selecting a hymn number from the combi box that will alert me if the hymn I am selecting in either of the AM1, AM2, AM3, AM4, PM1, PM2, PM3, or PM4 slots for the service - has already been selected within the last 60 days.

I don't mind how I am alerted - but it would be good if it was quite simple and unobtrusive.

Not sure if I have explained that well, but does anyone have an idea on how I could get the hymn alert?
Many thanks in advance.

 

[dhookom]

Sorry but I don't care much for your table structure. I would create a hymn junction table with fields like:

Service_ID
Hymn_ID
AM_PM
Num

This would allow you to more easily determine when a hymn was most recently in a service. If you don't want to or can't change the structure, you can create a normalizing union query like:

SELECT Service_ID, service_date, "AM" as AM_PM, 1 as Num, AM1 as Hymn
FROM Service
UNION ALL
SELECT Service_ID, service_date, "AM", 2, AM2
FROM Service
UNION ALL
SELECT Service_ID, service_date, "AM", 3, AM3
FROM Service
UNION ALL
SELECT Service_ID, service_date, "AM", 4, AM4
FROM Service
UNION ALL
SELECT Service_ID, service_date, "PM", 1, PM1
FROM Service
UNION ALL
SELECT Service_ID, service_date, "PM", 2, PM2
FROM Service
UNION ALL
SELECT Service_ID, service_date, "PM", 3, PM3
FROM Service
UNION ALL
SELECT Service_ID, service_date, "PM", 4, PM4
FROM Service

Then change your hymn combo box Row Sources to something like:

SELECT Hymn_ID, hymn_no, hymn_name
FROM Hymn
WHERE Hymn_ID NOT IN (SELECT Hymn FROM quniServiceHymn WHERE Service_Date > Date()-60)

This should keep more recent hymns out of the combo boxes.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[pastorandy]

Thank you for this. And yes, you are right, my DB needs to be structured properly. I just couldn't get it to work when I tried using a link table. Anyhow, I'm sorry I didn't make this clear in my post. I actually want the hymn list to include the hymn that was in a service less than 60 days ago, but to just highlight that it has previously been sung recently. I would like this so that I can over-ride the selection and have the hymn included if necessary. This happens sometimes.

Is there a way I can flag that up somehow?

Many thanks
Andy

 

[dhookom]

Then, I would change the Row Source to a value that shows the number of times the hymn has been used in the last 60 days

SELECT Hymn_ID, hymn_no, hymn_name, 
(SELECT Count(Hymn) FROM quniServiceHymn WHERE Service_Date > Date()-60) As CountLast60
FROM Hymn

EDIT: You would need to display all columns and could use the after update event of the combo box to display a message like:

If Me.cboHymnAM1.Column(3) > 0 Then
    Msgbox "You used this song less than 60 days ago"
End If

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[pastorandy]

Ok thank you. I'll give it a try and let you know how I get on. )

Many thanks.

 

[dhookom]

Hi PastorAndy,

You are very quick. I'm not sure if you read my edit with the MsgBox code.

Make sure you come back if you have any questions.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[pastorandy]

Thank you. Yes, I didn't see that last part when I posted.

Many thanks once again.

Andy

 

[MajP]

Duane,
Don't you need "Hymn_Name = [Hymn]" in your subquery?
(SELECT B.Count(Hymn) FROM quniServiceHymn as B WHERE B.Service_Date > Date()-60 and Hymn.Hymn_Name = B.Hymn)
I believe as written will give the count of all hymns used within 60 days

You could also have a two/or more column combobox that shows both the hymn and when it was last used. That would also allow you to see the "older" used hymns if you want to grab ones not used for a long time.

SELECT Hymn_ID, hymn_no, hymn_name, 
(SELECT Date - B.service_Date FROM quniServiceHymn as B WHERE Hymn.Hymn_Name = B.Hymn)as DaysSince
FROM Hymn

 

[dhookom]

Good catch MajP. Apparently I haven't had enough coffee this morning.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

Also comboboxes allow conditional formatting. So instead of message box you could have the record come up with a color, or shading.

 

[pastorandy]

Hi guys

Thanks for the replies. It has become apparent that my DB structure needs to be put right so I can do this properly. I am going to bite the bullet and redo it all.

Could you give me a few hints please?

I currently have two tables. Could you help putting this into a proper relationship…

Service: service_id, service_date, service_led_by, notices, childrens_talk, am_prayer_list, pm_prayer_list, AM_sermon_title, AM_bible_reading, PM_sermon_title, PM_bible_reading

Hymn: hymn_id, hymn_no, hymn_name, hymn_book, book_abbreviation, tag1, tag2, tag3

We have two services each Sunday (AM & PM). A service can have 4 or more hymns.

I am presuming a link table needs to be added.

Hymn_link: hymn_link_id, hymn_id, service_id

I tried something like this originally, but it wouldn’t work properly.
Could you advise without trashing my lack of knowledge here please.

Many thanks
Andy

 

[dhookom]

Andy,

You could use the union query I suggested to make a "link" table. A continuous subform would be needed to show hymns selection for each service. You can use the link master/child properties of the subform control to link the tables.

A crosstab query can be used to show the four (or more) hymns for each service much like your original table. The crosstab would be read-only.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

You would need to provide more information about the data you want to keep.

[pre]
Service table:
id AutoNumber
service_date Date
AM_PM Time 'switch', Yes/No field, needs a better name
led_by What is this field? A name of a person?
notices Memo field?
childrens_talk is that a Yes/No field?
prayer_list Looks like this 'list' needs another table
sermon_title Text
bible_reading What do you keep in here?
[/pre]



---- Andy

There is a great need for a sarcasm font.

 

[pastorandy]

Hi

id AutoNumber
service_date date
AM_PM Time 'switch', Yes/No field, needs a better name
led_by text field. person's name
notices text field. person reading the notices
childrens_talk text field. person's name.
prayer_list memo field.
sermon_title Text field
bible_reading text field. it's where I enter the Bible text for the sermon

 

[MajP]

If your service is a morning service do you currently only fill in AM1,..AM4, and evening service only "PM1".."PM4". If that is the case and you are using Duane's Sql to create your new table then you can remove AM_PM from the new table. Records in the new table are linked to a service and a service is Am/PM. Also you would no longer need Service_Date

SELECT Service_ID, 1 as Hymn_Counter, AM1 as Hymn_Name
FROM Service

I assume in your Hymn table there is a hymn number (hymn_No) which is the actual hymn number, but in you link table (1,2,3,4) you do not want to call that a hymn number. I would call it something like hymn_Counter.

If using Duane's sql the Hymn_ID is needed but not included. So before doing your make table query do an inner join on his union query linking by hymn_name to the hym table. Include the hymn_id but do not include hymn_name in your make table query.

Making the subform can be a little tricky if you never done it before with a many to many so come back for help. Now with your new design you can add additional hymns on certain days if needed.

 

[Andrzejek]

Like I suspected,
[pre]
led_by text field. person's name [person's ID]
notices text field. person reading the notices [person's ID]
childrens_talk text field. person's name. [person's ID]
[/pre]
looks like you need another table with People, and these 3 fields will keep the ID of the person from People table.

My simple 'rule' of DB design - if you repeat the data anywhere in your data base (not the IDs !), you are doing something wrong and need to improve it. That's why the People table so you don't have to repeat anybody's name, you just use their ID.


---- Andy

There is a great need for a sarcasm font.

 

[MajP]

To add, you currently have three roles for a Service
Led_By
Notice_Reader
Children_Talk

If there are other roles that you would potentially track, this could also be a separate table
tbl_Service_Roles
roleName (Notice Reader, Sermon Leader, Children Talk Leader, etc)
personID (foreign key to person table)
serviceID (foreign key to service table)

 

[pastorandy]

Hi
So i've spent the last few days working on a DB re-design.
My main purpose was to be able to get the hymn usage data and check that a hymn hadn't been selected within the last 60 days.

The Combo181 [AM1] on my main [Order of Service Form] has a query that pulls the hymn numbers from the hymn table.
I have an unbound text box on my [Order of Service Form] that pulls its value from the Combi181.

That displays the hymn name from the Combo181 column 2.

I have the query to check against the [hymn_usage] table to check if the hymn selected has been used within the date criteria. But how do I use that to change the colour of either the text of the hymn name on the unbound form field or the background colour of the text box?