Relationship issues

[Missionary]

I have a database that tracks baptisms in our Mission. Every week Missionaries from different areas submit the number of baptisms performed that week. On a separate fax, they later report the names of those baptised. I have a query that sums the number of baptisms reported and is grouped by [area] and by [month]. I have another query that counts the number of names also grouped by [area] and by [month].
Now, to make sure that all of the names were reported, I have a third query that correlates the data and shows Area, Month, SumofBaptisms, and CountofNames. (Of course hopefully, SumofBaptisms = CountofNames.) By setting the Join Properties, I was able to get it to show the proper data and check for discrepancies.
There is only one problem. If there were no names submitted for an area one month, the query doesn’t show a record for that area that month, even if the SumofBaptisms is 5 for example. How can I get it to show a record for every area every month even if there are no Names in the table?

I hope my explanation is clear. Thanks.

[missinglinq]

You have posted this question twice in 17 minutes! The duplicate posting has been deleted! Please refrain from this prohibited behavior in the future.

From FAQs

Do Not Double Post Your Questions

Double posting is where you start a thread on a topic and then for some reason start another thread on exactly the same topic in the same forum. Please do not do this because

1. It makes it very hard for people to answer you especially if there are answers happening in all the threads you have started because they have to read 2 or more threads in order to see what has already been said.
2. It swamps the forum with your problem resulting in less attention for the other threads.

If you feel for some reason that you post has been overlooked (for instance it hasn't had any replies) please do not repost the question. Post a message to the thread you started, this will bump it back to the top of the thread list for the forum.

Thank you for your attention in this matter.

Linq ;0)>
Moderator

[mshmyob]

You could create a default entry for every location for every month with Number of baptisms as 0.

Then your query would be an update query. This will then update only records that have new information and the Locations that had no baptisms for the month would show as 0.

I have a database that tracks baptisms in our Mission. Every week Missionaries from different areas submit the number of baptisms performed that week. On a separate fax, they later report the names of those baptised. I have a query that sums the number of baptisms reported and is grouped by [area] and by [month]. I have another query that counts the number of names also grouped by [area] and by [month].
Now, to make sure that all of the names were reported, I have a third query that correlates the data and shows Area, Month, SumofBaptisms, and CountofNames. (Of course hopefully, SumofBaptisms = CountofNames.) By setting the Join Properties, I was able to get it to show the proper data and check for discrepancies.
There is only one problem. If there were no names submitted for an area one month, the query doesn’t show a record for that area that month, even if the SumofBaptisms is 5 for example. How can I get it to show a record for every area every month even if there are no Names in the table?

I hope my explanation is clear. Thanks.

[Missionary]

You could create a default entry for every location for every month with Number of baptisms as 0.

Then your query would be an update query. This will then update only records that have new information and the Locations that had no baptisms for the month would show as 0.

I'm not sure I understand your suggestion, I worked on it for a couple hours, so let me explain what I've got.
First of all, in my Sum Query I already have a record every month in every area, it's just in the Names query that doesn't show records where there are no names.

So I played around with update queries for a while (my first time). I made a table that has a record for every month in every area and the number of baptisms listed as 0. Then I made an update query that was set to update the table fields to the values from the fields in the Names Query (i.e. [qryNames]![Month], [qryNames]![CountofNames], [qryNames]![Area]. )
I kept running into the error message "Operation Must use an updateable query."

Is there an easier way to get my origional query to show all SumofBaptisms records and and show "0" or Null where there is no corresponding CountofBaptisms value?

Thanks for your help.
PS, Sorry for the double post; my browser froze and I didn't think the first one made it in.

[mshmyob]

Can you describe your table structure for me and I will write the code needed.

I'm not sure I understand your suggestion, I worked on it for a couple hours, so let me explain what I've got.
First of all, in my Sum Query I already have a record every month in every area, it's just in the Names query that doesn't show records where there are no names.

So I played around with update queries for a while (my first time). I made a table that has a record for every month in every area and the number of baptisms listed as 0. Then I made an update query that was set to update the table fields to the values from the fields in the Names Query (i.e. [qryNames]![Month], [qryNames]![CountofNames], [qryNames]![Area]. )
I kept running into the error message "Operation Must use an updateable query."

