I have created an Access database (2003) that contains information about the 
status of specific projects our office is working.  There is only one table 
and the database contains the names of about 30 employees.  Each employee is 
assigned specific work and required to input dates regarding the status of 
each part of the overall process (there are about 5 pieces).  Is it possible 
to create a query where I input the employee's name and a single date 
criteria (i.e. between 6/13/07 and 6/20/07) and Access will search across 
multiple fields to return the requested information?  For instance, I have 
the following fields:  Announcement Open date, Announcement closing date, 
Certificate issued date, Certificate closed date, Selection made date, 
Selection returned date, and Returned action date.  These are not set up as 
required fields so it is possible some will be blank.  However, since the 
information in the database is for the entire year, I would like to be able 
to just limit the information to a specific week of the year.  Essentially, 
the idea is to be able to see what the employee has been working on for any 
specific week and where they are on the specific action.  Any help anyone can 
provide would be MUCH appreciated.  

Based on the description you provided, I'll guess that this set of data 
started out as a ... spreadsheet!  Using repeating fields as you've 
described is pretty much the only way to do it in Excel, but Access is a 
relational database.  You won't get the power of Access' features and 
functions if you feed it 'sheet data.

Before you go any further, I strongly recommend you look into 
"normalization".  Or, if Access is only of value in this situation as a 
'bigger spreadsheet', consider using a spreadsheet instead.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Access Novice" <Access Novice@discussions.microsoft.com> wrote in message 
news:B6B13F4C-6BA7-4407-ABD6-5595E089F3E6@microsoft.com...
>I have created an Access database (2003) that contains information about 
>the
> status of specific projects our office is working.  There is only one 
> table
> and the database contains the names of about 30 employees.  Each employee 
> is
> assigned specific work and required to input dates regarding the status of
> each part of the overall process (there are about 5 pieces).  Is it 
> possible
> to create a query where I input the employee's name and a single date
> criteria (i.e. between 6/13/07 and 6/20/07) and Access will search across
> multiple fields to return the requested information?  For instance, I have
> the following fields:  Announcement Open date, Announcement closing date,
> Certificate issued date, Certificate closed date, Selection made date,
> Selection returned date, and Returned action date.  These are not set up 
> as
> required fields so it is possible some will be blank.  However, since the
> information in the database is for the entire year, I would like to be 
> able
> to just limit the information to a specific week of the year. 
> Essentially,
> the idea is to be able to see what the employee has been working on for 
> any
> specific week and where they are on the specific action.  Any help anyone 
> can
> provide would be MUCH appreciated. 

Your table structure needs to be revised to this --
EmpID - integer - foreign key from Employee table
ProjID - text or integer - foreign key from Project table
Action - text or integer - foreign key from Activity table
ActionDate - DateTime
Hours - single - record time spent

With this structure your can do your search easy.  Also you can roll up how 
much time was expended on each activity and how many days end-to-end for each 
activity.  It allows any number of correlations.

You can use a union query to build the new table.  Have a append query that 
pulls from the union query to fill the new structure.
SELECT [Announcement Open date] AS ActionDate, "Announcement Open" AS 
[Action], EmpID, ProjID, Hours
FROM YOUR_TABLE
UNION ALL SELECT [Announcement closing date] AS ActionDate, "Announcement 
Closing" AS [Action], EmpID, ProjID, Hours
FROM YOUR_TABLE
UNION ALL SELECT [Certificate issued date] AS ActionDate, "Certificate 
issued" AS [Action], EmpID, ProjID, Hours
FROM YOUR_TABLE
UNION ALL SELECT [Certificate closed date] AS ActionDate, "Certificate 
closed" AS [Action], EmpID, ProjID, Hours
FROM YOUR_TABLE
UNION ALL SELECT [Selection made date] AS ActionDate, "Selection made" AS 
[Action], EmpID, ProjID, Hours
FROM YOUR_TABLE
UNION ALL SELECT [Selection returned date]  AS ActionDate, "Selection 
returned" AS [Action], EmpID, ProjID, Hours
FROM YOUR_TABLE
UNION ALL SELECT [Returned action date] AS ActionDate, "Returned action" AS 
[Action], EmpID, ProjID, Hours
FROM YOUR_TABLE;

-- 
KARL DEWEY
Build a little - Test a little


"Access Novice" wrote:

> I have created an Access database (2003) that contains information about the 
> status of specific projects our office is working.  There is only one table 
> and the database contains the names of about 30 employees.  Each employee is 
> assigned specific work and required to input dates regarding the status of 
> each part of the overall process (there are about 5 pieces).  Is it possible 
> to create a query where I input the employee's name and a single date 
> criteria (i.e. between 6/13/07 and 6/20/07) and Access will search across 
> multiple fields to return the requested information?  For instance, I have 
> the following fields:  Announcement Open date, Announcement closing date, 
> Certificate issued date, Certificate closed date, Selection made date, 
> Selection returned date, and Returned action date.  These are not set up as 
> required fields so it is possible some will be blank.  However, since the 
> information in the database is for the entire year, I would like to be able 
> to just limit the information to a specific week of the year.  Essentially, 
> the idea is to be able to see what the employee has been working on for any 
> specific week and where they are on the specific action.  Any help anyone can 
> provide would be MUCH appreciated.  

Thank you for the assistance.  It sounds great but I am not comprehending 
this very well.  I am very new to the Access program and I created a VERY 
basic database.  I think this is a bit over my head.

I only have one table (Title: Workload Stats) that all of this information 
is plugged into.  I guess from my basic understanding, a union query is 
supposed to join two tables, correct?  From your suggestion, I understand you 
to indicate I need to create three separate tables.  I have created another 
table Employees but that was only to use as a dropdown option in the Workload 
stats form.  I also created many single queries that search for the 
information I am looking for but they are specific to a stage - is it 
possible to combine those into a union query and then proceed as you 
suggested?

Thanks!

"KARL DEWEY" wrote:

> Your table structure needs to be revised to this --
> EmpID - integer - foreign key from Employee table
> ProjID - text or integer - foreign key from Project table
> Action - text or integer - foreign key from Activity table
> ActionDate - DateTime
> Hours - single - record time spent
> 
> With this structure your can do your search easy.  Also you can roll up how 
> much time was expended on each activity and how many days end-to-end for each 
> activity.  It allows any number of correlations.
> 
> You can use a union query to build the new table.  Have a append query that 
> pulls from the union query to fill the new structure.
> SELECT [Announcement Open date] AS ActionDate, "Announcement Open" AS 
> [Action], EmpID, ProjID, Hours
> FROM YOUR_TABLE
> UNION ALL SELECT [Announcement closing date] AS ActionDate, "Announcement 
> Closing" AS [Action], EmpID, ProjID, Hours
> FROM YOUR_TABLE
> UNION ALL SELECT [Certificate issued date] AS ActionDate, "Certificate 
> issued" AS [Action], EmpID, ProjID, Hours
> FROM YOUR_TABLE
> UNION ALL SELECT [Certificate closed date] AS ActionDate, "Certificate 
> closed" AS [Action], EmpID, ProjID, Hours
> FROM YOUR_TABLE
> UNION ALL SELECT [Selection made date] AS ActionDate, "Selection made" AS 
> [Action], EmpID, ProjID, Hours
> FROM YOUR_TABLE
> UNION ALL SELECT [Selection returned date]  AS ActionDate, "Selection 
> returned" AS [Action], EmpID, ProjID, Hours
> FROM YOUR_TABLE
> UNION ALL SELECT [Returned action date] AS ActionDate, "Returned action" AS 
> [Action], EmpID, ProjID, Hours
> FROM YOUR_TABLE;
> 
> -- 
> KARL DEWEY
> Build a little - Test a little
> 
> 
> "Access Novice" wrote:
> 
> > I have created an Access database (2003) that contains information about the 
> > status of specific projects our office is working.  There is only one table 
> > and the database contains the names of about 30 employees.  Each employee is 
> > assigned specific work and required to input dates regarding the status of 
> > each part of the overall process (there are about 5 pieces).  Is it possible 
> > to create a query where I input the employee's name and a single date 
> > criteria (i.e. between 6/13/07 and 6/20/07) and Access will search across 
> > multiple fields to return the requested information?  For instance, I have 
> > the following fields:  Announcement Open date, Announcement closing date, 
> > Certificate issued date, Certificate closed date, Selection made date, 
> > Selection returned date, and Returned action date.  These are not set up as 
> > required fields so it is possible some will be blank.  However, since the 
> > information in the database is for the entire year, I would like to be able 
> > to just limit the information to a specific week of the year.  Essentially, 
> > the idea is to be able to see what the employee has been working on for any 
> > specific week and where they are on the specific action.  Any help anyone can 
> > provide would be MUCH appreciated.  

