Lookup a record in another table based on a report's subtotal

[Guest]

taAccess2000
I've created a report where I enter the desired dates worked for any given
period via a parameter. Its based on on a query containing the names, hours,
job, rates and exemptions, etc). When I run just my query I get a list of 59
records which is correct. The report subtotals for each person and for each
different job which is what I want. This is my gross payroll per person, per
job, per week. I have a tax table that I need to use to look up this gross.
This is where I get lost.
For example:
In the report the first subtotal yields a GROSS of $3000, MARITAL STATUS is
Single
A sample listing of the first 3 records of the tax table follows:

REC# MSTATUS FMINIMUM FMAXIMUM FPERCENT EXCESS
1 Single 0 2650
0 0
2 Single 2650 9700
10% 2650
3 Single 9700 30800
15% 9700

CRITERIA: MARITAL STATUS = MSTATUS AND GROSS IS BETWEEN FMINIMUM AND FMAXIMUM.
Nothing I've tried in the report gives me access to this table. I'm an
access novice so I'd appreciate any help. If I haven't provided enough info,
I'd be happy to provide more. I can't move forward at all.

Thanks

 

[Duane Hookom]

You haven't to us which field you want to return or the name of your table.
Do you want to return FPERCENT?
Try something like:
=DLookup("FPERCENT","tblTaxRates","MSTATUS = '" & [MaritalStatus] & "' AND "
& txtGross & " Between FMINIMUM and FMAXIMUM")

 

[Guest]

Thanks so much Duane. I used your example and was able to get to the table I
needed. I see I have a lot to learn.
I need to use 3 fields from the tblfedtax ([fpercent], [amt2ded],
[fminimum]. Then I can calculate a formula from them based on the gross. I
only actually need the result of the formula to show on my report.
I had to use several dlookups and brought the fields to my report and hid
them. (Can I do a multiple dlookup?)
I finally got the figure I needed but something tells me I should be able to
approach this more simply. Any ideas?

You haven't to us which field you want to return or the name of your table.
Do you want to return FPERCENT?
Try something like:
=DLookup("FPERCENT","tblTaxRates","MSTATUS = '" & [MaritalStatus] & "' AND "
& txtGross & " Between FMINIMUM and FMAXIMUM")

--
Duane Hookom
MS Access MVP

taAccess2000
I've created a report where I enter the desired dates worked for any given
period via a parameter. Its based on on a query containing the names,
hours,
job, rates and exemptions, etc). When I run just my query I get a list of
59
records which is correct. The report subtotals for each person and for
each
different job which is what I want. This is my gross payroll per person,
per
job, per week. I have a tax table that I need to use to look up this
gross.
This is where I get lost.
For example:
In the report the first subtotal yields a GROSS of $3000, MARITAL STATUS
is
Single
A sample listing of the first 3 records of the tax table follows:

REC# MSTATUS FMINIMUM FMAXIMUM FPERCENT
EXCESS
1 Single 0 2650
0 0
2 Single 2650 9700
10% 2650
3 Single 9700 30800
15% 9700

CRITERIA: MARITAL STATUS = MSTATUS AND GROSS IS BETWEEN FMINIMUM AND
FMAXIMUM.
Nothing I've tried in the report gives me access to this table. I'm an
access novice so I'd appreciate any help. If I haven't provided enough
info,
I'd be happy to provide more. I can't move forward at all.

Thanks

 

[Duane Hookom]

I prefer to include most of this in the report's record source. I'm not sure
all what you need but you might want to add tblTaxRates to your report's
query and join the MSTATUS and MaritalStatus fields and set the criteria
under a Gross field or calculation to Between FMINIMUM and FMAXIMUM.

If you can't do this in a query, I recommend putting the calculations in a
user-defined function where you have much more flexibility and it is much
easier to maintain.

--
Duane Hookom
MS Access MVP

