query criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If someone could help me I would greatly appreicate it. Here's the problem:

I run a monthly report which by using criteria on my file # field gives me a
list of that month's files. I also have criteria in to keep it from pulling
files that were on HOLD for that month.

Now on new report with the next months files, how can I also retrieve those
files from months before that have now been taken of HOLD.

Hope that makes since. ANy help is appreciated. Thanks and God Bless
 
Are you saying that your records do NOT have a date/time field that holds
the date (?and time) of the file? How do you determine "that month's
files"?

If you DO have a field with a date in it, your query could return files with
a month and without a HOLD. For any month, if you use a parameter query.

Regards

Jeff Boyce
<Office/Access MVP>
 
On Tue, 6 Dec 2005 14:49:02 -0800, need help <need
If someone could help me I would greatly appreicate it. Here's the problem:

I run a monthly report which by using criteria on my file # field gives me a
list of that month's files. I also have criteria in to keep it from pulling
files that were on HOLD for that month.

Now on new report with the next months files, how can I also retrieve those
files from months before that have now been taken of HOLD.

Hope that makes since. ANy help is appreciated. Thanks and God Bless
Click to expand...

It would help us to know a bit about the structure of your table. How
do you record that a file is on hold, or that it has been taken off
hold? Exactly which records do you want to see on the report?

John W. Vinson[MVP]
 
Hopefully this will help:
I do NOT have a date/time field. My file #s are numbered according to month
and year (ie File #05-12099 is the 99th file received during the month of
December, 2005.) By having that # in my file# field I can pull a particular
months files. I also have a field with a drop down list (combo box) with the
choices of ACTIVE, COMPLETED and HOLD. When a file is placed on HOLD I do
not want it to show up on that months report. But when it is taken off HOLD
I need to see it on the next report I run for whatever month I'm running.
Example: say file 12099 is on HOLD so I won't see it on December report.
But it will probablly be off HOLD in a few weeks so I will need to see it on
January 2006 report. What I need to see on each months report is all ACTIVE
files for that month and only files from previous months that have now been
taken off HOLD. I hope I'm not too much trouble 0 I'm new at this. Sorry.
Thanks so much for your help and God Bless.
 
wow!! I've just finished reading over more of your posts. I am wondering if
I even have this database set up the most efficient way. Can I please tell
you what I'm using this for and maybe you will have some suggestions.

This is an appraisal business and the database is to be used for assigning
and managing file #s when orders are received AND also used as a mechanism to
produce monthly paysheets for our appraisers/trainees. I think its pretty
simple but it seems so confusing. Below are the fields I need.

The fields I know I need are:
Year: (current year - to be used as part of file #)
File#:(example: 12099 - 99th file received in december)
Subject Address: of property being appraised
Subject City: of property being appraised
Client Name: who we got order from
Client Loan #: for this order
Client Order #: for this order
Scheduling Notes: where we can make notes of what happens when we call
property owner to schedule appointment.

Trainee: Initials of Trainee that worked on file (if any)
Appraiser: Initials of Appraiser that worked on file

Status: is file Active, Complete, Cancelled or on HOLD (if its on HOLD it
does not go on pay sheet (report) but needs to go on next pay sheet after its
taken off HOLD.

Fees: based on what appraiser/trainee did on file (ie: inspected,comped and
typed report or maybe he just inspected and comped it and the trainee typed
the report for his review. These fees are earned by percent of total fee.
ie: appraiser might make 45% for comp/inspection and trainee might make 15%
for typing a report that pays a total fee of $350. There are 4 pay
possibilities for the appraiser and 6 pay possibilities for the trainee.

Can you tell me how many tables I need and anything else you think I need to
know? I am really in the dark here.

Thanks and God Bless You.
 
wow!! I've just finished reading over more of your posts. I am wondering if
I even have this database set up the most efficient way. Can I please tell
you what I'm using this for and maybe you will have some suggestions.
Click to expand...

I don't think I would have done it just this way.
This is an appraisal business and the database is to be used for assigning
and managing file #s when orders are received AND also used as a mechanism to
produce monthly paysheets for our appraisers/trainees. I think its pretty
simple but it seems so confusing. Below are the fields I need.
Click to expand...

If the definition of the purpose of a table includes the word AND...
you may need two tables.
The fields I know I need are:
Year: (current year - to be used as part of file #)
Click to expand...

Year is a reserved word and a bad idea as a fieldname. Access will get
it confused with the Year() function.
File#:(example: 12099 - 99th file received in december)
Click to expand...

Intelligent keys - storing two or more different kinds of data (dates
and sequential numbers) in a single field - is bad design. Unless this
field is needed to continue a long-established manual number, you can
do better.

Subject Address: of property being appraised
Subject City: of property being appraised
Client Name: who we got order from
Client Loan #: for this order
Client Order #: for this order
Click to expand...

Can one Client be involved with multiple Properties? Or can one
property have multiple appraisals? If so - you need more tables. Each
Table should refer to one specific "Entity" - real-life person, thing
or event. At the very least I'd expect you would need a table of
Properties; a table of Clients; and a table of Appraisals.
Scheduling Notes: where we can make notes of what happens when we call
property owner to schedule appointment.
Click to expand...

This should *CERTAINLY* be in another table, related one-to-many to
the Clients table. One note per call or per subject.
Trainee: Initials of Trainee that worked on file (if any)
Appraiser: Initials of Appraiser that worked on file
Click to expand...

I dislike initials as identifiers. Instead I'd suggest an Employee
table with an employee ID; you can pick the employee's full name (Jim
Harris, JH, or Janet Hodgekins, JH) from a combo box and store just
the ID.
Status: is file Active, Complete, Cancelled or on HOLD (if its on HOLD it
does not go on pay sheet (report) but needs to go on next pay sheet after its
taken off HOLD.
Click to expand...

ok... probably a little four-row one-field lookup table would help.
Fees: based on what appraiser/trainee did on file (ie: inspected,comped and
typed report or maybe he just inspected and comped it and the trainee typed
the report for his review. These fees are earned by percent of total fee.
ie: appraiser might make 45% for comp/inspection and trainee might make 15%
for typing a report that pays a total fee of $350. There are 4 pay
possibilities for the appraiser and 6 pay possibilities for the trainee.
Click to expand...

None of the fee calculations should be stored in your table; you may
need a fee table and/or some VBA code, but the fee should be
calculated dynamically rather than stored.
Can you tell me how many tables I need and anything else you think I need to
know? I am really in the dark here.
Click to expand...

Actually building the database would go beyond volunteer work, but I
hope the ideas above give you a start. You may also want to look at
the resources at

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101


John W. Vinson[MVP]
 
Back
Top