Reset Field Value

[Mile-O]

Because:

Bookings: can have many people
People: can appear on many bookings (from what I've read)

Therefore you have a many-to-many relationship which can only be simulated with a junction table; hence PersonsToBookings. And it's in this table that the Delegate's ID is assigned.

 

[BoroLee]

I have in my db :

an Events tbl which handles the events.

a Contacts tbl which handles contacts. New delegated MUST be a contact first

a Persons tbl which shows the Event they have booked on from the Events tbl, there ContactID from the Contacts tbl and a BookingID. This is the number is want to reset to 1 should there be no present booking on an event

 

[Mile-O]

an Events tbl which handles the events.

Good.

a Contacts tbl which handles contacts. New delegated MUST be a contact first

Bad. This is duplication of data. Stick to the one table for Persons. If need this ContactStatus then have a Date field called Delegate. When they move from mere Contacts to Delegates then put the date this happened in the Delegate field.

a Persons tbl which shows the Event they have booked on from the Events tbl

Bad. An EventID should not be dependant upon a PersonID. This only allows ONE event EVER for a person. I don't think you want this. I've offered scope for not needing to change the structure in the future.

a Persons tbl which shows the Event they have booked on from the Events tbl, there ContactID from the Contacts tbl and a BookingID.

Regarding Contacts and Persons see the point above.

This is the number is want to reset to 1 should there be no present booking on an event

I'm trying to tell you to go with my structure because:

• tblPersons will hold all the people. As you've said Contacts are no different from Delegates except for one little matter of status. Therefore they can all be modelled into one table. Two tables holding the same names is not normalised;
• tblEvents will hold all possible events and is used only as a lookup table as that's all it should be;
• tblBookings will hold details regarding a booking made by a delegate such as the event, the date the booking was made, and the delegate who made the booking;
• tblPersonsToBookings now holds allows you to select every person from the Persons table into a specific booking. A small bit of code can therefore assign the relevent DelegateNumber by performing a simple lookup down the two fields here. The delegate number should be here.

 

[BoroLee]

i think that tblPersons may be confusing. SHould really have been called tblBooking

 

[BoroLee]

I still don't think i'm getting this across correctly.

tblContacts holds all the details about people.

tblPersons only holds the ContactID from tblContact. Therefore no duplication of data.

tblEvent holds all details about Events.

tblPersons only holds the EventID from tblEvent. Therefore no duplication.

tblPersons has a field called PersonID. This is a bad description, and is in fact only there as a Primary Key. Should be called ID only.

Make more sense???????

 

[Mile-O]

Here you go: all done with one line of code.

The only thing I'd possibly change is moving the date of event into the tblBooking as events will more often than not occur more than once.

 

[BoroLee]

cheers for all your help, but that does not help me.

My tables have been populated so don't want to strat creating new tables.

don't get why i can't do wot i wnat with the tbl's i have

 

[Mile-O]

cheers for all your help, but that does not help me.

Okay. Just answer me this, though - was that what you wanted?

I don't understand why you'd populate tables if you don't even has the structure correct and working.

 

[BoroLee]

wot happened was i was given a spec to design this thing.

i was told it was working as they wanted, then hey presto, they changed there minds and decided that people could not be a delegate, if they were not a contact already.


it was kinda wot i wanted. thought i would be able to populate the BookingID field without having to create new tbls'

 

[BoroLee]

Mile,
In case your interested, i got it to work using the following :

Private Sub cboContact_AfterUpdate()

If DCount("BookingID", "tblPersons", "[EventID]=" & Me.EventID) = 0 Then
Me.BookingID = 1
Else
Me.BookingID = DCount("[PersonID]", "[tblPersons]", "[EventID]=" & Me.EventID) + 1
End If

End Sub