Re: Still New to Access DB


Evi
I hope you can see my responses as I tried to make it as clear as I can. I
started a new db and removed all the spaces as you suggested and used your
naming conventions. I tried to edit your post to help. All is as you have it
other than my notes in brackets.
eg
TblEmployee
EmpID (PK Autonumber)
EmpFirstName
EmpLastName
EmpNumber (a company number assigned each employee)

TblFunctionalCentre
PayID (PK
Account
Functional Centre
Org
Other Funding Source (is this meant to be a field in your table? what is
this about? (A text field for notes if another funding source will pay a
portion of the costs. Another program looks after this part of the funding if
it applies)

TblEvent
EventID (PK)
ConfName
Venue
Presenting
RefundDate (date by which refunds must be claimed)
(but no start and enddate for the event???) This table is correct. The start
and end dates are in the TblAttendEvent table.( This is true) (I just found
out I need a confStartDate and a ConfEndDate added, this was a request from
another user. Where will these fields go?)


TblAttendEvent
AtEventID (PK)
EmpID (Linked from TblEmployee (Yes)
FundID (linked from TblFundingCenter (I called this field PayedID)(Yes)
EventID (Linked from TblEvent) (Yes)
RegCost (how much it cost that employee to register for the event)
TravelCost (How much it cost the employee to travel to/from the event)
AccomCost (How much the employee had to pay for accomodation
TravelDate (I have this field) (date employee traveled to event)
ReturnDate (the date the employee returned from the event
DateBooked date Employee was booked to attend the event (I need this field)
DateAttended (date Employee attended the event - or else just use a yes/no
field) (I need the Date)
FundAmount (because you said that the centre funds an individual employee
rather than giving a lump sum to be divided among several Employees (I don't
have this field anywhere in the db and yes the centre funds an individual)

However, when you say that you need to know

'How much of each funding centre has been spent per employee and total
spent.' (This is a report the Manager wants to see as there are some 30
funding centres so wants to see the breakdown and totals on reports)


A list like this, with explanations, will ensure the structure is absolutely
correct before you go on to creating a coded, linked form.


When you say you checked the Tab order, did you go to View , Tab
order? (Yes in the form design view, it worked ok for the first record then
would jump to the last field in each of the two subforms ???)

For your combo, (Lets forget this for now if it will make things easier)

Can I suggest, that until you've had a bit more experience with forms and
subforms, that you put up with using seperate forms+subforms to add your
data. So use simple forms without data to add your data to your 'one' tables
(TblEmployee, TblEvent TblFunding Centre in my example)
Then use one of these 'one' table as a main form and add the links via a
combo with your single forms ready to add extra details (Not sure how this
would work ?? but ok. I was just trying so hard not to give up on the subform
x 2 idea )

Once again thanks for taking the time to help. I started this new post as
the first one was way down the list.


--
Thanks Brent


"Evi" wrote:

Hi Brent,
Could you check if I've listed your table structure correctly. I read your
previous post where you began to do it but it got a bit confusing as to
which field was in which table or if the space between the field names meant
that there were 2 fields or 1. You could just edit the structure below

eg
TblEmployee
EmpID (PK Autonumber)
EmpFirstName
EmpLastName

TblFunctionalCentre
PayID (PK
Account
Functional Centre
Org
Other Funding Source (is this meant to be a field in your table? what is
this about?

TblEvent
EventID (PK)
ConfName
Venue
Presenting
RefundDate (date by which refunds must be claimed)
(but no start and enddate for the event???)


TblAttendEvent
AtEventID (PK)
EmpID (Linked from TblEmployee
FundID (linked from TblFundingCenter
EventID (Linked from TblEvent)
RegCost (how much it cost that employee to register for the event)
TravelCost (How much it cost the employee to travel to/from the event)
AccomCost (How much the employee had to pay for accomodation
ReturnDate (the date the employee returned from the event
DateBooked date Employee was booked to attend the event
DateAttended (date Employee attended the event - or else just use a yes/no
field)
FundAmount (because you said that the centre funds an individual employee
rather than giving a lump sum to be divided among several Employees

However, when you say that you need to know

'How much of each funding centre has been spent per employee and total
spent.'

That seems to imply that the centre gave a lump sum for the employees and it
was divided up among them/
If this is true then you may need another table to express this. You will
need to be clear (since we dont' know your business) if the funding centre
gave a lump sum for say 5 employees to divide among themselves to attend a
particular event, or if the FC pay £x for Employee 1 and £x for Employee 2
to attend a specified event. The table structures will differ.



A list like this, with explanations, will ensure the structure is absolutely
correct before you go on to creating a coded, linked form.


When you say you checked the Tab order, did you go to View , Tab
order?

For your combo, you will almost certainly have to use different query from
the one on which your
subform is based and it needs to have a Unique Primary Key field. So if
your subform held a list of groceries with the main form being the Shopping
Trip, You wouldn't base your Groceries combo on the this subform table
because each grocery could appear more than once on different shopping
trips. You would need to base it on a table containing a list of different
items available for buying with each item being only listed once.
And you should include that Primary Key field which the wizard should hide
and next, the field which you want to see in your combo

To avoid confusion, you can start off basing your combo on your 'one' table
(each item in the combo is only listed once) and when that is working, then
go into the combo's Row
Source Properties and click next to that so that you turn the table there
into a
sorted query.

Can I suggest, that until you've had a bit more experience with forms and
subforms, that you put up with using seperate forms+subforms to add your
data. So use simple forms without data to add your data to your 'one' tables
(TblEmployee, TblEvent TblFunding Centre in my example)
Then use one of these 'one' table as a main form and add the links via a
combo with your single forms ready to add extra details

Once that feels comfortable, and you can manage your combos etc then you can
think about your double form Otherwise trouble-shooting will be a nightmare.



Evi

"Brent" <Brent@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0349FE83-ACAF-41D4-9AF7-1C4E7FC7A7CC@xxxxxxxxxxxxxxxx
I have started a new post after posting in my old one. I'm not sure this
the
right way to do this as the old post has worked its way down the list and
have had no responce.

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 once all is good, 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


--
Thanks Brent





.

Relevant Pages

  • Re: New to Access db
    ... The employee goes to an event funded from one functional centre account the ... Does this set up the Foreign Key Field automatically? ... You *can* link a subform to another subform so long as you have a proper ... Now for the grey area because I don't know what you mean by Funding ...
    (microsoft.public.access.tablesdbdesign)
  • Re: New to Access db
    ... You *can* link a subform to another subform so long as you have a proper ... EventCost (if each Employee pays the same amount - otherwise this will ... EventID (linked from TblEvent - Foreign Key Field' ... Now for the grey area because I don't know what you mean by Funding ...
    (microsoft.public.access.tablesdbdesign)
  • Re: New to Access db
    ... The Foreign Key fields in the Subform which arentt linked to the main form ... Add EmpID and Employee to the query. ... AttEventID - Primary key Auto Number ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Want all fields to show when Item selected
    ... audited to be sure the employee has filled them out correctly; ... Is it on tblAudits? ... since each audit can have many details. ... selection populates a combo box on the subform. ...
    (microsoft.public.access.forms)
  • Re: Create complex data entry form
    ... Within the Employees subform is a second ... EventTopicID that ties the employee to a specific event and a specific topic ... TblEvent so you can select the event. ... Use a form/subform-subform to record event-topic attendance. ...
    (microsoft.public.access.forms)