DCount Code Problem ?

Sorry but I am not getting the picture of your set-up. Post the following:

1. What is the purpose of the database? What is the purpose of the Form /
Subform?

1. The RecordSource of the Main Form ("TimeCards"). This is important as I
got no ideas what the Form is supposed to do and hence can't place your
DCount or your requirement correctly. Since the Subform already "uses" the
2 Tables (?) you described, I got no ideas of the data source for the main
Form.

2. The RecordSource of the Subform ("Time and Hours")

3. The LinkMasterFields and LinkChildField of the Subform Control.

4. In one of the earlier, you posted an example with 4 Records (3 on the
same date and 1 on a different date) and you mentioned "On the Form with
record selector", I guess you actually meant the Subform?

5. From the same sample data, I guess you want the count of days with 1 or 2
employees AND the count of days with more than 2 employees that *belong/ are
related* to the CurrentRecord of whatever on the main Form (RecordSource /
entity unknown - see point 1) so that you can work out the total hourly rate
applicable for each day?

6. What happens if 2 or more employees work on the same date on the same
CurrentRecord on the main Form but they work different number of hours at
different time? Let's say 3 employees but Employee1 works full day,
Employee2 work 1/2 day in the morning and Employee 3 works 1/4 day in the
afternoon (hence the nett workforce is less than 2)? How would you
calculate the hourly rate in this case?

--
HTH
Van T. Dinh
MVP (Access)



"Dave Elliott" wrote in message
.. .
Table Structu 2 tables used for form; Hours and Employees
From the Hours table these fields;HoursID-TimeID-Hours-Overtime-Work
Date-OT
Rate-Pay Rate-EmployeeID
From the Employees table; EmployeeID

TimeID links the sub-form (Time and Hours) to the main form TimeCards
EmployeeID links the 2 tables and TimeID links the sub-form to the main
form. (TimeCards) Master/Child
The sub-form gives exactly what I need, all the data is there, just cant
see
it the way i want

I need to get the number of records that have more than 2 employees on
them
and the count of the employees
I then would have to subtract the total number of records minus the ones
that have more than 2 employees on them for my calculation. I can use
another form to do this and base it on another query but dont know how to
structure it.
Remember for 1 or 2 employees the rate is 65, then after for each employee
1/2 the rate per hour
Same thing with 55 rate.

The Db is a Invoice db. it calculates the material, labor and the suggested
price to Invoice.
The main form TimeCards has as it's recordsource the query QDates using the
table Time
The sub-form named Time and Hours uses the query Time_Hours with 3 tables
Hours;Employees;TEmpOrCon
Sub-form is tied to main via master/child relationship of TimeID in the Time
table
Sub-form Time and Hours calculates the labor by Rate/Hours Total pay for the
day and grand total pay for the record
as well as overtime rate/ overtime pay...it shows the day worked by date and
also by Day, i.e. Friday
The calculations are already in place for the time / hours, this works fine.

Just need to calculate if more than 2 employees work on any given record on
the sub-form that is one record via the main form.
Example: 3 employees work on Friday, then the rate would be $65.00 an hour
for the first 2 men, then 32.50 or 1/2 the rate for every employee after
that.
Total Invoiced for hours being (4) for all employees: $390.00
So the count of DAYS (On The Same Day) where the count of EMPLOYEES
is over 3 needs to be found for the calculation to take place.
Hope this explains better.



"Van T. Dinh" wrote in message
...
Sorry but I am not getting the picture of your set-up. Post the
following:

1. What is the purpose of the database? What is the purpose of the Form
/
Subform?

1. The RecordSource of the Main Form ("TimeCards"). This is important as
I
got no ideas what the Form is supposed to do and hence can't place your
DCount or your requirement correctly. Since the Subform already "uses"
the
2 Tables (?) you described, I got no ideas of the data source for the main
Form.

2. The RecordSource of the Subform ("Time and Hours")

3. The LinkMasterFields and LinkChildField of the Subform Control.

4. In one of the earlier, you posted an example with 4 Records (3 on the
same date and 1 on a different date) and you mentioned "On the Form with
record selector", I guess you actually meant the Subform?

5. From the same sample data, I guess you want the count of days with 1 or
2
employees AND the count of days with more than 2 employees that *belong/
are
related* to the CurrentRecord of whatever on the main Form (RecordSource /
entity unknown - see point 1) so that you can work out the total hourly
rate
applicable for each day?

