Queries - how to search on multiple fields

A

Access Novice

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.
 
J

Jeff Boyce

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
 
K

KARL DEWEY

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;
 
A

Access Novice

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
K

KARL DEWEY

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.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.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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crating User Form to Add Names 1
Copy previous record into form field 0
Insert Multiple Fields without Dialog closing between each... 0
Display weeks from a Milestone 0
Dynamic Naming of Form Button 3
Using Access Database to build contracts with fields on pdf document 0
Creating a Search using a combo box and then creating a report. 4
Clearing Fields in a Word Form 5

Top