New to Access db

[Brent]

I am trying to develop a db to track people that go to conferences, where,
when, how much etc. I have three tables, one called employees, one called
events and one called funding centres. I have tried to form relationships
between tables and I think this is where the problems start. In the employee
table I have employee ID, first name, last name and employee number. In the
events table I have event ID, conf name, location, date of travel, date
returned, reg costs, travel costs, accommodation costs, refund deadline and
employee ID. In the funding center table I have payment ID, accounts,
functional centre, other funding sources and employee ID.
I have a one to many relationship from the employee table employee ID to
funding centre employee ID. I have a one to many from the employee ID in the
employee table to the events table employee ID.
First of all can someone tell me if these relationships are OK or are there
other fields I need to have for this to work? I set up a form with the
employee fields on top and a subform with an event and a funding subform but
it does not work.
Sorry for the length of the note but I wanted people to have the whole
picture. Any help would be appreciated.

Thanks Brent

 

[Evi]

Hi Brent
It's a promising start (and you've explained most of it very clearly) but
I'm not sure about having EmployeeID in both Funding Centre and EventID.

I'll explain.


The sort of structure you might expect to see is

TblEmployee
EmpID
Employee details (nothing to do with the event)

TblEvent
EventID
ConfName
Venue
EventDate
EventCost (if each Employee pays the same amount - otherwise this will go in
TblAttendEvent.
This table should have nothing to do with individual Employees - these
details will be the ones which are the same for anyone who attends the event
so that you don't have to type them out for each Employee
The grey areas are things like RegFee (is that the same for everyone? if
yes, it goes here, otherwise it goes in TblAttendEvent)

TblAttendEvent
AttEventID (primary Key)
EventID (linked from TblEvent - Foreign Key Field'
EmpID (linked from TblEmployee
AmountPaid (what the employee paid to attend the event)




TravelDate and ReturnedDatewould go into TblEvent if all Employees will be
travelling on the same day
Or it could go into TblAttendEvent if Employees might travel on Different
Days

The signal for where to put things is, "will I need to type that same data
for each record in this table?" If yes, then it should be in another table -
by Data I mean anything other than the foreign key field

Now for the grey area because I don't know what you mean by Funding Centre.

If a Funding Centre concerns how Events are funded, eg a Charitable Fund
donates money so that you can host this Event
AND if each Event is only funded by 1 Funding Centre
then
PayID will be the Foreign Key field in TblEvent

If a Funding Centre is how that particular Employee is Funded for that event
and Each employee is only ever funded per Event by one funding centre then
PayID would be the Foreign Key field in TblAttendEvent.

If a Funding Centre funds an individual Employee to attend any event and
each Employee is only funded by one Funding Centre then PayID would go into
TblEmployee

If an Event or 'Employee at an Event' or an individual employee can be
funded by more than one Funding Centre then the situation changes again.

So if an Event could be hosted by several Funding Centers eg lots of
Charitable Funds donate money to host an event then you need a seperate
table to record which Funding Centres have funded that event and PayD and
EventID would be foreign key fields in that table.

If Funding Centre records how a particular employee pays in installments for
an Event then your Funding Centre table would have

PayID (primary key)
AttEventID (foreign Key field from TblAttendEvent - because this table
concerns that employee attending that event)
PayDate
PayAmount (amount paid on that date)
and, (although it isn't strictly kosher to do this because it could be
calculated by adding up the payments in TblFundingCentre and comparing this
to the amount required in TblEvent)
PaidUp (a yes/no field) for when the employee has paid all he owes for that
Event

So you can see how important meaning is to design.


Suggestion - before you build any more forms etc, *temporarily* switch on
the AutoTrack name changes in the Database Options and remove the spaces out
of your field names - it will make future coding much easier because you
won't have to type brackets aroung each name. Also make some of your field
names shorter. It's best if your field name is not loads longer than data
you will put into your field (unless you have a very wide monitor). With a
datasheet form, if you slide your columns together to make room on your
screen, Employee Number and Employee ID will look uncomfortably alike.
Employee Number, for example, (unless you use very long Employee Numbers),
could be EmpNo. In Table Design in the Description column you will Type
Employee Number and any other description about that field so that anyone
clicking in the field will see its description in the Status bar. In reports
and forms you can use always use the full name in the labels

Good to see that you have avoided using symbols like # in the field name.

Final tip, don't let Access persuade you to use LookupFields in your tables.
They will make future problems hard to spot as you will see if you search
for lookup Fields in the newsgroups. Input your data into a proper form and
insert combo boxes when you need them

Evi

 

[Brent]

First of all Evi, I would like to thank you for your great advice and help
with this db. Secondly I would like to apologize for double posting but what
happened was after I posted the first one I thought I might be in the wrong
newsgroup so I posted the second one.

I will need some time to set up the table structure you have explained but I
would like to know how do I get back to you if I have more questions (as I
know I will) as the first posting will move down the list. Can I ask for you
or do I just have to repost to all?

I’m still not clear on relationships and what I was trying to do was set up
a form with the employee’s name etc. at the top of the form and having two
subforms in this form with Funding info and another with event info, however
it did not work as Access would not let me create a subform with the events.
The form would change employees name but the subforms would not move to next
record and I don’t think the funding source was attached to the event.

I will get started and hopefully let you know of future problems and ask for
more help

 

[Evi]

Please don't address your mail specifically to me, Brent, you will be
drawing a very short straw by limiting your replies to mine when there are
some really genius people around..
I'll look out for your posts, (I let Outlook Express mark my messages as
Watched) so I should be able to spot it.

Leave enough in the message so that anyone new to the thread could take up
the baton with new suggestions.

You *can* link a subform to another subform so long as you have a proper
table structure with Primary Keys for each form.- the wizard can go and suck
his toes!

As a broad outline (give me form and field names and we can make it more
exact)
You open the Main form that contains both subforms, in Design View
You use the Properties button while clicked on the second subform
(Subform2) and, on the Data tab, next to

Link Master Field
you would type
[Subform1].Form![PrimaryKeyFieldOfSubform1]

(replacing the names in brackets with real names of course)
Next to Link Child Field you would type

[PrimaryKeyFieldOfSubform1]

which is the Foreign Key field (ie a number field in your table which is
linked from a primary key in another table) in your Subform2

Now you would open Subform1 in Design view.
Click on Properties and click on Events
Next to OnCurrent, you would open a code page and type

Private Sub Form_Current()
Me.Parent.Subform2.Requery
'this bit just means that subform 2 will
'recheck what data is available to it
End Sub

The main difficulty you will have is ensuring that your subform has the same
name as it has in the database Window. So you would click on each subform,
in the MainForm's design view, Click on Properties 'Other' tab and where it
says Name, if it is different, type the name which you can see in the
Database window

I usually call mine

MyMainFormsNameSub1
MyMainFormsNameSub2

Evi

 

[Brent]

Does this db structure look like it will work for my db to track people that
go to conferences? Do these relationships look like they are correct? One
more question, the functional centre has an 11-12 diget number and if I use
the number assignment in the table design view I can only get 10 digits in.
Any suggestions?

The employee goes to an event funded from one functional centre account the
other source is text. Does this set up the Foreign Key Field automatically?
can I set up the employee form and add my subforms now?


Thanks to everyone that can help

AttEventID EventID EmpID PayID RegCostTravelCostAccomCostTravelDate
ReturnDate

EmpID EmpNumber EmpFirstName EmpLastName


EventID ConfName Venue Presenting RefundDate

PayID Account Functional Centre ORG Other Funding Source

Relationships

TblEmp – EmpID to TblAttendevent EmpID – One to many enforced

TblEvent – EventID to TblAttendevent EventID - One to many enforced

TblFunctional Centre – PayID to TblAttendevent - One to many enforced

AttEventID – Primary key Auto Number

EmpID – Primary key Auto Number

EventID – Primary key Auto Number

PayID – Primary key Auto Number



--
Thanks Brent

Please don't address your mail specifically to me, Brent, you will be
drawing a very short straw by limiting your replies to mine when there are
some really genius people around..
I'll look out for your posts, (I let Outlook Express mark my messages as
Watched) so I should be able to spot it.

Leave enough in the message so that anyone new to the thread could take up
the baton with new suggestions.

You *can* link a subform to another subform so long as you have a proper
table structure with Primary Keys for each form.- the wizard can go and suck
his toes!

As a broad outline (give me form and field names and we can make it more
exact)
You open the Main form that contains both subforms, in Design View
You use the Properties button while clicked on the second subform
(Subform2) and, on the Data tab, next to

Link Master Field
you would type
[Subform1].Form![PrimaryKeyFieldOfSubform1]

(replacing the names in brackets with real names of course)
Next to Link Child Field you would type

[PrimaryKeyFieldOfSubform1]

which is the Foreign Key field (ie a number field in your table which is
linked from a primary key in another table) in your Subform2

Now you would open Subform1 in Design view.
Click on Properties and click on Events
Next to OnCurrent, you would open a code page and type

Private Sub Form_Current()
Me.Parent.Subform2.Requery
'this bit just means that subform 2 will
'recheck what data is available to it
End Sub

The main difficulty you will have is ensuring that your subform has the same
name as it has in the database Window. So you would click on each subform,
in the MainForm's design view, Click on Properties 'Other' tab and where it
says Name, if it is different, type the name which you can see in the
Database window

I usually call mine

MyMainFormsNameSub1
MyMainFormsNameSub2

Evi

 

[Evi]

Your structure looks fine so long as it is the employee that is being funded
to attend the event and not the whole event.
What do you mean by the Number Assignment? a Long Integer field can record a
12 digit number without difficulty otherwise how would our Bill enter his
salary?

If you want to format a number so that it always has 12 digits - with
leading 0s if necessary, then next to Format in the Table's design type

000000000000

You can now create forms to display/enter your data in various ways.

Use the 'One' tables as your main form (tblEvent or TblEmployee) and drag
the linking table (tblAttendEvent) onto them as the subform so you can
either have a list of Employees who attended an event or a list of Events
attended by each employee.

The wizard should kick in to form the relationship but if he is feeling lazy
you can create the link yourself in Properties on the Data tab by typing the
linking key next to where it says

Link Master Field
and
Link Child Field

eg if your main form was from TblEvent you would type

EventID next to both the Master and Child links

The Foreign Key fields in the Subform which arentt linked to the main form
will be filled via combo boxes.

For combo boxes, it is usually best to create a query so for your Employees
you would create a query from TblEmployee. The first field would be EmpID,
the next one would say

Employee: EmpLastName & (", " + [EmpFirstName])
or

([EmpFirstName] + " ") & [EmpLastName]

so that your combo contains both first and last name for display purposes

I usually call my combo queries

QrycboEmployee etc

When you add the combo to your subform, the wizard should kick in to let you
choose your query. Add EmpID and Employee to the query. Drag the EmpID field
closed in the Wizard so that it is hidden when you use your combo.

Evi

Does this db structure look like it will work for my db to track people that
go to conferences? Do these relationships look like they are correct? One
more question, the functional centre has an 11-12 diget number and if I use
the number assignment in the table design view I can only get 10 digits in.
Any suggestions?


The employee goes to an event funded from one functional centre account the
other source is text. Does this set up the Foreign Key Field automatically?
can I set up the employee form and add my subforms now?


Thanks to everyone that can help

AttEventID EventID EmpID PayID RegCostTravelCostAccomCostTravelDate
ReturnDate

EmpID EmpNumber EmpFirstName EmpLastName


EventID ConfName Venue Presenting RefundDate

PayID Account Functional Centre ORG Other Funding Source

Relationships

TblEmp - EmpID to TblAttendevent EmpID - One to many enforced

TblEvent - EventID to TblAttendevent EventID - One to many enforced

TblFunctional Centre - PayID to TblAttendevent - One to many enforced

AttEventID - Primary key Auto Number

EmpID - Primary key Auto Number

EventID - Primary key Auto Number

PayID - Primary key Auto Number



--
Thanks Brent

Please don't address your mail specifically to me, Brent, you will be
drawing a very short straw by limiting your replies to mine when there are
some really genius people around..
I'll look out for your posts, (I let Outlook Express mark my messages as
Watched) so I should be able to spot it.

