siperiea
New member
- Local time
- Today, 05:23
- Joined
- Feb 26, 2009
- Messages
- 8
Hi all,
I've achieved this previously in Excel via VBA, however, I've been tasked with setting the same up in Access. Being 3 weeks new to Access and still learning I've hit my first major hurdle! Have been searching for about a week, as it must be possible to do this, I'm obviously not approaching it correctly.
Data below is just sample data, actual volumes involved are into 00's and 000's each month.
I have a table of dates with the first of each month recorded, along with total counts of members (MemberType1 and MemberType2), so I know how many members were present historically:
(MemberType is a sum of all Members within that type - we have multiple types.)
Datenum # MemberType ...
01/01/2008 # 2
01/02/2008 # 4
01/03/2008 # 4
...
This table updates / appends new data every month (if no date is present, it appends totals, if date is present, it updates).
I have a second table which is a record of when each Member has had an issue (IssueType is so I can exclude certain types later on):
Date # MemberID # IssueType
01/01/2008 # Member1 # Issue1
05/01/2008 # Member2 # Issue1
16/01/2008 # Member1 # Issue1
03/02/2008 # Member1 # Issue2
04/02/2008 # Member3 # Issue2
22/02/2008 # Member3 # Issue3
22/02/2008 # Member4 # Issue1
05/03/2008 # Member2 # Issue4
15/03/2008 # Member2 # Issue1
01/04/2008 # Member3 # Issue2
09/04/2008 # Member2 # Issue2
21/04/2008 # Member2 # Issue1
04/05/2008 # Member2 # Issue3
17/05/2008 # Member3 # Issue2
...
I have a query that counts the total number of issues each member has each month, returning results as follows:
DateNum # MemberID # Count of MemberID
01/01/2008 # Member1 # 2
01/01/2008 # Member2 # 1
01/02/2008 # Member1 # 1
01/02/2008 # Member3 # 2
01/02/2008 # Member4 # 1
01/03/2008 # Member2 # 1
01/04/2008 # Member3 # 1
01/04/2008 # Member2 # 2
01/05/2008 # Member2 # 1
01/05/2008 # Member3 # 1
...
################# STUCK HERE #################
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
What I need to do is get the total number of issues each member has had over the previous three months for that month:
DateNum # MemberID # Sum of Count of MemberID(previous 3 months)
01/01/2008 # Member1 # 0
01/01/2008 # Member2 # 0
01/02/2008 # Member1 # 2
01/02/2008 # Member3 # 0
01/02/2008 # Member4 # 0
01/03/2008 # Member2 # 1
01/04/2008 # Member3 # 2
01/04/2008 # Member2 # 2
01/05/2008 # Member2 # 3
01/05/2008 # Member3 # 2
...
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
################# STUCK HERE #################
The idea is to then count the number of 0's, 1's, 2's and 3+'s each month (I can do this bit), apply a weighting, 0 = 100%, 1 = 66%, 2 = 33%, 3+ = 0%, and calculate a weighted percentage - hence the requirement for the totals to be recorded each month in the first table.
To give a query:
DateNum # 100% # 66% # 33% # 0%
01/01/2008 # 2 # 0 # 0 # 0
01/02/2008 # 3 # 0 # 1 # 0
01/03/2008 # 3 # 1 # 0 # 0
01/04/2008 # 2 # 2 # 0 # 0
100% actually calculated by (MemberType)-(sum of all issues in that month).
To give a final percentage for each month:
DateNum # % # Calculation
01/01/2008 # 100% # (2/2)
01/02/2008 # 83.25% # (3.33/4)
01/03/2008 # 91.5% # (3.66/4)
01/04/2008 # 83% # (3.32/4)
I've tried cross tab queries, but I can't sum the previous three months, as this impacts the next months calculation. Totally self taught, first post on any forum. All advice greatly appreciated.
I'm stuck on the bit highlighted above between vvvv and ^^^^
Apologies for length of post, I wasn't sure how much info to provide.
Cheers
I've achieved this previously in Excel via VBA, however, I've been tasked with setting the same up in Access. Being 3 weeks new to Access and still learning I've hit my first major hurdle! Have been searching for about a week, as it must be possible to do this, I'm obviously not approaching it correctly.
Data below is just sample data, actual volumes involved are into 00's and 000's each month.
I have a table of dates with the first of each month recorded, along with total counts of members (MemberType1 and MemberType2), so I know how many members were present historically:
(MemberType is a sum of all Members within that type - we have multiple types.)
Datenum # MemberType ...
01/01/2008 # 2
01/02/2008 # 4
01/03/2008 # 4
...
This table updates / appends new data every month (if no date is present, it appends totals, if date is present, it updates).
I have a second table which is a record of when each Member has had an issue (IssueType is so I can exclude certain types later on):
Date # MemberID # IssueType
01/01/2008 # Member1 # Issue1
05/01/2008 # Member2 # Issue1
16/01/2008 # Member1 # Issue1
03/02/2008 # Member1 # Issue2
04/02/2008 # Member3 # Issue2
22/02/2008 # Member3 # Issue3
22/02/2008 # Member4 # Issue1
05/03/2008 # Member2 # Issue4
15/03/2008 # Member2 # Issue1
01/04/2008 # Member3 # Issue2
09/04/2008 # Member2 # Issue2
21/04/2008 # Member2 # Issue1
04/05/2008 # Member2 # Issue3
17/05/2008 # Member3 # Issue2
...
I have a query that counts the total number of issues each member has each month, returning results as follows:
DateNum # MemberID # Count of MemberID
01/01/2008 # Member1 # 2
01/01/2008 # Member2 # 1
01/02/2008 # Member1 # 1
01/02/2008 # Member3 # 2
01/02/2008 # Member4 # 1
01/03/2008 # Member2 # 1
01/04/2008 # Member3 # 1
01/04/2008 # Member2 # 2
01/05/2008 # Member2 # 1
01/05/2008 # Member3 # 1
...
################# STUCK HERE #################
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
What I need to do is get the total number of issues each member has had over the previous three months for that month:
DateNum # MemberID # Sum of Count of MemberID(previous 3 months)
01/01/2008 # Member1 # 0
01/01/2008 # Member2 # 0
01/02/2008 # Member1 # 2
01/02/2008 # Member3 # 0
01/02/2008 # Member4 # 0
01/03/2008 # Member2 # 1
01/04/2008 # Member3 # 2
01/04/2008 # Member2 # 2
01/05/2008 # Member2 # 3
01/05/2008 # Member3 # 2
...
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
################# STUCK HERE #################
The idea is to then count the number of 0's, 1's, 2's and 3+'s each month (I can do this bit), apply a weighting, 0 = 100%, 1 = 66%, 2 = 33%, 3+ = 0%, and calculate a weighted percentage - hence the requirement for the totals to be recorded each month in the first table.
To give a query:
DateNum # 100% # 66% # 33% # 0%
01/01/2008 # 2 # 0 # 0 # 0
01/02/2008 # 3 # 0 # 1 # 0
01/03/2008 # 3 # 1 # 0 # 0
01/04/2008 # 2 # 2 # 0 # 0
100% actually calculated by (MemberType)-(sum of all issues in that month).
To give a final percentage for each month:
DateNum # % # Calculation
01/01/2008 # 100% # (2/2)
01/02/2008 # 83.25% # (3.33/4)
01/03/2008 # 91.5% # (3.66/4)
01/04/2008 # 83% # (3.32/4)
I've tried cross tab queries, but I can't sum the previous three months, as this impacts the next months calculation. Totally self taught, first post on any forum. All advice greatly appreciated.
I'm stuck on the bit highlighted above between vvvv and ^^^^
Apologies for length of post, I wasn't sure how much info to provide.
Cheers