6. What happens if 2 or more employees work on the same date on the same
CurrentRecord on the main Form but they work different number of hours at
different time? Let's say 3 employees but Employee1 works full day,
Employee2 work 1/2 day in the morning and Employee 3 works 1/4 day in the
afternoon (hence the nett workforce is less than 2)? How would you
calculate the hourly rate in this case?

--
HTH
Van T. Dinh
MVP (Access)



"Dave Elliott" wrote in message
.. .
Table Structu 2 tables used for form; Hours and Employees
From the Hours table these fields;HoursID-TimeID-Hours-Overtime-Work
Date-OT
Rate-Pay Rate-EmployeeID
From the Employees table; EmployeeID

TimeID links the sub-form (Time and Hours) to the main form TimeCards
EmployeeID links the 2 tables and TimeID links the sub-form to the main
form. (TimeCards) Master/Child
The sub-form gives exactly what I need, all the data is there, just cant
see
it the way i want

I need to get the number of records that have more than 2 employees on
them
and the count of the employees
I then would have to subtract the total number of records minus the ones
that have more than 2 employees on them for my calculation. I can use
another form to do this and base it on another query but dont know how to
structure it.
Remember for 1 or 2 employees the rate is 65, then after for each
employee
1/2 the rate per hour
Same thing with 55 rate.

OK, much better picture in my head but please post the SQL String (of the
Query) being used as the RecordSource for the Subform [Time and Hours].

Also advise where you intend to use / place the result of the DCount().

--
HTH
Van T. Dinh
MVP (Access)


"Dave Elliott" wrote in message
...
The Db is a Invoice db. it calculates the material, labor and the
suggested
price to Invoice.
The main form TimeCards has as it's recordsource the query QDates using
the
table Time
The sub-form named Time and Hours uses the query Time_Hours with 3 tables
Hours;Employees;TEmpOrCon
Sub-form is tied to main via master/child relationship of TimeID in the
Time
table
Sub-form Time and Hours calculates the labor by Rate/Hours Total pay for
the
day and grand total pay for the record
as well as overtime rate/ overtime pay...it shows the day worked by date
and
also by Day, i.e. Friday
The calculations are already in place for the time / hours, this works
fine.

Just need to calculate if more than 2 employees work on any given record
on
the sub-form that is one record via the main form.
Example: 3 employees work on Friday, then the rate would be $65.00 an hour
for the first 2 men, then 32.50 or 1/2 the rate for every employee after
that.
Total Invoiced for hours being (4) for all employees: $390.00
So the count of DAYS (On The Same Day) where the count of EMPLOYEES
is over 3 needs to be found for the calculation to take place.
Hope this explains better.

SELECT Hours.SinMar, Hours.NoAllow, Hours.PayRate, Hours.Hours,
IIf([TEmpOrCon].[EmpOrCon]=0.2,1*[PayRate],[Hours]*[PayRate]) AS RegPay,
Hours.OTRate, Hours.Overtime, [Hours].[OTRate]*[Overtime]*[PayRate] AS
OTPay, [RegPay]+[OTPay] AS ExpPayPerDay, Hours.TimeID, Hours.HoursID,
Hours.EmployeeID, Employees.[First Name], Hours.[Work Date], Employees.[Last
Name], Hours.ChkNoID, IIf(IsNull([ChkNoID]),0,-1) AS Paid
FROM (Hours LEFT JOIN Employees ON Hours.EmployeeID = Employees.[Employee
ID]) LEFT JOIN TEmpOrCon ON Employees.EmpOrCon = TEmpOrCon.EmpOrConID
ORDER BY Hours.[Work Date] DESC , Employees.[Last Name];

Probably will have to put on either Time and Hours or footer on same and
then
The rest of the code will be on the main form TimeCards using a unbound
textbox using the values
of the Time and Hours form
I can send a small demo db if necessary.


"Van T. Dinh" wrote in message
...
OK, much better picture in my head but please post the SQL String (of the
Query) being used as the RecordSource for the Subform [Time and Hours].

Also advise where you intend to use / place the result of the DCount().

--
HTH
Van T. Dinh
MVP (Access)


