Self linking tabel in editabel query

[John]

Hi

I have a StaffBookings table with Date, Time, StaffID and ClientID columns
which I use to enter information via a bound form. Now I also need to have a
column that shows the count of jobs that the staff has done. My solution
would be to do a count query on StaffBookings table and link it to the
StaffBookings table itself.

Problem is that such a query makes the form un-editable as far as I
understand. How can I get round that; that form bound to StaffBookings table
is editable but also has the count?

Thanks

Regards

 

[Jeff Boyce]

John

You've described a "how", but I'm not clear on your "what" yet.

You have a form that you use for data entry related to Staff Bookings,
right?

Are you looking for a way to see how many bookings each staff person has?

If so, and if your table structure is well-normalized, you could use a main
form/subform construction, in which each staff person (shown one-at-a-time
on main form) would have a list of bookings showing in the subform. You
could use this approach to add/edit bookings and to edit info on the staff
person.

If your data structure isn't well-normalized, you could probably still get a
count of the number of bookings for a staff person by using one of the
database functions, like DCount() (check Access HELP for the syntax on this
function).

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[James A. Fortune]

Use the DCount function to count the rows per StaffID value.  Unlike using a
subquery or joining the table to a query which uses the COUNT operator the
query will be updatable:

SELECT *,
DCount("*", "StaffBookings","StaffID = " & [StaffID]) AS JobCount
FROM StaffBookings;

This assumes StaffID is a number data type.  If it’s a text data typeamend
it to:

DCount("*", "StaffBookings","StaffID = """ & [StaffID] & """")

Ken Sheridan
Stafford, England

Nice answer.

James A. Fortune
(e-mail address removed)