Hiding dates Query fields

[sooner]

I have a query that selects records for dates (six date fields) that are
equal to a certain month. There are several fields with dates and I would
like to "hide" those values which are not equal to the month that I need.

 

[John W. Vinson]

I have a query that selects records for dates (six date fields) that are
equal to a certain month. There are several fields with dates and I would
like to "hide" those values which are not equal to the month that I need.

More information please! Could you post the actual fieldnames and meanings of
your table fields, and the SQL of the query? It sounds like your table
structure is not properly normalized: if you have one record with six dates, a
Query will either retrieve the whole record or not; it sounds like you want to
"partially" retrieve the record.

 

[sooner]

I need to notify an employer at the end of each month of any employees who
had any background checks 9six different kinds) during that month. I would
prefer to only send them a listing of those checks which returned that month.
My query selects all the records that I want. However, it also shows the
dates for those checks that came back during the previous months(which I
would rather not revisit.
The criteria that I used is the month([field date name])=8. Then, I used
the the same criteria using another field date name on the next row down.

 

[John W. Vinson]

Please... as requested... post the table structure (with fieldnames and
datatypes) and the SQL view of the query. You are presenting a business-end
view, which is helpful; but to answer your Access technical question I need
the Access technical view.

I still suspect that you have six FIELDS for background checks. That is *wrong
design* - it's committing spreadsheet! What will you do if the administration
institutes a seventh kind of background check? Restructure your table, all
your queries, all your forms, all your reports? OUCH!

I need to notify an employer at the end of each month of any employees who
had any background checks 9six different kinds) during that month. I would
prefer to only send them a listing of those checks which returned that month.
My query selects all the records that I want. However, it also shows the
dates for those checks that came back during the previous months(which I
would rather not revisit.
The criteria that I used is the month([field date name])=8. Then, I used
the the same criteria using another field date name on the next row down.

More information please! Could you post the actual fieldnames and meanings of
your table fields, and the SQL of the query? It sounds like your table
structure is not properly normalized: if you have one record with six dates, a
Query will either retrieve the whole record or not; it sounds like you want to
"partially" retrieve the record.

 

[sooner]

John,

Unfortunately, I do not have the actual database in front of me. I do
actually need 6 fields to track when each background check was returned.
Sorry, I don't have much to go on. I was hoping to supress the information
that I don't want sent out to the employers (repeat information from previous
months) and focus on just the checks that returned just during the past
month.

Please... as requested... post the table structure (with fieldnames and
datatypes) and the SQL view of the query. You are presenting a business-end
view, which is helpful; but to answer your Access technical question I need
the Access technical view.

I still suspect that you have six FIELDS for background checks. That is *wrong
design* - it's committing spreadsheet! What will you do if the administration
institutes a seventh kind of background check? Restructure your table, all
your queries, all your forms, all your reports? OUCH!

I need to notify an employer at the end of each month of any employees who
had any background checks 9six different kinds) during that month. I would
prefer to only send them a listing of those checks which returned that month.
My query selects all the records that I want. However, it also shows the
dates for those checks that came back during the previous months(which I
would rather not revisit.
The criteria that I used is the month([field date name])=8. Then, I used
the the same criteria using another field date name on the next row down.

I have a query that selects records for dates (six date fields) that are
equal to a certain month. There are several fields with dates and I would
like to "hide" those values which are not equal to the month that I need.

More information please! Could you post the actual fieldnames and meanings of
your table fields, and the SQL of the query? It sounds like your table
structure is not properly normalized: if you have one record with six dates, a
Query will either retrieve the whole record or not; it sounds like you want to
"partially" retrieve the record.

 

[John Spencer]

Ok, your database is mis-designed.

You should have a table with
PersonID
CheckType
CheckDate
You have a relationship between this table and the current table. In
the current table you would not have any fields for storing the check dates.

Then you create a record in that table for each type of check that a
person has. This makes life simple if (When) you need to add an
additional type of check, find checks run in a specific date range, etc.

If you can't change your database then you can use a union query with
six subqueries. Here is an example of a UNION query with only 3 types
of reviews/checks.

SELECT EmployeeID, BGCheckDate1 as CheckDate, "Security" as CheckType
FROM YourTable
WHERE BGCheckDate1 is Not Null
UNION ALL
SELECT EmployeeID, BGCheckDate2, "Agency" as CheckType
FROM YourTable
WHERE BGCheckDate2 is Not Null
UNION ALL
SELECT EmployeeID, BGCheckDate3, "Police" as CheckType
FROM YourTable
WHERE BGCheckDate3 is Not Null

Once you have this query built you can use it as the source for another
query. In that query you can apply criteria on the date field and get
only the checks within a specific date range.

If you want detailed help, do as John Vinson suggested and tell us (him)
your data structure or at a minimum copy and paste the SQL statement of
the query you are currently using (Hint: Menu: VIEW: SQL).

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John,

Unfortunately, I do not have the actual database in front of me. I do
actually need 6 fields to track when each background check was returned.
Sorry, I don't have much to go on. I was hoping to supress the information
that I don't want sent out to the employers (repeat information from previous
months) and focus on just the checks that returned just during the past
month.

Please... as requested... post the table structure (with fieldnames and
datatypes) and the SQL view of the query. You are presenting a business-end
view, which is helpful; but to answer your Access technical question I need
the Access technical view.

I still suspect that you have six FIELDS for background checks. That is *wrong
design* - it's committing spreadsheet! What will you do if the administration
institutes a seventh kind of background check? Restructure your table, all
your queries, all your forms, all your reports? OUCH!

I need to notify an employer at the end of each month of any employees who
had any background checks 9six different kinds) during that month. I would
prefer to only send them a listing of those checks which returned that month.
My query selects all the records that I want. However, it also shows the
dates for those checks that came back during the previous months(which I
would rather not revisit.
The criteria that I used is the month([field date name])=8. Then, I used
the the same criteria using another field date name on the next row down.

:

I have a query that selects records for dates (six date fields) that are
equal to a certain month. There are several fields with dates and I would
like to "hide" those values which are not equal to the month that I need.
More information please! Could you post the actual fieldnames and meanings of
your table fields, and the SQL of the query? It sounds like your table
structure is not properly normalized: if you have one record with six dates, a
Query will either retrieve the whole record or not; it sounds like you want to
"partially" retrieve the record.