October 13, 201114 yr I am trying to create what I thought was a fairly simple database and I am having trouble with one part of it. Maybe my brain has gone on vacation but I can't seem to get it wrapped around my problem for some reason. Here is the detail: The output of this database is to: Create a voucher write a letter to a county write a letter to the dept of agriculture I have 5 tables: Vouchers; Voucher Detail; Vendors; Drain Commissioners; Drains The scenario: Vendor A submits an invoice for work done on Drain X. Before we can pay the bill a voucher has to be created and mailed to the counties the drain is in and the dept of ag. All drain commissioners and dept of ag have to sign the voucher. Once signed and returned the bill can be paid. Drain X may be in 2 counties (ours and a neighboring county) or 3 counties (us and 2 neighboring counties) This is where my trouble is lying. I need to have 2 county letters and one ag letter. All I can get right now is 1 county letter and 1 ag letter. Layout #1 is a data entry layout (voucher tbl) they enter the date, drain #, invoice # from bill; deputy in charge, selects the vendor, and in a portal (voucher detail) select the account to charge, the amount, and description of work. Layout #2 is the voucher. It includes all data from Layout #1 but should have a spot for drain commissioner 1 and 2 to sign as well as the dept of ag and the deputy in charge. Layout #3: letter to the department of ag. This includes merge data from layout #1. It's related table is voucher detail. Layout #4: letter to the other counties: I only get 1 letter and I may need 2. Can you help me? dataentry.pdf agletter.pdf countyletter.pdf voucher.pdf
October 13, 201114 yr Drain X may be in 2 counties (ours and a neighboring county) or 3 counties (us and 2 neighboring counties) This is where my trouble is lying. That sounds about right - and the trouble is not lying with your layouts, but with your tables and relationships. How do you record the fact that a drain belongs to multiple counties? Ideally, there should be a join table between Drains and Counties. Possibly, you could get away with a checkbox in the Drains table. Edited October 13, 201114 yr by comment
Create an account or sign in to comment