Weird Query Problem

[Golfinray]

I have 11 date fields. The dates are various stages of project completion.
Some construction projects are just starting and some are right at the
finish. Managers put dates in a form when each step in the process is
complete so that we know where we are on each project. The table, query, and
form all work fine. The problem is I went in to query out where each project
is for a report to the boss. He wants to know what steps have been completed
in the last week (if there is a date there, it is complete.) I pulled in the
project# and the 11 fields. I set each fields criteria to Between 4/16/2008
and 4/23/2008. Nothing! Do I need to use an OR statement or AND or what? I'm
lost. Thanks so much!

 

[KARL DEWEY]

I have 11 date fields.
Your structure is a spreadsheet instead of a relational database. Change it
to look like this --
Project -
Phase -
PhaseDate -

To reorganize your data use a union query like this --
SELECT Project, "Phase 1" AS [Phase], [Phase 1] AS [PhaseDate]
FROM UrTable
UNION SELECT Project, "Phase 2" AS [Phase], [Phase 2] AS [PhaseDate]
FROM UrTable
UNION SELECT Project, "Phase 3" AS [Phase], [Phase 3] AS [PhaseDate]
FROM UrTable
..........
UNION SELECT Project, "Phase 11" AS [Phase], [Phase 11] AS [PhaseDate]
FROM UrTable;

 

[Klatuu]

You do need OR conditions. So it would be like [Date1] BETWEEN #4/16/20083
AND #4/23/2008# OR [Date2] BETWEEN BETWEEN #4/16/20083 AND #4/23/2008# OR
[Date3] BETWEEN #4/16/20083 AND #4/23/2008# OR ...

 

[Golfinray]

Karl - Thanks for responding. If I make it as you recommend, when I build
forms, how would I have data entry for each step of completion? I need one
box the managers enter data into that says Construction Approval, another
that says Construction Contracts, Another Construction Bids, another
Construction Start. Wouldn't I have to query out those 11 dates anyway for my
form? Thanks, Ray

Your structure is a spreadsheet instead of a relational database. Change it
to look like this --
Project -
Phase -
PhaseDate -

To reorganize your data use a union query like this --
SELECT Project, "Phase 1" AS [Phase], [Phase 1] AS [PhaseDate]
FROM UrTable
UNION SELECT Project, "Phase 2" AS [Phase], [Phase 2] AS [PhaseDate]
FROM UrTable
UNION SELECT Project, "Phase 3" AS [Phase], [Phase 3] AS [PhaseDate]
FROM UrTable
..........
UNION SELECT Project, "Phase 11" AS [Phase], [Phase 11] AS [PhaseDate]
FROM UrTable;


--
KARL DEWEY
Build a little - Test a little

I have 11 date fields. The dates are various stages of project completion.
Some construction projects are just starting and some are right at the
finish. Managers put dates in a form when each step in the process is
complete so that we know where we are on each project. The table, query, and
form all work fine. The problem is I went in to query out where each project
is for a report to the boss. He wants to know what steps have been completed
in the last week (if there is a date there, it is complete.) I pulled in the
project# and the 11 fields. I set each fields criteria to Between 4/16/2008
and 4/23/2008. Nothing! Do I need to use an OR statement or AND or what? I'm
lost. Thanks so much!

 

[Klatuu]

See my previous post. It is really very simple.
--
Dave Hargis, Microsoft Access MVP

Karl - Thanks for responding. If I make it as you recommend, when I build
forms, how would I have data entry for each step of completion? I need one
box the managers enter data into that says Construction Approval, another
that says Construction Contracts, Another Construction Bids, another
Construction Start. Wouldn't I have to query out those 11 dates anyway for my
form? Thanks, Ray

I have 11 date fields.

Your structure is a spreadsheet instead of a relational database. Change it
to look like this --
Project -
Phase -
PhaseDate -

To reorganize your data use a union query like this --
SELECT Project, "Phase 1" AS [Phase], [Phase 1] AS [PhaseDate]
FROM UrTable
UNION SELECT Project, "Phase 2" AS [Phase], [Phase 2] AS [PhaseDate]
FROM UrTable
UNION SELECT Project, "Phase 3" AS [Phase], [Phase 3] AS [PhaseDate]
FROM UrTable
..........
UNION SELECT Project, "Phase 11" AS [Phase], [Phase 11] AS [PhaseDate]
FROM UrTable;


--
KARL DEWEY
Build a little - Test a little

I have 11 date fields. The dates are various stages of project completion.
Some construction projects are just starting and some are right at the
finish. Managers put dates in a form when each step in the process is
complete so that we know where we are on each project. The table, query, and
form all work fine. The problem is I went in to query out where each project
is for a report to the boss. He wants to know what steps have been completed
in the last week (if there is a date there, it is complete.) I pulled in the
project# and the 11 fields. I set each fields criteria to Between 4/16/2008
and 4/23/2008. Nothing! Do I need to use an OR statement or AND or what? I'm
lost. Thanks so much!

 

[KARL DEWEY]

Use a form/subform with project information in the form and phases in a
subform.
A least three tables.
Project --
ProjID - Autonumber - primary key
Title - text
Start - DateTime
ProjEnd - DateTime
Contact - text
etc.

Phase --
PhaseID - Autonumber - primary key
Name - text

ProjPhase --
ProjPhaseID - Autonumber - primary key
ProjID - number - integer - foreign key
PhaseID - number - integer - foreign key
PhaseDate - DateTime


KARL DEWEY
Build a little - Test a little

Karl - Thanks for responding. If I make it as you recommend, when I build
forms, how would I have data entry for each step of completion? I need one
box the managers enter data into that says Construction Approval, another
that says Construction Contracts, Another Construction Bids, another
Construction Start. Wouldn't I have to query out those 11 dates anyway for my
form? Thanks, Ray

I have 11 date fields.

Your structure is a spreadsheet instead of a relational database. Change it
to look like this --
Project -
Phase -
PhaseDate -

To reorganize your data use a union query like this --
SELECT Project, "Phase 1" AS [Phase], [Phase 1] AS [PhaseDate]
FROM UrTable
UNION SELECT Project, "Phase 2" AS [Phase], [Phase 2] AS [PhaseDate]
FROM UrTable
UNION SELECT Project, "Phase 3" AS [Phase], [Phase 3] AS [PhaseDate]
FROM UrTable
..........
UNION SELECT Project, "Phase 11" AS [Phase], [Phase 11] AS [PhaseDate]
FROM UrTable;


--
KARL DEWEY
Build a little - Test a little

I have 11 date fields. The dates are various stages of project completion.
Some construction projects are just starting and some are right at the
finish. Managers put dates in a form when each step in the process is
complete so that we know where we are on each project. The table, query, and
form all work fine. The problem is I went in to query out where each project
is for a report to the boss. He wants to know what steps have been completed
in the last week (if there is a date there, it is complete.) I pulled in the
project# and the 11 fields. I set each fields criteria to Between 4/16/2008
and 4/23/2008. Nothing! Do I need to use an OR statement or AND or what? I'm
lost. Thanks so much!