Need help defining relationship

[tottijohn]

Attached a sample for better clarification.

Right now, all my records are entered through 'Amending Initial Booking' layout and into 'Whole Database' table. The 'Amended Initial Bookings Table' is related through the 'Group Reference' field to the 'Whole Database' and used to store the latest amended records by using last function. This is to display only the latest amendments while able to keep a backup incase of any contigency.

Using 'Group Reference' as the primary key, there will also be another table called 'Double Entry Database', which will store any 'Group Reference' that will have a second arrival date.

So right now, i will like to have a layout, calls 'Hotel Booking', which will be able to sort, find and display records base on 'Arrival Date' and 'D'Entry Arrival Date'. For example in the sample file, i want to search for 02/11/06. 'Group Reference' A0001 should display on the top body while A0002 will display on the lower half.

I can't get the relationship to work this way, any help is appreciated, thanks

New7.zip

[Fei Long]

When you search, where you input the search on top or bottom? Which date ?The one in the body or the footer ?

[tottijohn]

On top, in the body part. I know i need to define a relation between the two Arrival Date, but with the Group Reference already being used, i hit a wall right there....

[Lee Smith]

Even though you are formatting the date as day, month, year, the system settings for the date are Month, day, year. So, I believe you have to enter the date as 11/2/2006, because if you enter 2/11/2006 there isn't a match.

HTH

Lee

[tottijohn]

Hi lee, yep system date setting is one issue, but the real problem here is that since the two tables are related via only "group reference" (The same group reference may have more than 1 arrival date, thus i created the 2nd table for all the 2nd entry).

So on a layout, I want to display and sort records from both tables according to their respective arrival date. Right now, since its only related via "group reference" searching arrival date will only yield the first table "group reference" and not the 2nd arrival date.

So is it possible to have different kinds of relationship between 2 tables for different layouts?

Please tell me if i am not making sense here, thanks ;)

[Fei]

You can create an extra table then relate to both arrival date. Then you can do sort from there.

[tottijohn]

Why din i think of that?! Anyway what calculation do you use for the field in the duplicated table? I can't seem to duplicated a record into two tables.

I am trying to use Lookup value from another table so that the new table will have the same fields values but i can't get it to do it automatically.

Edited November 20, 2006 by Guest

[tottijohn]

Still stuck at trying to duplicate a table... Its not as easy as it seems