Count as One When Same Address

[aellistechsupport]

Hello,


I'm not sure how to figure this out.

I'm attaching the database ZipCodeMemberships.zip and I can do a Find Duplicates ("MoreThanOneMbrInHousehold" query) on the address fields but then how to use that to incorporate it with the other records to show the final count of memberships?

A person can become a member and given a membership number. They can renew each year.
The memberships table "dbo_v030ALLMembershipsAllDates" has the membership information and when each membership ends.
There can be multiple records for the same person with different EndDates AND a membership in varying member types.

I think I was able to get the total count of memberships "SingleMembershipsInv030ALLMemberships" and not count it if the same member had memberships in different member types. And total count of memberships for each member type "MembershipTotal"

Further, a person can have the same address as another (same household).
I need to count the membership only once if they have the same address (same household) in a given membership year.

I hope this helps explain what's in the table and what I'm trying to do but not sure how to accomplish and someone can assist on how to figure that out.

[CJ_London]

some questions

need to count the membership only once if they have the same address (same household) in a given membership year.

1. how do you handle typo's
2. from this statement it sounds like you want a count of the number of households, not members? Please clarify

as to the basic answer, create a SELECT DISTINCT query on the fields that define a member (sounds like it would be all the address fields) with a criteria of

WHERE EndDate Between startofmembershipyear and endofmembershipyear

You'll need to supply the dates.

Then have a second query based on the first query to get your count

[aellistechsupport]

Hi Ajax,
any spelling errors is expected.
that said, they do not want the count of memberships IF they have the same address (live in the same household).
so say there are 3 members living in the same house (having same address), it should be counted as 1.

I can do the date range parameter, trying to find out how to figure out how to count memberships as 1 if the members have the same address.

As mentioned the starting point was doing the Find Duplicates query "MoreThanOneMbrInHousehold" and not sure where to go from there to determine the end result.

NOTE: in the sample data there are 2 years worth of membership information for an age group due to the size limit that can be uploaded here.

so for now forgo the date range. based on ALL the data available how do I figure out to count 1 when there are multiple members in the same address.

[CJ_London]

From what you describe

so say there are 3 members living in the same house (having same address), it should be counted as 1.
...
...
how do I figure out to count 1 when there are multiple members in the same address.

as previously advised you need to do is use a select distinct on the addresses

[aellistechsupport]

Please provide an example of the SELECT DISTINCT you've advised to get the results requested.

[CJ_London]

SELECT DISTINCT Addr1, Addr2, Addr3 FROM myTable WHERE enddate BETWEEN startofyear and endofyear

[HiTechCoach]

Took a peek at your database and there is one table to hold all the members and their membership activity. I hate to break it to you but you have a spreadsheet not a Relational Database.

In a properly normalized database, what you want would be basic stuff.

I would recommend having at least these tables:

tblAddrress - a master list of each unique address


tblPeople - a master list of all the people - Name DOB, age, email
-- use a foreign key to link tot eh master Address table. This way it is very easy to identify all the people at the same address

tblMembership - basic info about each type of membership a person has

tblMemebershipActivity - a sub/child table for tblMembership with records for each membership cycle. This would have the Start/End dates, year, paid, etc.
--

[aellistechsupport]

HiTechCoach,
That's a view that I imported some of the data results from SQL to MS Access in order to get to the report results.

The actual tables are several which include:
1. Person table
2. Address
3. PersonMemberships
4. MemberTypes

It's really irrelevant here with what's going on.
Since it's the links between all these tables that created the detailed information needed on the report in the SQL View that's linked (for here, sample data imported to as a table).

So what you've stated doesn't help here with what's at issue.

The report needs to include the details from all these tables, which is the dbo_v030MembershipsAllDates View. From there these reports are needed.
One I'm struggling with is finding ALL the member info in a given date range BUT to only count it once IF they are in the same household.

Ajax, that is what I thought you're suggesting, however it's not producing the correct as I am still seeing membership info from the same household.

SELECT DISTINCT dbo_v030ALLMembershipsAllDates.StreetOne, dbo_v030ALLMembershipsAllDates.City, dbo_v030ALLMembershipsAllDates.State, dbo_v030ALLMembershipsAllDates.[USFSA#], dbo_v030ALLMembershipsAllDates.LastName, dbo_v030ALLMembershipsAllDates.FirstName, dbo_v030ALLMembershipsAllDates.ZipCode
FROM dbo_v030ALLMembershipsAllDates
WHERE (((dbo_v030ALLMembershipsAllDates.EndYear) Between Year(Date())+IIf(Month(Date())>=7,1,0) And Year(Date())+IIf(Month(Date())>=7,1,0)+4))
ORDER BY dbo_v030ALLMembershipsAllDates.StreetOne, dbo_v030ALLMembershipsAllDates.City, dbo_v030ALLMembershipsAllDates.State;

[HiTechCoach]

HiTechCoach,
That's a view that I imported some of the data results from SQL to MS Access in order to get to the report results.

The actual tables are several which include:
1. Person table
2. Address
3. PersonMemberships
4. MemberTypes

It's really irrelevant here with what's going on.
Since it's the links between all these tables that created the detailed information needed on the report in the SQL View that's linked (for here, sample data imported to as a table).

So what you've stated doesn't help here with what's at issue.

That tells me that your initial view it the problem. Why not make a view that does what you need or create an Access query using the actual tables to get exactly what you need? It would be a lot less work!

Taking normalized data and then creating a query/view to de-normalize (flatten) the data for a report and then trying to extract the normalized data from it is a lot of extra effort that may never work correctly.

[aellistechsupport]

HiTechCoach,
I'm sorry, I'm not seeing what the difference is creating a View in SQL or Query in Access. Either way the detailed information that's in the Access table from the View is required.
And based on that, using Access for a dashboard / UI to run a few different reports based on user input, is why Access is being used here.

Again, not sure what difference it makes to create the query in Access to get the detailed information?
Goal here is to get the particular totals based on the detailed information. This point being based on the same address for the members.
In order to obtain address information for the members and the memberships for these members is by creating the View in SQL or Query in Access.
That part is done.

The stage of reporting at issue is to then determine the counts based on same address as one membership.

[HiTechCoach]

I think it is best I step aside for now and let others try to help you.

I will keep monitoring this thread.

Good luck with your project.