Re: New to Access db

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


"Evi" wrote:

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







"Brent" <Brent@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E84F236B-A568-4BE4-B356-2E8E25D2CECD@xxxxxxxxxxxxxxxx
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


"Evi" wrote:

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" <Brent@xxxxxxxxxxxxxxxxxxxxxxxxx>
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


"Evi" wrote:

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
.

Loading