Prev: vvvv
Next: Inquiry box
From: John on 6 Apr 2010 17:06
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


From: Jeff Boyce on 6 Apr 2010 17:29
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.

"John" <info(a)nospam.infovis.co.uk> wrote in message
news:e%23og9zc1KHA.5996(a)TK2MSFTNGP05.phx.gbl...
> 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
>


From: KenSheridan via AccessMonster.com on 6 Apr 2010 17:48
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 type amend
it to:

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

Ken Sheridan
Stafford, England

John wrote:
>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

--
Message posted via http://www.accessmonster.com

From: James A. Fortune on 7 Apr 2010 08:46
On Apr 6, 5:48 pm, "KenSheridan via AccessMonster.com" <u51882(a)uwe>
wrote:
> 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 type amend
> it to:
>
> DCount("*", "StaffBookings","StaffID = """ & [StaffID] & """")
>
> Ken Sheridan
> Stafford, England

Nice answer.

James A. Fortune
MPAPoster(a)FortuneJames.com
 | 
Pages: 1
Prev: vvvv
Next: Inquiry box