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.
--

 

[Lorenzo]

Ok I will try to make things as clear as possible. I have broke down the
structure to the simplest level so it can be better read.
I have created to perform the test, the following 3 tables.

Step one
1 tblReservation, IDReservation (only primary key), dteReservation
2 tblInvoices, IDInvoice (only primary key), dteInvoice, IDReservation
3 tblCleaning, IDCleaning(only primary key), dteCleaning, IDReservation

Version that causes the error

Step two
The relations: I use the Referential Integrity with all the options
seletcted (update and delete correlated fileds) for IDReservation in
tblResevation and tblInvoices and the same for IDReservation in
tblReservation and tblCleaning

Step three
I create one form from tblReservation (not from any query) then I add 2
subForms one for invoices and one for cleaning. Here everything works
because if I add a new reservation I can add as many records in cleanings or
invoices as I want that relates to that particular Reservation .

The problem
I close the previous form and I add a new one to insert only addiotional
cleaning in the Cleaning table one for mantainace. If I try to add one
cleaning at this time I get the error that a correlated field in
tblReservation is required to add this record.

What I have found that seems to work is the following:
Step one as above

Step two
The relations: Here when I select the Referential Integrity I go deeper
in the options and I select the three options, "apply Referential Integrity
" and Update and Delete correlated fields.
In the Type of Joins menu I select that I want to include all the records
in the 'tblFatture' and only the records in 'tblPrenotazioni' where the
related fields are equal. I do the same thing for IDCleaning.

Step three
Same as above

It works now. If I go and add a new form for cleanings or invoices I can
have as many as I want without have to worry about related fields in
tblReservation. In my IDReservations in the tblInvoice and tblCleaning I
will just see a "blank" field for any record that I add that I don't relate
to tblReservation.

I hope this can help sombody telling me if I am doing things wrong. n It
works fine but never know maybe it is not correct...
Lorenzo

 

[James Hahn]

It was never an issue of doing it right or wrong - it was only a question of
finding where the condition was being applied and removing it. I suggested
it was a required field, because I though you were altering a database that
had already been built, but it seems you are working with a new database. In
that case, any solution will be the correct one, because you now know that
when you build your reports and enquiries there can be invoice and cleaning
records that do not have a reservation record. An existing database might
have relied upon the relationship you have now changed, so the change could
have broken other things, like reports. But since you haven't built them
yet, this change isn't going to affect anything.
--