Thanks so much Duane. I used your example and was able to get to the
table I
needed. I see I have a lot to learn.
I need to use 3 fields from the tblfedtax ([fpercent], [amt2ded],
[fminimum]. Then I can calculate a formula from them based on the gross.
I
only actually need the result of the formula to show on my report.
I had to use several dlookups and brought the fields to my report and hid
them. (Can I do a multiple dlookup?)
I finally got the figure I needed but something tells me I should be able
to
approach this more simply. Any ideas?

You haven't to us which field you want to return or the name of your
table.
Do you want to return FPERCENT?
Try something like:
=DLookup("FPERCENT","tblTaxRates","MSTATUS = '" & [MaritalStatus] & "'
AND "
& txtGross & " Between FMINIMUM and FMAXIMUM")

--
Duane Hookom
MS Access MVP

taAccess2000
I've created a report where I enter the desired dates worked for any
given
period via a parameter. Its based on on a query containing the names,
hours,
job, rates and exemptions, etc). When I run just my query I get a list
of
59
records which is correct. The report subtotals for each person and for
each
different job which is what I want. This is my gross payroll per
person,
per
job, per week. I have a tax table that I need to use to look up this
gross.
This is where I get lost.
For example:
In the report the first subtotal yields a GROSS of $3000, MARITAL
STATUS
is
Single
A sample listing of the first 3 records of the tax table follows:

REC# MSTATUS FMINIMUM FMAXIMUM FPERCENT
EXCESS
1 Single 0 2650
0 0
2 Single 2650 9700
10% 2650
3 Single 9700 30800
15% 9700

CRITERIA: MARITAL STATUS = MSTATUS AND GROSS IS BETWEEN FMINIMUM AND
FMAXIMUM.
Nothing I've tried in the report gives me access to this table. I'm an
access novice so I'd appreciate any help. If I haven't provided enough
info,
I'd be happy to provide more. I can't move forward at all.

Thanks

 

[Guest]

I'll give it a try and thank you very much for taking the time to answer my
questions.

I prefer to include most of this in the report's record source. I'm not sure
all what you need but you might want to add tblTaxRates to your report's
query and join the MSTATUS and MaritalStatus fields and set the criteria
under a Gross field or calculation to Between FMINIMUM and FMAXIMUM.

If you can't do this in a query, I recommend putting the calculations in a
user-defined function where you have much more flexibility and it is much
easier to maintain.

--
Duane Hookom
MS Access MVP

Thanks so much Duane. I used your example and was able to get to the
table I
needed. I see I have a lot to learn.
I need to use 3 fields from the tblfedtax ([fpercent], [amt2ded],
[fminimum]. Then I can calculate a formula from them based on the gross.
I
only actually need the result of the formula to show on my report.
I had to use several dlookups and brought the fields to my report and hid
them. (Can I do a multiple dlookup?)
I finally got the figure I needed but something tells me I should be able
to
approach this more simply. Any ideas?

You haven't to us which field you want to return or the name of your
table.
Do you want to return FPERCENT?
Try something like:
=DLookup("FPERCENT","tblTaxRates","MSTATUS = '" & [MaritalStatus] & "'
AND "
& txtGross & " Between FMINIMUM and FMAXIMUM")

--
Duane Hookom
MS Access MVP


taAccess2000
I've created a report where I enter the desired dates worked for any
given
period via a parameter. Its based on on a query containing the names,
hours,
job, rates and exemptions, etc). When I run just my query I get a list
of
59
records which is correct. The report subtotals for each person and for
each
different job which is what I want. This is my gross payroll per
person,
per
job, per week. I have a tax table that I need to use to look up this
gross.
This is where I get lost.
For example:
In the report the first subtotal yields a GROSS of $3000, MARITAL
STATUS
is
Single
A sample listing of the first 3 records of the tax table follows:

REC# MSTATUS FMINIMUM FMAXIMUM FPERCENT
EXCESS
1 Single 0 2650
0 0
2 Single 2650 9700
10% 2650
3 Single 9700 30800
15% 9700

CRITERIA: MARITAL STATUS = MSTATUS AND GROSS IS BETWEEN FMINIMUM AND
FMAXIMUM.
Nothing I've tried in the report gives me access to this table. I'm an
access novice so I'd appreciate any help. If I haven't provided enough
info,
I'd be happy to provide more. I can't move forward at all.

Thanks