Is there an easier way to get my origional query to show all SumofBaptisms records and and show "0" or Null where there is no corresponding CountofBaptisms value?

Thanks for your help.
PS, Sorry for the double post; my browser froze and I didn't think the first one made it in.

[Missionary]

Can you describe your table structure for me and I will write the code needed.

I have two tables.
The first one is [Baptisms] This table lists details of each person who is baptized. The fields that might be useful are [Baptism Date] (which is a date/time data type), [Area] (text), [Names] (if you needed something to count) .
The other table is called [Nine Keys].
This has, among others, the following fields: [Area] (text), [Date] (The date on the weekly report; Date/time data type), and [Investigators Baptized] (The number of investigators baptized each week in each area; Number data type).

I am hoping to have a query that shows Area, Month, SumofInvestigat ors Baptized, CountofNames. It needs to use all of the records from the [Nine Keys] and the corresponding counts from [Baptisms]. It would be nice to show a 0 where there are no Names in an area in a month, but Null is ok too.

Thank you so much for your help.
PS Sorry about the long names and spaces; it took me a while to learn the secret access naming system.

[mshmyob]

See if the attached sample database helps you. I put in data for December of 2007 and January of 2008.

So when you set the date filter choose Dec 1 2007 and Dec 31, 2007
or
Jan 1 2008 and Jan 31 2008

[Missionary]

See if the attached sample database helps you. I put in data for December of 2007 and January of 2008.

So when you set the date filter choose Dec 1 2007 and Dec 31, 2007
or
Jan 1 2008 and Jan 31 2008

I can't open the zip file. It says it's corrupted. Could you try again? Thanks a ton.

[Scott Price]

Windows Zipped Folder utility does indeed report it as corrupted, however WinRAR opens the archive just fine.

Regards,
Scott

[Missionary]

Thanks Scott

mshmyob,
I'm not sure I've explained myself very well as to what I want. I've attached a sample database that I made. It has two tables: one with the reported baptisms statistics and one with the names of the people baptized. With the queries (you'll see) I've got almost exactly what I want. The CheckBaptism query shows the sum of reported baptisms and the count of the names of the people. You'll see a few discrepancies in the numbers (I need this query to check for them). Everything works great except for one thing: As an example, in January Area 4 reported 2 baptisms, but there were no names submitted. In the CheckBaptism query, it doesn't show a record for Area 4 in January. I need it to show the number of reported baptisms (2) and a 0 for the count of names. Any suggestions?

Thank you for your help so far.
God Bless

[mshmyob]

Here you are. I redid your database to eliminate data redundancy and data anomolies for the way you want to do it.

I put in some sample data but not all that you supplied.

You can put in a Manual Count for a baptism date for an Area and also put in names if supplied for that Date and Area. (keep in mind you DO NOT have to put the names in if you do not want to or don't have them).

You can then run the report and it will give you a report based on date/area and then a count of the Manual Baptism counts for the month and also a count of names entered for the month. Obviously these might or might not match depending if you were given the names.

If you get an error opening it about the form frmBaptisms just ignore it. I was working with your stuff and it didn't clear out. It won't affect anything.

I hope this helps.

Thanks Scott

mshmyob,
I'm not sure I've explained myself very well as to what I want. I've attached a sample database that I made. It has two tables: one with the reported baptisms statistics and one with the names of the people baptized. With the queries (you'll see) I've got almost exactly what I want. The CheckBaptism query shows the sum of reported baptisms and the count of the names of the people. You'll see a few discrepancies in the numbers (I need this query to check for them). Everything works great except for one thing: As an example, in January Area 4 reported 2 baptisms, but there were no names submitted. In the CheckBaptism query, it doesn't show a record for Area 4 in January. I need it to show the number of reported baptisms (2) and a 0 for the count of names. Any suggestions?

Thank you for your help so far.
God Bless

[Missionary]

mshmyob,
It worked like a prayer.
Thank you. Thank you. Thankyou.

[mshmyob]

You're welcome. Good luck.

mshmyob,
It worked like a prayer.
Thank you. Thank you. Thankyou.