Help with query criteria

[Opal]

I am creating an inventory database. Inventory is taken 3 times per
day based on shift start. I want to be able to create a parameter
query and then a report that will show the inventory taken at the
start of each shift. There are nine areas that are inventoried each
shift. I need to be able to query by date and time. I want the
times
to be broken down by shift name (i.e. Days, Afternoons, Nights). The
form that feeds the table where the query is run has a txtDate field
and txtTime field with default values of =Date() and =Time() so that
when
the data is saved to the Inventory table the time and date are there
automatically. However, with nine areas to inventory and several
people entering the inventory into the database, it will not be
entered at exactly the same time each shift but the date is the same.
Time is formatted to "medium time" and inventory will be taken
between:

Days = 6:00am to 7:00am
Afternoons = 2:00pm to 5:45pm (due to two different afternoon shift
start times)
Nights = 10:00pm to 11:00pm

Since there are three separate inventories taken I am having trouble
with
establishing the criteria for the query.
Should I create a table with these ranges noted and use a "
Where....between..."
SQL statement....? I am stumped on how to achieve the desired result
with
this.

 

[John Spencer]

You need to calculate the shift. You could use something like the
following.

Field: Shift: IIF([TimeField] Between #00:06:00# and
#00:07:00#,"Morning, IIF([TimeField] Between #00:14:00# and
#00:17:45#,"Afternoon",IIF([TimeField] Between #00:22:00# and
#00:23:00#,"Night","Out-Of-Range")))


A better solution would be to add a table with the shift names and start
and stop times. Then you could use that in a query to get the shift
names. If the time ranges changed, then you could edit the table.

However, that is more complex to implement in a query since the query
cannot be constructed in the query grid and must be built in SQL view.
A simple version of that would be something like the following.

Assumptions:
Table: ShiftTable with 3 fields
ShiftName
StartTime
EndTime
No Overlaps on StartTime and EndTime between records; StartTime always
before EndTime; all time ranges encompass only one day (no Start 11 PM,
end 2 AM type records)

SELECT I.*, S.ShiftName
FROM InventoryTable as I INNER JOIN ShiftTable as S
On I.TimeField Between S.StartTime and S.EndTime


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Opal]

You need to calculate the shift. You could use something like the
following.

