table structure

[Zoe]

I have a table called tblCheckRequests and a table called tblRequisitions. I
need to add some new fields to the database to collect data regarding
Invoices. This invoice data relates to both Check Requests as well as
Requisition Requests. I have created a new table called tblInvoices that
captures InvoiceNumber, InvoiceID, Date, status, amount etc.
My questions is this. I need to capture this invoice data as it relates to
both check requests and requisitions requests. How do I go about doing this?
If I add both the CheckRequestID as well as the RequisitionID to the Invoice
table - then either would be left blank depending on the situation. I am not
sure how to set this up? Hope I provided enough detail for you. Thanks.

 

[Jeanette Cunningham]

Hi Zoe,

What are the rules at your office about requisitions and checks?
For example, if a person is requesting a check, will the check always be for
an item on the requisition list?
Do you have a relationship between the tblChecks and tblRequisitions?
Do some check requests arise when you want to pay an Invoice, and other
check requests have nothing to do with Invoices?
If so post back with details and we can help further.

Jeanette Cunningham

 

[Zoe]

Most of the time - a Requisitions form is used to process invoices. When
completing the Requisition form - we are capturing a company code, accounting
unit, accounting number, sub account # etc. along with a Purchase Order # and
there can be multiple invoices relating to one requisition.

Once in a while - the check request form is used which is processed
immediatley and is not matched up against a Purchase Order #. The check
request form captures alot less info than the Req request. It still captures
a company code, accounting unit, accounting number, sub account #. This
populates the CheckRequest table.

My user wants to be able to track data relating to an invoice - amount, date
paid, invoice status etc.(Invoice table) I was originally going to tie this
table to the Requisition Table via the RequisitionID however she needs to
also track the same invoice data if requested via the Check Request form
(tblCheckRequest)

So that is where I am stuck. I can't put both a CheckRequestID and
RequisitionRequestID in the Invoice table as one of them will always be
blank? If entering the invoice data via the requisition form - then there
will be no CheckRequestID and vice versa if entering the invoice data via the
Check Request form then there will be no RequisitionID. I hope I am
explaining this clearly. Please let me know if you need more information to
answer my question. Thanks so much.

 

[Jeanette Cunningham]

Zoe,
It looks like there is a one to many relationship between tblInvoices and
tblRequisitions.
There is also a one to many relationship between tblInvoices and
tblCheckRequest.

You can have both tables related to tblInvoices, but neither the
Requisitioned or the CheckRequestID is required in tblInvoices,
but you want to make sure that either the RequisitionID or the
CheckRequestID is there.

You may wish to post this as a new question for one of the MVP's (who have
more experience than me) to answer.

My suggestion would be to have a table for InvoiceSource
The table would have an InvoiceSourceID as its primary key.
It would have both the Requisitioned and the CheckRequestID.
There would be a one to many relationship between tblInvoices and
tblInvoiceSourceID.

An invoice could only be entered if it has an InvoiceSourceID.
This way the InvoiceSourceID is always related to either a CheckRequestID or
a RequisitionID.
This way you would always be able to track the details for a particular
invoice.

Jeanette Cunningham

 

[Jeanette Cunningham]

On second thought, it could be simplified to2 tables.
tblRequisitions and tblInvoices
tblRequisitions would have a primary key, there would be a yes/no field to
track if it was a requisition request or a check request.
Both checks and requisitions have a few fields in common that allow someone
to track where the invoice came to and from.
This way check requests are a just a special type of requisition with fewer
details than requisition requests.
tlbRequisitions needs to have fields for all the data for check requests as
well as requisition requests.
This setup should be a lot simpler to manage than my first suggestion.

Jeanette Cunningham