"Dave Elliott" wrote in message
...
The Db is a Invoice db. it calculates the material, labor and the
suggested
price to Invoice.
The main form TimeCards has as it's recordsource the query QDates using
the
table Time
The sub-form named Time and Hours uses the query Time_Hours with 3 tables
Hours;Employees;TEmpOrCon
Sub-form is tied to main via master/child relationship of TimeID in the
Time
table
Sub-form Time and Hours calculates the labor by Rate/Hours Total pay for
the
day and grand total pay for the record
as well as overtime rate/ overtime pay...it shows the day worked by date
and
also by Day, i.e. Friday
The calculations are already in place for the time / hours, this works
fine.

Just need to calculate if more than 2 employees work on any given record
on
the sub-form that is one record via the main form.
Example: 3 employees work on Friday, then the rate would be $65.00 an
hour
for the first 2 men, then 32.50 or 1/2 the rate for every employee after
that.
Total Invoiced for hours being (4) for all employees: $390.00
So the count of DAYS (On The Same Day) where the count of
EMPLOYEES
is over 3 needs to be found for the calculation to take place.
Hope this explains better.

Compact & zip it and send to VanThienDinhAThotmail.com

--
HTH
Van T. Dinh
MVP (Access)



"Dave Elliott" wrote in message
m...
SELECT Hours.SinMar, Hours.NoAllow, Hours.PayRate, Hours.Hours,
IIf([TEmpOrCon].[EmpOrCon]=0.2,1*[PayRate],[Hours]*[PayRate]) AS RegPay,
Hours.OTRate, Hours.Overtime, [Hours].[OTRate]*[Overtime]*[PayRate] AS
OTPay, [RegPay]+[OTPay] AS ExpPayPerDay, Hours.TimeID, Hours.HoursID,
Hours.EmployeeID, Employees.[First Name], Hours.[Work Date],
Employees.[Last
Name], Hours.ChkNoID, IIf(IsNull([ChkNoID]),0,-1) AS Paid
FROM (Hours LEFT JOIN Employees ON Hours.EmployeeID = Employees.[Employee
ID]) LEFT JOIN TEmpOrCon ON Employees.EmpOrCon = TEmpOrCon.EmpOrConID
ORDER BY Hours.[Work Date] DESC , Employees.[Last Name];

Probably will have to put on either Time and Hours or footer on same and
then
The rest of the code will be on the main form TimeCards using a unbound
textbox using the values
of the Time and Hours form
I can send a small demo db if necessary.

The db is a invoice database. purpose of timecards form is to record allinfo
for a invoice, such as employee time, vendor material, payments to invoice,
employee commission. date of invoice and how many times sent and by who,
history of payments, count how many employees worked on what job, what date
and their hours, this used to calculate the expenes for the job, i.e.
timecard as well as material used so invoice price can be calculated.
Time is not calculated on this form, only days, dates. The ONLY thin I need
help with is the sub-form Time And Hours on the main form TimeCards, thats
it.
as for the rocordsource, this is easily found via the forms in design mode.
the whole timecard is one (1) record.
datasource for main form is query QDates, but this does no have anything to
do with problem at hand.
recordsource for sub-form time and hours is a query named Time_Hours.
Call me if you have any questions or else e-mail you number and I will call
you.
The database is very complex, but we are ONLY dealing with one form, the
sub-form, Time and Hours
the data is there but I need to see it in a different way.

Thanks,

Dave



"Van T. Dinh" wrote in message
...
Sorry but I am not getting the picture of your set-up. Post the
following:

1. What is the purpose of the database? What is the purpose of the Form
/
Subform?

1. The RecordSource of the Main Form ("TimeCards"). This is important as
I
got no ideas what the Form is supposed to do and hence can't place your
DCount or your requirement correctly. Since the Subform already "uses"
the
2 Tables (?) you described, I got no ideas of the data source for the main
Form.

2. The RecordSource of the Subform ("Time and Hours")

3. The LinkMasterFields and LinkChildField of the Subform Control.

4. In one of the earlier, you posted an example with 4 Records (3 on the
same date and 1 on a different date) and you mentioned "On the Form with
record selector", I guess you actually meant the Subform?

5. From the same sample data, I guess you want the count of days with 1 or
2
employees AND the count of days with more than 2 employees that *belong/
are
related* to the CurrentRecord of whatever on the main Form (RecordSource /
entity unknown - see point 1) so that you can work out the total hourly
rate
applicable for each day?

6. What happens if 2 or more employees work on the same date on the same
(IGNORE THIS)
CurrentRecord on the main Form but they work different number of hours at
different time? Let's say 3 employees but Employee1 works full day,
Employee2 work 1/2 day in the morning and Employee 3 works 1/4 day in the
afternoon (hence the nett workforce is less than 2)? How would you
calculate the hourly rate in this case?

