Join/Query Help

[jitsky]

I have a really simple problem, but I can't figure it out: I have a table
that lists conferences along with the associated fees. Another table lists
employees who register for different conferences. I enter the employee info
and do a lookup to the conference for the conference name. I want the fee to
automatically populate in the attendee table. How do I do this?

 

[Jeanette Cunningham]

With access, there is no need to, and neither should you, store the fee in
the attendee table.
Whenever you want to see the fee for any attendee in a form or in a report,
you grab the attendee fee by using a query.
You would join the 2 tables in the query, put the attendee name and the fee
as the fields to show when you run the query.
Use this query to show the attendance for employees in a form or a report.

If you need some advice on creating the query, please post the names of the
table, the names of the primary keys and the names for the fields about
attendees and fees.


Jeanette Cunningham

 

[Jeanette Cunningham]

ps

After I pressed the send button, I realised that the price of each
conference might change over time.
I don't know much detail about your database ...
If you could have a different fee for the same conference ID - this could
happen if the fees go up next year - then you have a valid reason for
storing the attendance fee in the attendance table with the employee's ID
and the Date of attendance.
You might also have a table for conference names and a related table for
conference fees, with conference ID, fee and FeeDate.



Jeanette Cunningham

 

[jitsky]

So what you are saying is that as long as I have the relation between the
tables from the lookup to the conference, when I add the fee to the query or
report it will populate?

One more question - I have a field in the attendee table for total cost
which is travel cost field in attendee plus the conf fee. How can I
calculate the total?

 

[jitsky]

I dont think that will be an issue. recurring conferences will be put in as
new occurences. I dont think I need to have separate table
Thanks for all your help!

 

[Jeanette Cunningham]

I suggest to have a separate field for travel cost.
You calculate the total cost using a query. Don't store the total cost.

You use the 2 tables in a query that will give both the conference fee and
the travel cost.
You add the conference fee to the travel cost in a calculated field in the
query.


Jeanette Cunningham