Database Structure

[Lorenzo]

Hello Everyone!
I have a little complicated structure (at least for me !) and I would
appreciate any help.

The DB serves to track invoicing in two different directions. It is the
same company and it follows the same invoicing number which I handle thanks
to a function and not through the Autonumber field.
Until today I have developed the DB with in mind keeping the tblReservation
as the "one" part of the "one to many" relationship. I have a main form
from which I insert reservations. The sources for this main form are the
following tables:

The main table has the following structure:
tblReservation, IDReservation, dteReservation, dteArrival,
dteDeparture...etc...IDApartment

The first subMask is generated by the following table
tblInvoices, IDInvoice, dteInvoice, strNumberInvoice (which is
autoincrementing thanks to a function I call each time I want to add an
invoice #)
IDReservation, blnDwPayment, blnBalance, ....etc....
I have done so I can have more than one invoice for each reservation since I
have one invoice for downpayment and one for balance but all related to that
Reservation ID. Important: I want to prevent deleting the Invoices in case
I cancel a reservation though.

The second subMask relates to the cleaning services table so:
tblCleaning, IDCleaning, dteCleaning, intHours, .....etc...IDReservation
again here I have done so I can have multiple cleaning services for that
reservations.


The problem
I have come to te point I was adding another new from that handles invoicing
for offices rents. I wanted to use the same tblInvoice since I need to have
the same invoicing number. With my DB structured like this I am not able to
accomplish it because if I want to insert an "extra invoice" the Integrity
reference won't let me add any record to the tblInvoice unless I have a new
reservation as well. For the same reason I am not able to add to my DB a
new form for general cleaning services not depending from Reservations again
here I have the IDReservation in the cleaning tables that asks for its
related number.

Any help?
Thank you in advance.

Lorenzo

 

[Joseph Meehan]

I glanced over your information, so please excuse me if my comments do
not apply.

How about a table for Invoices and a second table for Invoice items.
That way you could have several items on the invoice with the same payee
address etc but one item for rent, one for product etc. One Invoice number
would cover one two or more items.

 

[James Hahn]

The IDReservation field is a required field in both tblCleaning and
tblInvoices. You need to determine why it's required. It might be required
at the table level because you have made it a part of the table key. It
might be required at the form level because you are using a query that
requires it as part of a lookup or update procedure.

I would recommend that you find a way to create a reservation (it might
contain only dummy data) for the offices, so that both offices and
residential have exactly the same data structure.
-

 

[Meindert Manshanden]

Your table relations define that you can't create an invoice without a
reservation.
If you can have an invoice without a reservation you can change the
relation between
tblInvoices and tblReservation. This way you can bypass the dummy.

 

[James Hahn]

That might help for invoices, provided there aren't reports etc that assume
a reservation exists for every invoice, but it won't help for cleaning.

 

[Lorenzo]

Guys thanks for all your help,
the only thing I have in mind and seems to really work to bypass everything
is to create a new table "activities" for example from which I can start
counting all the reservations, cleanings, invoices for both offices and
independent invoices or cleanings. I will provide each table that I need to
"make" fire with this IDActivity so in my forms I will just click a new
activity and consequently a new reservation will be created as well as new
cleaning and services related to that reservation-activity. In case I need
to add a general cleaning not related to a reservation I will just relate it
to that activity number not including the reserv table to the form.

The idea came thanks to Joseph with the ITEM invoice(what is the Irish math
thing? I want to know !!!) but many thanks to James too.
I do have several form related to Invoice# so I really can't have an invoice
without #

Thank you,
feels good to have friends around here
Lorenzo