Leave enough in the message so that anyone new to the thread could take up
the baton with new suggestions.

You *can* link a subform to another subform so long as you have a proper
table structure with Primary Keys for each form.- the wizard can go and suck
his toes!

As a broad outline (give me form and field names and we can make it more
exact)
You open the Main form that contains both subforms, in Design View
You use the Properties button while clicked on the second subform
(Subform2) and, on the Data tab, next to

Link Master Field
you would type
[Subform1].Form![PrimaryKeyFieldOfSubform1]

(replacing the names in brackets with real names of course)
Next to Link Child Field you would type

[PrimaryKeyFieldOfSubform1]

which is the Foreign Key field (ie a number field in your table which is
linked from a primary key in another table) in your Subform2

Now you would open Subform1 in Design view.
Click on Properties and click on Events
Next to OnCurrent, you would open a code page and type

Private Sub Form_Current()
Me.Parent.Subform2.Requery
'this bit just means that subform 2 will
'recheck what data is available to it
End Sub

The main difficulty you will have is ensuring that your subform has the same
name as it has in the database Window. So you would click on each subform,
in the MainForm's design view, Click on Properties 'Other' tab and where it
says Name, if it is different, type the name which you can see in the
Database window

I usually call mine

MyMainFormsNameSub1
MyMainFormsNameSub2