Field: Shift: IIF([TimeField] Between #00:06:00# and
#00:07:00#,"Morning, IIF([TimeField] Between #00:14:00# and
#00:17:45#,"Afternoon",IIF([TimeField] Between #00:22:00# and
#00:23:00#,"Night","Out-Of-Range")))

A better solution would be to add a table with the shift names and start
and stop times. Then you could use that in a query to get the shift
names. If the time ranges changed, then you could edit the table.

However, that is more complex to implement in a query since the query
cannot be constructed in the query grid and must be built in SQL view.
A simple version of that would be something like the following.

Assumptions:
Table: ShiftTable with 3 fields
ShiftName
StartTime
EndTime
No Overlaps on StartTime and EndTime between records; StartTime always
before EndTime; all time ranges encompass only one day (no Start 11 PM,
end 2 AM type records)

SELECT I.*, S.ShiftName
FROM InventoryTable as I INNER JOIN ShiftTable as S
On I.TimeField Between S.StartTime and S.EndTime

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================



Show quoted text -

Thank you John. I appreciate your assistance, but I am still a little
confused. I have created the table, which is where I thought I should
begin with this. But the "Select" statement you wrote has lost
me....Am I to create a select query with the two tables (Inventory and
ShiftName) and put this in the criteria? Sorry, I've just never seen
a statement like this and
my exposure to queries has been very basic to this point.

 

[John Spencer]

No, what I showed would be the entire query. To do this, you would need
to open the query in SQL view. Menu: View >> SQL

Then you would need to type in the query string. If you aren't familiar
with this. Try the following

Make a new query
-- Add the new table and the Inventory table to the query
-- Select the fields you want to display
-- Drag from inventory.timefield to the startTime field of the new table
-- Add any where conditions you need/want
-- Select View: SQL from the menu

-- In the text view look for the text that starts with
INVoices INNER JOIN [YourNewTable] ON Invoices.TimeField =
YourNewTable.StartTime
-- Replace the = with Between
-- after StartTime, add " AND YourNewTable.EndTime" (no quotes)
-- Try to run the query. View: DataSheet



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

You need to calculate the shift. You could use something like the
following.

Field: Shift: IIF([TimeField] Between #00:06:00# and
#00:07:00#,"Morning, IIF([TimeField] Between #00:14:00# and
#00:17:45#,"Afternoon",IIF([TimeField] Between #00:22:00# and
#00:23:00#,"Night","Out-Of-Range")))

A better solution would be to add a table with the shift names and start
and stop times. Then you could use that in a query to get the shift
names. If the time ranges changed, then you could edit the table.

However, that is more complex to implement in a query since the query
cannot be constructed in the query grid and must be built in SQL view.
A simple version of that would be something like the following.

Assumptions:
Table: ShiftTable with 3 fields
ShiftName
StartTime
EndTime
No Overlaps on StartTime and EndTime between records; StartTime always
before EndTime; all time ranges encompass only one day (no Start 11 PM,
end 2 AM type records)

SELECT I.*, S.ShiftName
FROM InventoryTable as I INNER JOIN ShiftTable as S
On I.TimeField Between S.StartTime and S.EndTime

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================



Show quoted text -

Thank you John. I appreciate your assistance, but I am still a little
confused. I have created the table, which is where I thought I should
begin with this. But the "Select" statement you wrote has lost
me....Am I to create a select query with the two tables (Inventory and
ShiftName) and put this in the criteria? Sorry, I've just never seen
a statement like this and
my exposure to queries has been very basic to this point.

 

[Opal]

No, what I showed would be the entire query. To do this, you would need
to open the query in SQL view. Menu: View >> SQL

Then you would need to type in the query string. If you aren't familiar
with this. Try the following

Make a new query
-- Add the new table and the Inventory table to the query
-- Select the fields you want to display
-- Drag from inventory.timefield to the startTime field of the new table
-- Add any where conditions you need/want
-- Select View: SQL from the menu

-- In the text view look for the text that starts with
INVoices INNER JOIN [YourNewTable] ON Invoices.TimeField =
YourNewTable.StartTime
-- Replace the = with Between
-- after StartTime, add " AND YourNewTable.EndTime" (no quotes)
-- Try to run the query. View: DataSheet

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

You need to calculate the shift. You could use something like the
following.
Field: Shift: IIF([TimeField] Between #00:06:00# and
#00:07:00#,"Morning, IIF([TimeField] Between #00:14:00# and
#00:17:45#,"Afternoon",IIF([TimeField] Between #00:22:00# and
#00:23:00#,"Night","Out-Of-Range")))
A better solution would be to add a table with the shift names and start
and stop times. Then you could use that in a query to get the shift
names. If the time ranges changed, then you could edit the table.
However, that is more complex to implement in a query since the query
cannot be constructed in the query grid and must be built in SQL view.
A simple version of that would be something like the following.
Assumptions:
Table: ShiftTable with 3 fields
ShiftName
StartTime
EndTime
No Overlaps on StartTime and EndTime between records; StartTime always
before EndTime; all time ranges encompass only one day (no Start 11 PM,
end 2 AM type records)
SELECT I.*, S.ShiftName
FROM InventoryTable as I INNER JOIN ShiftTable as S
On I.TimeField Between S.StartTime and S.EndTime
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi again, John,

Okay, so in SQL view I had:

FROM 1AInventoryTable INNER JOIN ShiftTable ON
[1AInventoryTable].txtTime = ShiftTable.StartTime

and per your instructions I changed it to:

FROM 1AInventoryTable INNER JOIN ShiftTable ON
[1AInventoryTable].txtTime Between ShiftTable.StartTime and
ShiftTable.EndTime

and I get an error: "Between operator without And in query expression
'[1AInventoryTable].txtTime Between ShiftTable.StartTime'.

 

[John Spencer]

Sometimes the between and will not work. So try

FROM 1AInventoryTable INNER JOIN ShiftTable
ON [1AInventoryTable].txtTime >= ShiftTable.StartTime and
[1AInventoryTable].txtTime <= ShiftTable.EndTime

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

No, what I showed would be the entire query. To do this, you would need
to open the query in SQL view. Menu: View >> SQL

Then you would need to type in the query string. If you aren't familiar
with this. Try the following

Make a new query
-- Add the new table and the Inventory table to the query
-- Select the fields you want to display
-- Drag from inventory.timefield to the startTime field of the new table
-- Add any where conditions you need/want
-- Select View: SQL from the menu

-- In the text view look for the text that starts with
INVoices INNER JOIN [YourNewTable] ON Invoices.TimeField =
YourNewTable.StartTime
-- Replace the = with Between
-- after StartTime, add " AND YourNewTable.EndTime" (no quotes)
-- Try to run the query. View: DataSheet

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

You need to calculate the shift. You could use something like the
following.
Field: Shift: IIF([TimeField] Between #00:06:00# and
#00:07:00#,"Morning, IIF([TimeField] Between #00:14:00# and
#00:17:45#,"Afternoon",IIF([TimeField] Between #00:22:00# and
#00:23:00#,"Night","Out-Of-Range")))
A better solution would be to add a table with the shift names and start
and stop times. Then you could use that in a query to get the shift
names. If the time ranges changed, then you could edit the table.
However, that is more complex to implement in a query since the query
cannot be constructed in the query grid and must be built in SQL view.
A simple version of that would be something like the following.
Assumptions:
Table: ShiftTable with 3 fields
ShiftName
StartTime
EndTime
No Overlaps on StartTime and EndTime between records; StartTime always
before EndTime; all time ranges encompass only one day (no Start 11 PM,
end 2 AM type records)
SELECT I.*, S.ShiftName
FROM InventoryTable as I INNER JOIN ShiftTable as S
On I.TimeField Between S.StartTime and S.EndTime
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi again, John,

Okay, so in SQL view I had:

FROM 1AInventoryTable INNER JOIN ShiftTable ON
[1AInventoryTable].txtTime = ShiftTable.StartTime

and per your instructions I changed it to:

FROM 1AInventoryTable INNER JOIN ShiftTable ON
[1AInventoryTable].txtTime Between ShiftTable.StartTime and
ShiftTable.EndTime

and I get an error: "Between operator without And in query expression
'[1AInventoryTable].txtTime Between ShiftTable.StartTime'.

 

[Opal]

Sometimes the between and will not work. So try

FROM 1AInventoryTable INNER JOIN ShiftTable
ON [1AInventoryTable].txtTime >= ShiftTable.StartTime and
[1AInventoryTable].txtTime <= ShiftTable.EndTime

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

No, what I showed would be the entire query. To do this, you would need
to open the query in SQL view. Menu: View >> SQL
Then you would need to type in the query string. If you aren't familiar
with this. Try the following
Make a new query
-- Add the new table and the Inventory table to the query
-- Select the fields you want to display
-- Drag from inventory.timefield to the startTime field of the new table
-- Add any where conditions you need/want
-- Select View: SQL from the menu
-- In the text view look for the text that starts with
INVoices INNER JOIN [YourNewTable] ON Invoices.TimeField =
YourNewTable.StartTime
-- Replace the = with Between
-- after StartTime, add " AND YourNewTable.EndTime" (no quotes)
-- Try to run the query. View: DataSheet
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Opal wrote:
You need to calculate the shift. You could use something like the
following.
Field: Shift: IIF([TimeField] Between #00:06:00# and
#00:07:00#,"Morning, IIF([TimeField] Between #00:14:00# and
#00:17:45#,"Afternoon",IIF([TimeField] Between #00:22:00# and
#00:23:00#,"Night","Out-Of-Range")))
A better solution would be to add a table with the shift names and start
and stop times. Then you could use that in a query to get the shift
names. If the time ranges changed, then you could edit the table.
However, that is more complex to implement in a query since the query
cannot be constructed in the query grid and must be built in SQL view.
A simple version of that would be something like the following.
Assumptions:
Table: ShiftTable with 3 fields
ShiftName
StartTime
EndTime
No Overlaps on StartTime and EndTime between records; StartTime always
before EndTime; all time ranges encompass only one day (no Start 11 PM,
end 2 AM type records)
SELECT I.*, S.ShiftName
FROM InventoryTable as I INNER JOIN ShiftTable as S
On I.TimeField Between S.StartTime and S.EndTime
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thank you, once again. I can now run the query. I put test data in
the table to cover all three shift inventory times and ran the query.
One row per shift. I ran the query and it returned all three
rows....I think I missing additional criteria here......
Sorry for being such a newbie......

 

[John Spencer]

I am sorry, I don't understand what your problem is at this point. You
should be getting three rows (or more if you are doing multiple dates).
Each shift would be on a separate row.

IF you want all three shifts on the same line, you need to use a cross-tab
query or you need to use three correlated subqueries in the SELECT clause of
your query.

Can you post an example of what you want and what you get. Also, copy and
paste the SQL statement you are using ( Menu bar -View: SQL)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Opal]

I am sorry, I don't understand what your problem is at this point. You
should be getting three rows (or more if you are doing multiple dates).
Each shift would be on a separate row.

IF you want all three shifts on the same line, you need to use a cross-tab
query or you need to use three correlated subqueries in the SELECT clause of
your query.

Can you post an example of what you want and what you get. Also, copy and
paste the SQL statement you are using ( Menu bar -View: SQL)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.






- Show quoted text -

Thank you John, I found another way around the problem that works.
I will be back the group soon, however, as I will need to create
calculations within another
query before I can run my report.....I hope you will be around if I
need more help. Thanks again!