Hope access at large can help with this. I am wondering how to set up my
database table architecture to meet the following demand. Thanks in advance.

I currently have in MS Access 2007 two tables linked and indexing through
primary key. In is an events log and the other is person log. both tables
have their own unique auto number. Now I need a third id number that auto
updates consecutively for the events log table's category field.

basically I need a control number for the event, one for each person
associated to any given event and now one for the type of event. I have six
types of events, therefore I will need it to autonumber based on the type
selected.

The only way I can see to do this is add six more tables (logs) for the
specific types then I would have to add six more sub forms to my input form
which only five would used on any given input form. Therefore I don't want
to add the useless subforms.

Does this make since and is there a way to do this? here is an example on
what I am trying to do:

Event ID and Person ID are continuous but the category control number is
continuous based on the category itself. Currently, I have to manually put
it in a text field but when you have hundreds, remembering or looking up the
last entry is laborous.

event ID category cat cntl# person prn ID
1 a 1 me 1
you 2
him 3
2 a 2 myself 4
3 b 1 he 5
4 c 1 me 6
they 7
them 8
5 a 3 she 9
6 d 1 her 10

I see need for four tables --
People - PeopleID, LName, FName, DOB, Phone, etc.
EventType - EventID, Type, Description
Event - date, sponsor, when, where, etc,
PeopleEvent - junction table with PeopleID & EventID

Set one-to-many relationship between People & PeopleEvent, Event &
PeopleEvent and EventType & Event.

Use a form/subform for People/PeopleEvent and Event/PeopleEvent with
Master/Child links set on the IDs. Use a Listbox in the Event form to select
the EventType.

--
KARL DEWEY
Build a little - Test a little


"DM" wrote:

> Hope access at large can help with this. I am wondering how to set up my
> database table architecture to meet the following demand. Thanks in advance.
>
> I currently have in MS Access 2007 two tables linked and indexing through
> primary key. In is an events log and the other is person log. both tables
> have their own unique auto number. Now I need a third id number that auto
> updates consecutively for the events log table's category field.
>
> basically I need a control number for the event, one for each person
> associated to any given event and now one for the type of event. I have six
> types of events, therefore I will need it to autonumber based on the type
> selected.
>
> The only way I can see to do this is add six more tables (logs) for the
> specific types then I would have to add six more sub forms to my input form
> which only five would used on any given input form. Therefore I don't want
> to add the useless subforms.
>
> Does this make since and is there a way to do this? here is an example on
> what I am trying to do:
>
> Event ID and Person ID are continuous but the category control number is
> continuous based on the category itself. Currently, I have to manually put
> it in a text field but when you have hundreds, remembering or looking up the
> last entry is laborous.
>
> event ID category cat cntl# person prn ID
> 1 a 1 me 1
> you 2
> him 3
> 2 a 2 myself 4
> 3 b 1 he 5
> 4 c 1 me 6
> they 7
> them 8
> 5 a 3 she 9
> 6 d 1 her 10
>
>

Thank you;

With this structure, I see the same thing I already get unless I am not
seeing your full picture. with one EventType table that has only one
autonumber, the number goes from 1 to what ever. I need it to be dependent
on the type (type a_1-1000; type b_1-to 1000; etc).

Is there an if statement maybe to do this in a text field? Maybe I don't
understand junction table...

Thanks again


"KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message
news:E2F899C6-DEC3-496B-B1A8-4AFEB6FD9CD5@microsoft.com...
>I see need for four tables --
> People - PeopleID, LName, FName, DOB, Phone, etc.
> EventType - EventID, Type, Description
> Event - date, sponsor, when, where, etc,
> PeopleEvent - junction table with PeopleID & EventID
>
> Set one-to-many relationship between People & PeopleEvent, Event &
> PeopleEvent and EventType & Event.
>
> Use a form/subform for People/PeopleEvent and Event/PeopleEvent with
> Master/Child links set on the IDs. Use a Listbox in the Event form to
> select
> the EventType.
>
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "DM" wrote:
>
>> Hope access at large can help with this. I am wondering how to set up my
>> database table architecture to meet the following demand. Thanks in
>> advance.
>>
>> I currently have in MS Access 2007 two tables linked and indexing through
>> primary key. In is an events log and the other is person log. both tables
>> have their own unique auto number. Now I need a third id number that auto
>> updates consecutively for the events log table's category field.
>>
>> basically I need a control number for the event, one for each person
>> associated to any given event and now one for the type of event. I have
>> six
>> types of events, therefore I will need it to autonumber based on the type
>> selected.
>>
>> The only way I can see to do this is add six more tables (logs) for the
>> specific types then I would have to add six more sub forms to my input
>> form
>> which only five would used on any given input form. Therefore I don't
>> want
>> to add the useless subforms.
>>
>> Does this make since and is there a way to do this? here is an example on
>> what I am trying to do:
>>
>> Event ID and Person ID are continuous but the category control number is
>> continuous based on the category itself. Currently, I have to manually
>> put
>> it in a text field but when you have hundreds, remembering or looking up
>> the
>> last entry is laborous.
>>
>> event ID category cat cntl# person prn ID
>> 1 a 1 me 1
>> you 2
>> him 3
>> 2 a 2 myself 4
>> 3 b 1 he 5
>> 4 c 1 me 6
>> they 7
>> them 8
>> 5 a 3 she 9
>> 6 d 1 her 10
>>
>>