>>I only have one table (Title: Workload Stats) that all of this information 
is plugged into.  
Your table is laid out like a spreadsheet (Excel) instead of a relational 
database that Access is.
>>I guess from my basic understanding, a union query is supposed to join two tables, correct?
The union query I post calls the same table for each occurance date field 
you have in your spreadsheet to build a record for each.
>>I understand you to indicate I need to create three separate tables.
Actually four but you can use less.  You do not have to have project or 
action tables.  By using them in a one-to-many relationship all entries for 
project or action are exactly one spelling to eliminate errors.   You can 
also use the field evaluation in the table to accomplish the same thing.  
With the tables you can use them use as a dropdown option in the data entry 
forms.

-- 
KARL DEWEY
Build a little - Test a little


"Access Novice" wrote:

> Thank you for the assistance.  It sounds great but I am not comprehending 
> this very well.  I am very new to the Access program and I created a VERY 
> basic database.  I think this is a bit over my head.
> 
> I only have one table (Title: Workload Stats) that all of this information 
> is plugged into.  I guess from my basic understanding, a union query is 
> supposed to join two tables, correct?  From your suggestion, I understand you 
> to indicate I need to create three separate tables.  I have created another 
> table Employees but that was only to use as a dropdown option in the Workload 
> stats form.  I also created many single queries that search for the 
> information I am looking for but they are specific to a stage - is it 
> possible to combine those into a union query and then proceed as you 
> suggested?
> 
> Thanks!
> 
> "KARL DEWEY" wrote:
> 
> > Your table structure needs to be revised to this --
> > EmpID - integer - foreign key from Employee table
> > ProjID - text or integer - foreign key from Project table
> > Action - text or integer - foreign key from Activity table
> > ActionDate - DateTime
> > Hours - single - record time spent
> > 
> > With this structure your can do your search easy.  Also you can roll up how 
> > much time was expended on each activity and how many days end-to-end for each 
> > activity.  It allows any number of correlations.
> > 
> > You can use a union query to build the new table.  Have a append query that 
> > pulls from the union query to fill the new structure.
> > SELECT [Announcement Open date] AS ActionDate, "Announcement Open" AS 
> > [Action], EmpID, ProjID, Hours
> > FROM YOUR_TABLE
> > UNION ALL SELECT [Announcement closing date] AS ActionDate, "Announcement 
> > Closing" AS [Action], EmpID, ProjID, Hours
> > FROM YOUR_TABLE
> > UNION ALL SELECT [Certificate issued date] AS ActionDate, "Certificate 
> > issued" AS [Action], EmpID, ProjID, Hours
> > FROM YOUR_TABLE
> > UNION ALL SELECT [Certificate closed date] AS ActionDate, "Certificate 
> > closed" AS [Action], EmpID, ProjID, Hours
> > FROM YOUR_TABLE
> > UNION ALL SELECT [Selection made date] AS ActionDate, "Selection made" AS 
> > [Action], EmpID, ProjID, Hours
> > FROM YOUR_TABLE
> > UNION ALL SELECT [Selection returned date]  AS ActionDate, "Selection 
> > returned" AS [Action], EmpID, ProjID, Hours
> > FROM YOUR_TABLE
> > UNION ALL SELECT [Returned action date] AS ActionDate, "Returned action" AS 
> > [Action], EmpID, ProjID, Hours
> > FROM YOUR_TABLE;
> > 
> > -- 
> > KARL DEWEY
> > Build a little - Test a little
> > 
> > 
> > "Access Novice" wrote:
> > 
> > > I have created an Access database (2003) that contains information about the 
> > > status of specific projects our office is working.  There is only one table 
> > > and the database contains the names of about 30 employees.  Each employee is 
> > > assigned specific work and required to input dates regarding the status of 
> > > each part of the overall process (there are about 5 pieces).  Is it possible 
> > > to create a query where I input the employee's name and a single date 
> > > criteria (i.e. between 6/13/07 and 6/20/07) and Access will search across 
> > > multiple fields to return the requested information?  For instance, I have 
> > > the following fields:  Announcement Open date, Announcement closing date, 
> > > Certificate issued date, Certificate closed date, Selection made date, 
> > > Selection returned date, and Returned action date.  These are not set up as 
> > > required fields so it is possible some will be blank.  However, since the 
> > > information in the database is for the entire year, I would like to be able 
> > > to just limit the information to a specific week of the year.  Essentially, 
> > > the idea is to be able to see what the employee has been working on for any 
> > > specific week and where they are on the specific action.  Any help anyone can 
> > > provide would be MUCH appreciated.