--
HTH
Van T. Dinh
MVP (Access)



"Dave Elliott" wrote in message
.. .
Table Structu 2 tables used for form; Hours and Employees
From the Hours table these fields;HoursID-TimeID-Hours-Overtime-Work
Date-OT
Rate-Pay Rate-EmployeeID
From the Employees table; EmployeeID

TimeID links the sub-form (Time and Hours) to the main form TimeCards
EmployeeID links the 2 tables and TimeID links the sub-form to the main
form. (TimeCards) Master/Child
The sub-form gives exactly what I need, all the data is there, just cant
see
it the way i want

I need to get the number of records that have more than 2 employees on
them
and the count of the employees
I then would have to subtract the total number of records minus the ones
that have more than 2 employees on them for my calculation. I can use
another form to do this and base it on another query but dont know how to
structure it.
Remember for 1 or 2 employees the rate is 65, then after for each
employee
1/2 the rate per hour
Same thing with 55 rate.

Dave

I got you message enquiring whether I got your file (which I didn't) and
when I replied, the e-mail bounced back undeliverable.

--
HTH
Van T. Dinh
MVP (Access)




"Dave Elliott" wrote in message
m...
The db is a invoice database. purpose of timecards form is to record
allinfo
for a invoice, such as employee time, vendor material, payments to
invoice,
employee commission. date of invoice and how many times sent and by who,
history of payments, count how many employees worked on what job, what
date
and their hours, this used to calculate the expenes for the job, i.e.
timecard as well as material used so invoice price can be calculated.
Time is not calculated on this form, only days, dates. The ONLY thin I
need
help with is the sub-form Time And Hours on the main form TimeCards, thats
it.
as for the rocordsource, this is easily found via the forms in design
mode.
the whole timecard is one (1) record.
datasource for main form is query QDates, but this does no have anything
to
do with problem at hand.
recordsource for sub-form time and hours is a query named Time_Hours.
Call me if you have any questions or else e-mail you number and I will
call
you.
The database is very complex, but we are ONLY dealing with one form, the
sub-form, Time and Hours
the data is there but I need to see it in a different way.

Thanks,

Dave



"Van T. Dinh" wrote in message
...
Sorry but I am not getting the picture of your set-up. Post the
following:

1. What is the purpose of the database? What is the purpose of the
Form
/
Subform?

1. The RecordSource of the Main Form ("TimeCards"). This is important
as
I
got no ideas what the Form is supposed to do and hence can't place your
DCount or your requirement correctly. Since the Subform already "uses"
the
2 Tables (?) you described, I got no ideas of the data source for the
main
Form.

2. The RecordSource of the Subform ("Time and Hours")

3. The LinkMasterFields and LinkChildField of the Subform Control.

4. In one of the earlier, you posted an example with 4 Records (3 on
the
same date and 1 on a different date) and you mentioned "On the Form with
record selector", I guess you actually meant the Subform?

5. From the same sample data, I guess you want the count of days with 1
or
2
employees AND the count of days with more than 2 employees that *belong/
are
related* to the CurrentRecord of whatever on the main Form (RecordSource
/
entity unknown - see point 1) so that you can work out the total hourly
rate
applicable for each day?

6. What happens if 2 or more employees work on the same date on the same
(IGNORE THIS)
CurrentRecord on the main Form but they work different number of hours
at
different time? Let's say 3 employees but Employee1 works full day,
Employee2 work 1/2 day in the morning and Employee 3 works 1/4 day in
the
afternoon (hence the nett workforce is less than 2)? How would you
calculate the hourly rate in this case?

--
HTH
Van T. Dinh
MVP (Access)



"Dave Elliott" wrote in message
.. .
Table Structu 2 tables used for form; Hours and Employees
From the Hours table these fields;HoursID-TimeID-Hours-Overtime-Work
Date-OT
Rate-Pay Rate-EmployeeID
From the Employees table; EmployeeID

TimeID links the sub-form (Time and Hours) to the main form TimeCards
EmployeeID links the 2 tables and TimeID links the sub-form to the main
form. (TimeCards) Master/Child
The sub-form gives exactly what I need, all the data is there, just
cant
see
it the way i want

I need to get the number of records that have more than 2 employees on
them
and the count of the employees
I then would have to subtract the total number of records minus the
ones
that have more than 2 employees on them for my calculation. I can use
another form to do this and base it on another query but dont know how
to
structure it.
Remember for 1 or 2 employees the rate is 65, then after for each
employee
1/2 the rate per hour
Same thing with 55 rate.

Re-sent this morning. 05/16/ @ 10:20am

"Van T. Dinh" wrote in message
...
Dave

I got you message enquiring whether I got your file (which I didn't) and
when I replied, the e-mail bounced back undeliverable.

--
HTH
Van T. Dinh
MVP (Access)




"Dave Elliott" wrote in message
m...
The db is a invoice database. purpose of timecards form is to record
allinfo
for a invoice, such as employee time, vendor material, payments to
invoice,
employee commission. date of invoice and how many times sent and by who,
history of payments, count how many employees worked on what job, what
date
and their hours, this used to calculate the expenes for the job, i.e.
timecard as well as material used so invoice price can be calculated.
Time is not calculated on this form, only days, dates. The ONLY thin I
need
help with is the sub-form Time And Hours on the main form TimeCards,
thats
it.
as for the rocordsource, this is easily found via the forms in design
mode.
the whole timecard is one (1) record.
datasource for main form is query QDates, but this does no have anything
to
do with problem at hand.
recordsource for sub-form time and hours is a query named Time_Hours.
Call me if you have any questions or else e-mail you number and I will
call
you.
The database is very complex, but we are ONLY dealing with one form, the
sub-form, Time and Hours
the data is there but I need to see it in a different way.

Thanks,

Dave



"Van T. Dinh" wrote in message
...
Sorry but I am not getting the picture of your set-up. Post the
following:

1. What is the purpose of the database? What is the purpose of the
Form
/
Subform?

1. The RecordSource of the Main Form ("TimeCards"). This is important
as
I
got no ideas what the Form is supposed to do and hence can't place your
DCount or your requirement correctly. Since the Subform already "uses"
the
2 Tables (?) you described, I got no ideas of the data source for the
main
Form.

2. The RecordSource of the Subform ("Time and Hours")

3. The LinkMasterFields and LinkChildField of the Subform Control.

4. In one of the earlier, you posted an example with 4 Records (3 on
the
same date and 1 on a different date) and you mentioned "On the Form
with
record selector", I guess you actually meant the Subform?

5. From the same sample data, I guess you want the count of days with 1
or
2
employees AND the count of days with more than 2 employees that
*belong/
are
related* to the CurrentRecord of whatever on the main Form
(RecordSource
/
entity unknown - see point 1) so that you can work out the total hourly
rate
applicable for each day?

6. What happens if 2 or more employees work on the same date on the
same
(IGNORE THIS)
CurrentRecord on the main Form but they work different number of hours
at
different time? Let's say 3 employees but Employee1 works full day,
Employee2 work 1/2 day in the morning and Employee 3 works 1/4 day in
the
afternoon (hence the nett workforce is less than 2)? How would you
calculate the hourly rate in this case?

--
HTH
Van T. Dinh
MVP (Access)



"Dave Elliott" wrote in message
.. .
Table Structu 2 tables used for form; Hours and Employees
From the Hours table these fields;HoursID-TimeID-Hours-Overtime-Work
Date-OT
Rate-Pay Rate-EmployeeID
From the Employees table; EmployeeID

TimeID links the sub-form (Time and Hours) to the main form TimeCards
EmployeeID links the 2 tables and TimeID links the sub-form to the
main
form. (TimeCards) Master/Child
The sub-form gives exactly what I need, all the data is there, just
cant
see
it the way i want

I need to get the number of records that have more than 2 employees on
them
and the count of the employees
I then would have to subtract the total number of records minus the
ones
that have more than 2 employees on them for my calculation. I can use
another form to do this and base it on another query but dont know how
to
structure it.
Remember for 1 or 2 employees the rate is 65, then after for each
employee
1/2 the rate per hour
Same thing with 55 rate.

Will look into it when I get a chance this weekend.

--
Van T. Dinh
MVP (Access)



"Dave Elliott" wrote in message
. ..
Re-sent this morning. 05/16/ @ 10:20am

Thanks

"Van T. Dinh" wrote in message
...
Will look into it when I get a chance this weekend.

--
Van T. Dinh
MVP (Access)



"Dave Elliott" wrote in message
. ..
Re-sent this morning. 05/16/ @ 10:20am