UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Tracking Paperwork And Payments, Access 2016    
 
   
Tuckejam
post May 30 2020, 08:29 AM
Post#1



Posts: 3
Joined: 30-May 20




I have spent the last two weeks trying to figure this one out, asked some friends and posted the question to a different access forum, and so far I have found no love.


Ok I Have Chapters that report to the State Association.

Every month each chapter has to turn in: an audit form, bank statement and a check to pay its dues
The problem is things are often late, and I need to know how late,

So I am trying to Track, for each chapter, for each month

Chapter x
Attended Jan 2020 Mtg (Y/N)
Jan 2020 Audit (Date Received)
Jan 2020 Bank Statement (Date Received)
Jan 2020 Dues Payment (Date Received)

How do I set up the table or tables to do this????

I have been learning and building this db over the last 6 months and have completed over 20 hours of online Access Training so I understand access, and basic db design but this problem eludes me.

Thanks in advance for the help
Go to the top of the page
 
GroverParkGeorge
post May 30 2020, 08:38 AM
Post#2


UA Admin
Posts: 37,510
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

You could help us help you by providing a detailed description of the existing tables, perhaps a screen shot of the relationship window would be the best.


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Tuckejam
post May 30 2020, 09:04 AM
Post#3



Posts: 3
Joined: 30-May 20



Attached File  OHC_db_Relationships.PNG ( 79.91K )Number of downloads: 7


(hmmm... Not sure how to attach this image, so consider this attempt number one)


as you can see (if the image loads) Lots of tables, lots of different things I am playing around with (login screens, transaction data for the checking account) Most not related to this issues.

My current though/plan

tbl_ppkItem
ItemID
Item [Audit, bank statement, payment]


tbl_ppkMonth
MonthID
Month [Jan, Feb, mar, etc]

tbl_ppkYear
YearID
year [2018, 2019, 2020, 2021 etc]


Then make a unique ID for each item, month, year combination

tbl_ppkGroup
ppkGroupID
ppkItem
ppkMonth
ppkYear


Then maybe tie in each unique group id [item,month,year] to each chapter??????
I have no Idea where I am going with this plan, I just had to have SOME forward movement after thinking about this for two weeks and getting nowhere
Go to the top of the page
 
Tuckejam
post May 30 2020, 09:08 AM
Post#4



Posts: 3
Joined: 30-May 20



Attached File  Utterly_Lost.PNG ( 158.83K )Number of downloads: 5
Go to the top of the page
 
GroverParkGeorge
post May 30 2020, 09:12 AM
Post#5


UA Admin
Posts: 37,510
Joined: 20-June 02
From: Newcastle, WA


Thanks for the detail.

So, at the moment you have nothing set up at all to track paperwork it appears.

I think the basic outline you propose would work. However, you need a way to track the actual arrival dates of the items in question. I think that would be an additional field in your proposed table tbl_ppkGroup. Then you could determine "timeliness" by simply querying that table for dates, using a criteria that checks the actual date an item was received. You may want to add a field to your proposed tbl_ppkItem table for the "DueDate", which would probably not be an actual date per se but the number of days the chapter has to submit those items, i.e. 10 or 15.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post May 30 2020, 11:56 AM
Post#6


UtterAccess VIP
Posts: 12,404
Joined: 10-February 04
From: South Charleston, WV


My opinion is you need to move away from mixing apples and oranges. First and foremost you need a receipts table. That would be the amount, date received, and who sent it. And an audits table, a meetings table, and a bank statements table. Then you search these tables to confirm that whoever has sent in whatever and if not say so.
You would never put a "January 2019" control on a form.

--------------------
Robert Crouser
Go to the top of the page
 
tina t
post May 30 2020, 01:14 PM
Post#7



Posts: 6,691
Joined: 11-November 10
From: SoCal, USA


i agree with George. if all you need to track is what happened - a payment was received, a document was received - a table to document those occurrences will do it. a field for "the date it happened" in tbl_ppkGroup will do that, like George said, along with a foreign key field ChapterID to link each occurrence to a specific chapter.

i get the feeling, however, that you see tbl_ppkGroup as a kind of "control list" - what should/may happen, including when (month and year). if that's the case, then you have a many-to-many relationship between tbl_Chapters and tbl_ppkGroup: one chapter may have many group occurrences, and one group occurrence may happen with many chapters.

to support that relationship, you'd need a linking table, as

tblChapterGroupOccurrences
cgoID (autonumber, primary key)
cgoChapterIDfk (foreign key from tbl_Chapters)
cgoPpkGroupIDfk (foreign key from tbl_ppkGroup)
cgoDate (the date of the occurrence - a payment was received, a document was received, a meeting was attended)

i threw in that last bit about meeting attendance, because of your original post:

QUOTE
So I am trying to Track, for each chapter, for each month

Chapter x
Attended Jan 2020 Mtg (Y/N)
Jan 2020 Audit (Date Received)
Jan 2020 Bank Statement (Date Received)
Jan 2020 Dues Payment (Date Received)

if you see tbl_PpkItems as a list of "activities" rather than a strictly a list of documents, you can accommodate meeting attendance by adding it to that table. and then adding the month/year records for that item in tbl_ppkGroup. then you can track it in tblChapterGroupOccurrences.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th July 2020 - 07:04 AM