Evi

I will need some time to set up the table structure you have explained

but
I

would like to know how do I get back to you if I have more questions (as I
know I will) as the first posting will move down the list. Can I ask

for
you

or do I just have to repost to all?

I'm still not clear on relationships and what I was trying to do was

set
up

a form with the employee's name etc. at the top of the form and having two
subforms in this form with Funding info and another with event info, however
it did not work as Access would not let me create a subform with the events.
The form would change employees name but the subforms would not move

to
next

record and I don't think the funding source was attached to the event.

I will get started and hopefully let you know of future problems and

ask
for

more help



--
Thanks Brent


:

Hi Brent
It's a promising start (and you've explained most of it very

clearly)
but

I'm not sure about having EmployeeID in both Funding Centre and EventID.

I'll explain.


The sort of structure you might expect to see is

TblEmployee
EmpID
Employee details (nothing to do with the event)

TblEvent
EventID
ConfName
Venue
EventDate
EventCost (if each Employee pays the same amount - otherwise this

will
go in

TblAttendEvent.
This table should have nothing to do with individual Employees - these
details will be the ones which are the same for anyone who attends

the
event

so that you don't have to type them out for each Employee
The grey areas are things like RegFee (is that the same for everyone? if
yes, it goes here, otherwise it goes in TblAttendEvent)

TblAttendEvent
AttEventID (primary Key)
EventID (linked from TblEvent - Foreign Key Field'
EmpID (linked from TblEmployee
AmountPaid (what the employee paid to attend the event)




TravelDate and ReturnedDatewould go into TblEvent if all Employees

will
be

travelling on the same day
Or it could go into TblAttendEvent if Employees might travel on Different
Days

The signal for where to put things is, "will I need to type that

same
data

for each record in this table?" If yes, then it should be in another table -
by Data I mean anything other than the foreign key field

Now for the grey area because I don't know what you mean by Funding Centre.

If a Funding Centre concerns how Events are funded, eg a Charitable Fund
donates money so that you can host this Event
AND if each Event is only funded by 1 Funding Centre
then
PayID will be the Foreign Key field in TblEvent

If a Funding Centre is how that particular Employee is Funded for

that
event

and Each employee is only ever funded per Event by one funding

centre
then

PayID would be the Foreign Key field in TblAttendEvent.

If a Funding Centre funds an individual Employee to attend any event and
each Employee is only funded by one Funding Centre then PayID would

go
into

TblEmployee

If an Event or 'Employee at an Event' or an individual employee can be
funded by more than one Funding Centre then the situation changes again.

So if an Event could be hosted by several Funding Centers eg lots of
Charitable Funds donate money to host an event then you need a seperate
table to record which Funding Centres have funded that event and

PayD
and

EventID would be foreign key fields in that table.

If Funding Centre records how a particular employee pays in

installments
for

an Event then your Funding Centre table would have

PayID (primary key)
AttEventID (foreign Key field from TblAttendEvent - because this table
concerns that employee attending that event)
PayDate
PayAmount (amount paid on that date)
and, (although it isn't strictly kosher to do this because it could be
calculated by adding up the payments in TblFundingCentre and

comparing
this

to the amount required in TblEvent)
PaidUp (a yes/no field) for when the employee has paid all he owes

for
that

Event

So you can see how important meaning is to design.


Suggestion - before you build any more forms etc, *temporarily*

switch
on

the AutoTrack name changes in the Database Options and remove the

spaces
out

of your field names - it will make future coding much easier because you
won't have to type brackets aroung each name. Also make some of your field
names shorter. It's best if your field name is not loads longer

than
data

you will put into your field (unless you have a very wide monitor).

With
a

datasheet form, if you slide your columns together to make room on your
screen, Employee Number and Employee ID will look uncomfortably alike.
Employee Number, for example, (unless you use very long Employee Numbers),
could be EmpNo. In Table Design in the Description column you will Type
Employee Number and any other description about that field so that anyone
clicking in the field will see its description in the Status bar. In reports
and forms you can use always use the full name in the labels

Good to see that you have avoided using symbols like # in the field name.

Final tip, don't let Access persuade you to use LookupFields in your tables.
They will make future problems hard to spot as you will see if you search
for lookup Fields in the newsgroups. Input your data into a proper

form
and

insert combo boxes when you need them

Evi




I am trying to develop a db to track people that go to

conferences,
where,

when, how much etc. I have three tables, one called employees, one called
events and one called funding centres. I have tried to form relationships
between tables and I think this is where the problems start. In the
employee
table I have employee ID, first name, last name and employee

number.
In

the
events table I have event ID, conf name, location, date of travel, date
returned, reg costs, travel costs, accommodation costs, refund deadline
and
employee ID. In the funding center table I have payment ID, accounts,
functional centre, other funding sources and employee ID.
I have a one to many relationship from the employee table employee

ID
to

funding centre employee ID. I have a one to many from the employee

ID
in

the
employee table to the events table employee ID.
First of all can someone tell me if these relationships are OK or are
there
other fields I need to have for this to work? I set up a form with the
employee fields on top and a subform with an event and a funding subform
but
it does not work.
Sorry for the length of the note but I wanted people to have the whole
picture. Any help would be appreciated.

Thanks Brent

 

[John W. Vinson]

Your structure looks fine so long as it is the employee that is being funded
to attend the event and not the whole event.
What do you mean by the Number Assignment? a Long Integer field can record a
12 digit number without difficulty otherwise how would our Bill enter his
salary?

Actually it cannot. A currency can, but a Long Integer must be between
-2147483648 and 2147483647 - 10 digits, and not all 10 digit numbers at that.

A 12-character identifier should certainly be stored in a Text field, not in
any kind of Number.

 

[Brent]

Thanks for all your help and sharing your knowledge so generously. I am very
interested in working with Access and hope to learn more about the program in
the future. I have purchased a book for beginners but was really hung up with
one table and then the relationships.
I will put the remainder of the db together a see how everything works.

--
Thanks Brent

Your structure looks fine so long as it is the employee that is being funded
to attend the event and not the whole event.
What do you mean by the Number Assignment? a Long Integer field can record a
12 digit number without difficulty otherwise how would our Bill enter his
salary?

If you want to format a number so that it always has 12 digits - with
leading 0s if necessary, then next to Format in the Table's design type

000000000000

You can now create forms to display/enter your data in various ways.

Use the 'One' tables as your main form (tblEvent or TblEmployee) and drag
the linking table (tblAttendEvent) onto them as the subform so you can
either have a list of Employees who attended an event or a list of Events
attended by each employee.

The wizard should kick in to form the relationship but if he is feeling lazy
you can create the link yourself in Properties on the Data tab by typing the
linking key next to where it says

Link Master Field
and
Link Child Field

eg if your main form was from TblEvent you would type

EventID next to both the Master and Child links

The Foreign Key fields in the Subform which arentt linked to the main form
will be filled via combo boxes.

For combo boxes, it is usually best to create a query so for your Employees
you would create a query from TblEmployee. The first field would be EmpID,
the next one would say

Employee: EmpLastName & (", " + [EmpFirstName])
or

([EmpFirstName] + " ") & [EmpLastName]

so that your combo contains both first and last name for display purposes

I usually call my combo queries

QrycboEmployee etc

When you add the combo to your subform, the wizard should kick in to let you
choose your query. Add EmpID and Employee to the query. Drag the EmpID field
closed in the Wizard so that it is hidden when you use your combo.

Evi

Does this db structure look like it will work for my db to track people that
go to conferences? Do these relationships look like they are correct? One
more question, the functional centre has an 11-12 diget number and if I use
the number assignment in the table design view I can only get 10 digits in.
Any suggestions?


The employee goes to an event funded from one functional centre account the
other source is text. Does this set up the Foreign Key Field automatically?
can I set up the employee form and add my subforms now?


Thanks to everyone that can help

AttEventID EventID EmpID PayID RegCostTravelCostAccomCostTravelDate
ReturnDate

EmpID EmpNumber EmpFirstName EmpLastName


EventID ConfName Venue Presenting RefundDate

PayID Account Functional Centre ORG Other Funding Source

Relationships

TblEmp - EmpID to TblAttendevent EmpID - One to many enforced

TblEvent - EventID to TblAttendevent EventID - One to many enforced

TblFunctional Centre - PayID to TblAttendevent - One to many enforced

AttEventID - Primary key Auto Number

EmpID - Primary key Auto Number

EventID - Primary key Auto Number

PayID - Primary key Auto Number



--
Thanks Brent

Please don't address your mail specifically to me, Brent, you will be
drawing a very short straw by limiting your replies to mine when there are
some really genius people around..
I'll look out for your posts, (I let Outlook Express mark my messages as
Watched) so I should be able to spot it.

Leave enough in the message so that anyone new to the thread could take up
the baton with new suggestions.

You *can* link a subform to another subform so long as you have a proper
table structure with Primary Keys for each form.- the wizard can go and suck
his toes!

As a broad outline (give me form and field names and we can make it more
exact)
You open the Main form that contains both subforms, in Design View
You use the Properties button while clicked on the second subform
(Subform2) and, on the Data tab, next to

Link Master Field
you would type
[Subform1].Form![PrimaryKeyFieldOfSubform1]

(replacing the names in brackets with real names of course)
Next to Link Child Field you would type

[PrimaryKeyFieldOfSubform1]

which is the Foreign Key field (ie a number field in your table which is
linked from a primary key in another table) in your Subform2

Now you would open Subform1 in Design view.
Click on Properties and click on Events
Next to OnCurrent, you would open a code page and type

Private Sub Form_Current()
Me.Parent.Subform2.Requery
'this bit just means that subform 2 will
'recheck what data is available to it
End Sub

The main difficulty you will have is ensuring that your subform has the same
name as it has in the database Window. So you would click on each subform,
in the MainForm's design view, Click on Properties 'Other' tab and where it
says Name, if it is different, type the name which you can see in the
Database window

I usually call mine

MyMainFormsNameSub1
MyMainFormsNameSub2

Evi







"Brent" <[email protected]>
I will need some time to set up the table structure you have explained but
I
would like to know how do I get back to you if I have more questions (as I
know I will) as the first posting will move down the list. Can I ask for
you
or do I just have to repost to all?

I'm still not clear on relationships and what I was trying to do was set
up
a form with the employee's name etc. at the top of the form and having two
subforms in this form with Funding info and another with event info,
however
it did not work as Access would not let me create a subform with the
events.
The form would change employees name but the subforms would not move to
next
record and I don't think the funding source was attached to the event.

I will get started and hopefully let you know of future problems and ask
for
more help



--
Thanks Brent


:

Hi Brent
It's a promising start (and you've explained most of it very clearly)
but
I'm not sure about having EmployeeID in both Funding Centre and EventID.

I'll explain.


The sort of structure you might expect to see is

TblEmployee
EmpID
Employee details (nothing to do with the event)

TblEvent
EventID
ConfName
Venue
EventDate
EventCost (if each Employee pays the same amount - otherwise this will
go in
TblAttendEvent.
This table should have nothing to do with individual Employees - these
details will be the ones which are the same for anyone who attends the
event
so that you don't have to type them out for each Employee
The grey areas are things like RegFee (is that the same for everyone? if
yes, it goes here, otherwise it goes in TblAttendEvent)

TblAttendEvent
AttEventID (primary Key)
EventID (linked from TblEvent - Foreign Key Field'
EmpID (linked from TblEmployee
AmountPaid (what the employee paid to attend the event)




TravelDate and ReturnedDatewould go into TblEvent if all Employees will
be
travelling on the same day
Or it could go into TblAttendEvent if Employees might travel on
Different
Days

The signal for where to put things is, "will I need to type that same
data
for each record in this table?" If yes, then it should be in another
table -
by Data I mean anything other than the foreign key field

Now for the grey area because I don't know what you mean by Funding
Centre.

If a Funding Centre concerns how Events are funded, eg a Charitable Fund
donates money so that you can host this Event
AND if each Event is only funded by 1 Funding Centre
then
PayID will be the Foreign Key field in TblEvent

If a Funding Centre is how that particular Employee is Funded for that
event
and Each employee is only ever funded per Event by one funding centre
then
PayID would be the Foreign Key field in TblAttendEvent.

If a Funding Centre funds an individual Employee to attend any event and
each Employee is only funded by one Funding Centre then PayID would

 

[Brent]

Thanks John, using text was the only way I could get it to work but thought
it may cause problems in the future and should be a number.

What about a number that starts with a 0 ie. 01 is there a way to stop the
program from dropping the 0 when you move off of the field?

 

[Evi]

In Acc2000 at least, If you choose a currency datatype in your table, you
can still format it as a 12 digit number in your query's properties using
the 000000000000 (although you can't do this in Table Design). A form or
report based on this query will also show a 12 digit number, although Acc2k
will demand that you use the Report Wizard rather than Autoreport so as to
avoid the 'Subscript out of range' error

Evi

 

[John W. Vinson]

Thanks John, using text was the only way I could get it to work but thought
it may cause problems in the future and should be a number.

What about a number that starts with a 0 ie. 01 is there a way to stop the
program from dropping the 0 when you move off of the field?

It's no problem at all with a text field. With a number field you have to be
very careful with the Format property (or use the Format function) - the
number values 1 and 01 and 000000000001 are all *exactly the same number* in a
Number field, and Access makes no distinction. With a text field, the text
strings "01" and "1" are simply different text strings, just as the text
strings "ab" and "b" are different text strings.

 

[Brent]

This started as a table design problem so I hope I am posting to the correct
newsgroup

I am still having trouble with my subforms. I have set up the main form
based on the employee. Subform form 1 is my TblFundingCentre with a Link
Master Field EmpID with a Link Child Field PayID. Problem here is Tab Order,
I have checked the Tab Order in design view and it looks OK but tab goes to
the last field on the subform.
I have the same problem with subform# 2 TblAttendEvent Link Master Field
EmpID with a Link Child Field EmpID. When I created subform # 2 I had to add
fields from TblEvent as I need that info in the record.
I had a suggesting last week that I could put a combo field using a query on
my sub form but I could not get that to work as the wizard said I could not
use the query and try another table or Query.
I may want to add that at the end, I need to get reports with what employees
name, the conf they attended and what funding center paid for it. Also how
much of each funding centre has been spent per employee and total spent.


Any help would be appreciated

--
Brent

Thanks for all your help and sharing your knowledge so generously. I am very
interested in working with Access and hope to learn more about the program in
the future. I have purchased a book for beginners but was really hung up with
one table and then the relationships.
I will put the remainder of the db together a see how everything works.

--
Thanks Brent

Your structure looks fine so long as it is the employee that is being funded
to attend the event and not the whole event.
What do you mean by the Number Assignment? a Long Integer field can record a
12 digit number without difficulty otherwise how would our Bill enter his
salary?

If you want to format a number so that it always has 12 digits - with
leading 0s if necessary, then next to Format in the Table's design type

000000000000

You can now create forms to display/enter your data in various ways.

Use the 'One' tables as your main form (tblEvent or TblEmployee) and drag
the linking table (tblAttendEvent) onto them as the subform so you can
either have a list of Employees who attended an event or a list of Events
attended by each employee.

The wizard should kick in to form the relationship but if he is feeling lazy
you can create the link yourself in Properties on the Data tab by typing the
linking key next to where it says

Link Master Field
and
Link Child Field

eg if your main form was from TblEvent you would type

EventID next to both the Master and Child links

The Foreign Key fields in the Subform which arentt linked to the main form
will be filled via combo boxes.

For combo boxes, it is usually best to create a query so for your Employees
you would create a query from TblEmployee. The first field would be EmpID,
the next one would say

Employee: EmpLastName & (", " + [EmpFirstName])
or

([EmpFirstName] + " ") & [EmpLastName]

so that your combo contains both first and last name for display purposes

I usually call my combo queries

QrycboEmployee etc

When you add the combo to your subform, the wizard should kick in to let you
choose your query. Add EmpID and Employee to the query. Drag the EmpID field
closed in the Wizard so that it is hidden when you use your combo.

Evi

Does this db structure look like it will work for my db to track people that
go to conferences? Do these relationships look like they are correct? One
more question, the functional centre has an 11-12 diget number and if I use
the number assignment in the table design view I can only get 10 digits in.
Any suggestions?


The employee goes to an event funded from one functional centre account the
other source is text. Does this set up the Foreign Key Field automatically?
can I set up the employee form and add my subforms now?


Thanks to everyone that can help

AttEventID EventID EmpID PayID RegCostTravelCostAccomCostTravelDate
ReturnDate

EmpID EmpNumber EmpFirstName EmpLastName


EventID ConfName Venue Presenting RefundDate

PayID Account Functional Centre ORG Other Funding Source

Relationships

TblEmp - EmpID to TblAttendevent EmpID - One to many enforced

TblEvent - EventID to TblAttendevent EventID - One to many enforced

TblFunctional Centre - PayID to TblAttendevent - One to many enforced

AttEventID - Primary key Auto Number

EmpID - Primary key Auto Number

EventID - Primary key Auto Number

PayID - Primary key Auto Number



--
Thanks Brent


:

Please don't address your mail specifically to me, Brent, you will be
drawing a very short straw by limiting your replies to mine when there are
some really genius people around..
I'll look out for your posts, (I let Outlook Express mark my messages as
Watched) so I should be able to spot it.

Leave enough in the message so that anyone new to the thread could take up
the baton with new suggestions.

You *can* link a subform to another subform so long as you have a proper
table structure with Primary Keys for each form.- the wizard can go and suck
his toes!

As a broad outline (give me form and field names and we can make it more
exact)
You open the Main form that contains both subforms, in Design View
You use the Properties button while clicked on the second subform
(Subform2) and, on the Data tab, next to

Link Master Field
you would type
[Subform1].Form![PrimaryKeyFieldOfSubform1]

(replacing the names in brackets with real names of course)
Next to Link Child Field you would type

[PrimaryKeyFieldOfSubform1]

which is the Foreign Key field (ie a number field in your table which is
linked from a primary key in another table) in your Subform2

Now you would open Subform1 in Design view.
Click on Properties and click on Events
Next to OnCurrent, you would open a code page and type

Private Sub Form_Current()
Me.Parent.Subform2.Requery
'this bit just means that subform 2 will
'recheck what data is available to it
End Sub

The main difficulty you will have is ensuring that your subform has the same
name as it has in the database Window. So you would click on each subform,
in the MainForm's design view, Click on Properties 'Other' tab and where it
says Name, if it is different, type the name which you can see in the
Database window

I usually call mine

MyMainFormsNameSub1
MyMainFormsNameSub2

Evi







"Brent" <[email protected]>
I will need some time to set up the table structure you have explained but
I
would like to know how do I get back to you if I have more questions (as I
know I will) as the first posting will move down the list. Can I ask for
you
or do I just have to repost to all?

I'm still not clear on relationships and what I was trying to do was set
up
a form with the employee's name etc. at the top of the form and having two
subforms in this form with Funding info and another with event info,
however
it did not work as Access would not let me create a subform with the
events.
The form would change employees name but the subforms would not move to
next
record and I don't think the funding source was attached to the event.

I will get started and hopefully let you know of future problems and ask
for
more help



--
Thanks Brent


:

Hi Brent
It's a promising start (and you've explained most of it very clearly)
but
I'm not sure about having EmployeeID in both Funding Centre and EventID.

I'll explain.


The sort of structure you might expect to see is

TblEmployee
EmpID
Employee details (nothing to do with the event)

TblEvent
EventID
ConfName
Venue
EventDate
EventCost (if each Employee pays the same amount - otherwise this will
go in
TblAttendEvent.
This table should have nothing to do with individual Employees - these
details will be the ones which are the same for anyone who attends the
event
so that you don't have to type them out for each Employee
The grey areas are things like RegFee (is that the same for everyone? if
yes, it goes here, otherwise it goes in TblAttendEvent)

TblAttendEvent
AttEventID (primary Key)
EventID (linked from TblEvent - Foreign Key Field'
EmpID (linked from TblEmployee
AmountPaid (what the employee paid to attend the event)




TravelDate and ReturnedDatewould go into TblEvent if all Employees will
be
travelling on the same day
Or it could go into TblAttendEvent if Employees might travel on
Different
Days

The signal for where to put things is, "will I need to type that same
data
for each record in this table?" If yes, then it should be in another
table -
by Data I mean anything other than the foreign key field

Now for the grey area because I don't know what you mean by Funding
Centre.

If a Funding Centre concerns how Events are funded, eg a Charitable Fund
donates money so that you can host this Event
AND if each Event is only funded by 1 Funding Centre
then
PayID will be the Foreign Key field in TblEvent