Again on structure

[Lorenzo]

Hello There,
I have called out for help on a structure matter and I don't seem to get
around this problem.

I have the following tables

tblReservation,
IDReservation, dteReservation, dteArrival,
dteDeparture...etc..

tblInvoice,
IDInvoice, dteInvoice, strNumberInvoice (which is autoincrementing thanks to
a function I call each time I want to add an invoice #)
IDReservation, blnDwPayment, blnBalance, ....etc....
IMPORTANT I have included the field IDReservation in the Invoice tables
because I can have more than one invoice for each reservation.

With the same procedure I also have
tblCleaning,
IDCleaning, dteCleaning, intHours, .....etc...IDReservation
once again here I have done the same thing so I can ahve multiple cleaning
services for that reservations.

Let's say I have all this on one form and all works fine as long as I work
in this reservation form.

The problem

If I need to add any additional service for example a cleaning for
mantainance of the apartment which does NOT require the adding of a new
reservation
I get stuck. The same thing happens if I need to add a form for adding
separated invoices not related to reservations.

Can you help me with the structure?

 

[James Hahn]

You haven't addressed the question already posed - why is the reservation
required for the invoice? For instance, is idReservation a part of the key
for the cleaning (or invoice) table? If so, why, and what would be the
result of removing it from the key? Is it a required field for some other
reason, such as a query you use on the form? From the description of your
table structure it appears that IDCleaning is a unique field, and a blank
IDReservation in the cleaning table should be quite OK. Perhaps a
reservation entry is required as part of the process of creating this
IDCleaning code. If so, you need to reconstruct that process so it can cope
with a non-existent reservation table entry.

Or, you haven't mentioned why you can't create a reservation table entry for
these invoices, even if there no actual reservation document and there's no
useful information in the entry.

This information is not available from a simple listing of table fields -
detailed information about the relationships for these tables and the source
of data for the fields would be required for a definitive answer.

 

[Lorenzo]

Hello James,
thanks for your help. The situation is as follow.
Right now I am not using more than one field to generate the unique key in
the tables. I use the autonumber in the tblReservation and I am using
numeric keys in the Cleaning and Invoice tables.
I have one IDReservation in the cleaning table and IDReservation in the
Invoices table. This is what I am doing that causes errors.
I have one form which is generated form the main table tblReservation. I
have then two subforms one for Invoices and one for Cleanings. This way I
would be able to bind each reservation to various Invoices, Cleanings. The
error comes when I use any other form and I want to add any other type of
cleanings or invoice, I get stuck because the tblCleanings or tblInvoices
seem to require a reservation. How can I relate the table in a way that I
can at the same time be able in one form to link together cleanings and
invoices to reservations but also be able to add cleanings or invoices
independently?.

Do you suggest to avoid using the Integrity rules at all?

My previous post was the following:
"Database Structure" posted on 19/01/2005 Lorenzo

Thank you so much,
Lorenzo

 

[James Hahn]

You are still avoiding the critical issue! "The error comes when I use any
other form and I want to add any other type of cleanings or invoice, I get
stuck because the tblCleanings or tblInvoices seem to require a
reservation."

Where do you get the impression that they 'seem to require a reservation.'?
Is there a message? Does it describe the problem? Or does the process
simply fail and the data is not stored? You need to determine why these
tables are structured to require a value for this field, or why this form
has been built so that it requires the reservation table entry.

For instance, does the IDreservation field in tblCleanings or tblInvoices
have the 'Required' attribute set to Y? Or does it have a validation rule
that requires an entry in the reservation table? Or is there a relationship
for this table that requires the reservation entry? Or is the form set up so
that this field is accessed by looking up a matching entry in the
reservation table.

You may need to do some experimenting. For instance, if you try create a
new form from the cleaning table and use that for data entry, does the
problem occur? If you enter data directly into the cleaning table, does the
problem occur? In either case, does the message change, perhaps to
something more informative?

Once you have discovered where the requirement for the reservation table
entry is coming from, you can explore the implications of removing it.

If you can't work this out, then quote the exact message that you are
receiving and the exact point in the process where it appears, and someone
may be able to interpret it for you.

 

[Lorenzo]

Ciao James,
don't beat me up with a stick on the back of my fingers now but ....I got it
figured it out...
the "error" was simply this: I was using the default setting in access for
which I need a correlated field in both tables of my relation. Going deeper
in the options I figured out I could use the left or the right join to
include all the record or not . The default setting in my relation was
requiring all fields from tblInvoice and all the fields of tblReservation
and so on. This way I could not have a blank field in any other
IDreservation in any other tables.

I Must have looked pretty newbie huh? I am learning though !
I thank you though becasue you are the one that really got me onto the right
path.
Lorenzo

 

[James Hahn]

I think you are saying that the form used for data entry was relying on a
query that included all fields from tblReservation. However, the problem
would not be a blank idReservation field in tblInvoices as such, but that
the key field required to access a reservation record (idReservation) had a
value (blank) that meant no matching tblReservation record was found. If you
had created a dummy tblReservation record with a blank key field, then a
matching record would have been found, and the form would have processed the
data. That would be one way of fixing it, but like any change,it has other
implications. If you can describe how you actually fixed it, someone may be
able to indicate what possible problems you need to look out for.
--