Still New to Access DB

[Brent]

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

 

[Evi]

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

 

[Jeff Boyce]

Brent

Does that mean you aren't getting the answer you wanted, or you are getting
an answer that you don't understand?

If your question is about forms and subforms, the forms-related newsgroups
might be a more appropriate.

Your description covers a lot of "how" you are trying to do this.

Can you step back for a moment and describe the "why"? If you were to
explain what you'll be able to do (and why) to an 80-year old grandmother,
in terms she'd likely understand, how would you describe it?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Brent]

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.

 

[Evi]

Hi Brent. If you need to have a start date and end date for the event (is
that what is meant by ConfStartDate, ConfEndDate?) then it would go in the
table of events. The reason it might be necessary is if the employee doesn't
attend the whole event. It's looking fine as far as I can see.

If you are saying that Events are things that happen during a Conference,
then you would need to add another level to your database, though an easy
one. You need a TblConference whose Primary Key field will be a Foreign Key
field in TblEvent.

You say:

'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)

With the structure below, that should not be a problem. You could have a
query based mainly on TblAttendEvent. You would add (without their primary
keys) any details from the other tables which you required for the report
(so you wouldn't need the date field for instance or the AtEventID field or
even the EventID because the statistics required don't concern that but you
would want some of the Employee fields from TblEmployee and some of the the
Funding Centre fields from TblFunding centre. You could turn that into a
totals query and base a report on that grouped first by funding centre, then
by employee.

Evi

 

[Brent]

Hi Evi. Once again thank you for all your help. I have not had time to do any
more work on th db over the last few days but will get on it today. I think
everything should work now.
Do you think my main form being the Employee form and the two subforms will
be ok or should I forget it and move forward? It would be great if I could
get that to work.
That should be all for now and again thanks to all

 

[Brent]

Jeff

Thanks for taking the time to respond. I think you make a good point
regarding the "how" and describe the "why" and will try to take that aproach
next time.

 

[Evi]

Don't forget it altogether, but there must be some reason why you couldn't
get it to work, so just for now, see if it works OK to do a normal main form
and subform. If you can get that working OK, and everything works as it
ought, then you can go onto doing the double sub.
Evi

 

[Brent]

Your right Evi, I tried it again by building a new db and it still didn't
work. What main form and subform would you suggest I start with?
I would like to start with the employee form as the employee name and
employee number seems like the natural place to start. Would the AttEvent
subforms be OK?
I would need to figure out how to get the other fields on the employee form
ie. funding and event info.
Thanks for all your help

 

[Evi]

Lets see if we can pinpoint what is going wrong. If you don't have any data
in your db, add a dummy employee to TblEmployee.
Add a dummy event to TblEvent.

An AttEvent subform would list how many events that employee attended so you
could certainly start with that.

1. Click on TblEmployee then drag the (closed) TblAttEvent from the db
window onto the newly created form in Design view.
2. The wizard ought to kick in. (if it doesn't, ensure that the Wizard
button is pressed down in the Form's design view (looks like a wand)

Is this bit happening?

Evi

 

[Brent]

Yes, this is happening. After the first record the tab order on the subform
goes right to the last field but the Master and Child links on the subform
are linked to the EmpID which is right.
We will need to get the event and funding tables in on this.

Should we stay on this post or start a new one? It takes me about 1/2 hour
to get here. Is there a faster way to get onto Microsoft? This is the only
web site it takes so long to load.

 

[John W. Vinson]

Is there a faster way to get onto Microsoft? This is the only
web site it takes so long to load.

Yes. The webpage is slow, bloated, buggy... and completely unnecessary.

You can use Outlook Express or Windows Mail as a Newsreader; set the news
server (under Tools... Accounts) to msnews.microsoft.com, and post to the
newsgroup microsoft.public.access.tablesdbdesign.

 

[Evi]

Hi Brent, you need to open the Subform by itself (not the main form) in
Design view to set the Tab order.

Open the subform in Design view, add a combo based on TblEvent to it, so
that you can choose which event the employee is attending. (it needs EventID
and whatever fields you need to identify an event)
Is everything else working OK on the form - enter dummy data to your tables
if you need to. Does it change records to show only records for that
Employee, can you add more than 1 record to it.

Can you add a new employee to it?

I access this ng using Outlook Express and Newsgroups. It works pretty well.
If I want to search for a topic, then I go online.

By all means start a new thread - the more input you get, the better.
Include your db design, listing your tables, and if you have a problem,
state exactly what is happening

Evi

 

[Brent]

Evi
Yes everything else is working OK on the form. I have entered dummy entries
and it seems to work fine.

The tab order in the design view on the subform only is correct, however the
tabbing still does not work properly. It may have something to do with
tabbing from field to field on the form and it goes to the next row on the
first records subform then to the second record and then to the last field
on the subform. We can work on this issue later if you think it doesn't have
anything to do with anything else we are trying to do..

Now, when it comes to adding the combo box to the subform in design view,
the wizard will not allow me to look up the value I want in a table or
query. When I select the fields I want in the combo box it said the data
can not be retrieved from the source I have selected. I must select a
different table or query.
When I try - I will type in the value and I add the combo to the form and
type in the value I get it in 3 rows. Any thoughts???

Thanks to you and John using Outlook Express works like a charm.

Brent

 

[Evi]

Typing in the data into your combo is not the answer.
You need to know why the Wizard won't allow you to use that query or table
combo.
Does this happen when you use a table for your combo's source?

Possible reasons:
You have chosen a query which doesn't work properly
You have selected the fields without selecting the unique primary key field
You are using the wrong table as the combo's source eg you want to retrieve
a list of Employees but instead of using the Employees table where each
employee is only mentioned once per record (a 'one' table) you have chosen a
table where each employee has several records such as TblAttendEvent,
You have chosen a query rather than a table and have chosen one into which
you have put 2 Primary Keys.

I can't imagine why your tab order isn't working in your subform. Has anyone
else got any ideas on this one?

It sounds as if you are saying that it actually misses out some of the
fields before going on to the next record. Is that right?

As a long shot, open your form in Design View.
Click below the form and click Properties. On the Other tab, check that it
says All Records next to Cycle.
On the same page, check the Tab Index for each field (the first field should
be 0, the next field 1 etc.

Something doesn't add up. Is it possible that in an effort to make your
explanation of your db more userfriendly, you have missed out information
about it which may be relevant?

Evi

 

[Brent]

Hi Evi
I have made some notes below in your reply from yesterday
This is the table structure, does it look OK?
The main form is Employee with subform AttendEvent. Now I and trying to add
a combo box from TblEvent into my subform. Right? Do I need to have data in
the table event to add it as a combo box? i.e. pre load ConfNames? If so I
don't know all the ConfNames now. I have used combo boxes in the past on
forms (per loaded with data) and they worked fine.
If you areTblEmployee

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 getting tired of this just let me know as I
appreciate all you have done for me so far and it is taking up your time.

TblEmployee
EmpID (PK Autonumber)
EmpFirstName
EmpLastName
EmpNumber (a company number assigned each employee)

TblFunctionalCentre
FundingID (PK
Account
Functional Centre
Org
Other Funding Source (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)
DateBooked
DateAttended
DateReturned

TblAttendEvent
AtEventID (PK)
EmpID (Linked from TblEmployee FK)
FundID (linked from TblFundingCenter FK)
EventID (Linked from TblEvent FK)
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

 

[Michael Gramelspacher]

TblEvent
EventID (PK)
ConfName
Venue
Presenting
RefundDate (date by which refunds must be claimed)
DateBooked
DateAttended
DateReturned

Seems like this should be two tables: Events, EventsBookings.

Events
(EventID, ConfName, Venue, RegFeeAmt,RefundDate, StartDate, EndDate)

EventsBookings
(EmpID, EventID, FundID, BookedDate)

EventsAttendance
(EmpID, EventID, FundID,RegAmt, AccomAmt,TravelAmt,TravelDate,ReturnDate)

It is not clear how FunctionalCenter works. A functional center has one account
which is charged for all travel costs. All costs are lumped into one account.

 

[Brent]

Thanks Michael for taking the time to help. Are the pk and fk correct below?

It is not clear how FunctionalCenter works. A functional center has one
account which is charged for all travel costs. All costs are lumped into
one account. (Yes this is correct, then the employee is reimbursed for the
expense. However, there are some 30 functional centers from different
departments that are used. The idea is to be able to trace the employee,
what event they attended, when, where etc. then the manager wants to run
reports showing employees attended what event etc. and what functional
center will pay and how much has been charged against the center). Hope this
is not confusing.

Problem that I have been working on with Evi on is that I use the employee
form with a subform AttEvent. We want to add a combo box to the AttEvent
subform and the wizard will not allow me to add it and of course the tab
order does not work right.(don't know why).

My question is, how do I tie this data together on a form? What are the
relationships? If more info is required please let me know.

Any help is much appreciated.


Events
EventID-PK
ConfName
Venue
RegFeeAmt
RefundDate
StartDate
EndDate

EventsBookings
EmpID-FK
EventID-FK
FundID-FK
BookedDate

EventsAttendance
EmpID-FK
EventID-FK
FundID-FK
RegAmt
AccomAmt
TravelAmt
TravelDate
ReturnDate

 

[Michael Gramelspacher]

Thanks Michael for taking the time to help. Are the pk and fk correct below?

It is not clear how FunctionalCenter works. A functional center has one
account which is charged for all travel costs. All costs are lumped into
one account. (Yes this is correct, then the employee is reimbursed for the
expense. However, there are some 30 functional centers from different
departments that are used. The idea is to be able to trace the employee,
what event they attended, when, where etc. then the manager wants to run
reports showing employees attended what event etc. and what functional
center will pay and how much has been charged against the center). Hope this
is not confusing.

Problem that I have been working on with Evi on is that I use the employee
form with a subform AttEvent. We want to add a combo box to the AttEvent
subform and the wizard will not allow me to add it and of course the tab
order does not work right.(don't know why).

My question is, how do I tie this data together on a form? What are the
relationships? If more info is required please let me know.

Any help is much appreciated.


Events
EventID-PK
ConfName
Venue
RegFeeAmt
RefundDate
StartDate
EndDate

EventsBookings
EmpID-FK
EventID-FK
FundID-FK
BookedDate

EventsAttendance
EmpID-FK
EventID-FK
FundID-FK
RegAmt
AccomAmt
TravelAmt
TravelDate
ReturnDate

see http://www.psci.net/gramelsp/temp/Subform_Example.zip

look at Form1 as that is the normal way to link subforms

 

[Brent]

Michael, thank you very much for taking the time to create this wonderful
subform example, it's exactly what I've been trying to accomplish. I will
use this as an example for all my future access db projects. I'm not sure
why I couldn't get to this point but now I can use your example and work
things out. I would like once again to thank Evi, John, Jeff and you for
your time and patience helping me through this process.
I've purchased a new book "Database Design for Mere Mortals" as suggested in
a pervious post and hope that will help with some of the design issues I
have had in the past.
I will continue to visit this newsgroup and take advantage of all the great
support there is on this site.

Thanks to all

Brent