Running diferent query with one command

[Charles via AccessMonster.com]

I would like to run queries with just one botton and a date dispalyed in a
form.
If Sunday March 09, 2008, is dispalyed I would like to click on a button and
run a query that will select emloyees that are scheduled to work on this day
of the week along with other pertinent information already preselected by
that query.
Currenlty I am using 7 diferrent buttons to run 7 differnrent append queries
but it is too confusing and I am sure there is an better way.

Thanks

Charles

 

[Duane Hookom]

Why 7 buttons and how is "a date displayed in a form"? How about providing
some background about what you want to do and what you are currently doing?

 

[Charles via AccessMonster.com]

Thanks for the quick follow up.
I have a table with all assignments. 7 days. "Sunday" "StartSun" "EndSun" I
have this for all seven days, Under the day of the week is the shift number
and employees may work a different shift each day with it's corrensponding
times. Presently I run a query for each day to generate a report listing
employees scheduled to work, shift number and times. This is also linked to a
work order system and it workls well I just want to simplify the assignment
part by running the query just by identifying the weekday by the date, the
date is entered by a pop up calender in the front form and I link the various
queries by using the following in the criteria or when I append the
information to the a master table storing all previous assignments "Forms!
[Operation]![TripDate] The queries add the assignments to a master table were
all records are stored and then I run query to generate the report t by using
the date the criteria or filter.

Thanks

Why 7 buttons and how is "a date displayed in a form"? How about providing
some background about what you want to do and what you are currently doing?

I would like to run queries with just one botton and a date dispalyed in a
form.

[quoted text clipped - 8 lines]

Charles

 

[Duane Hookom]

I'm sorry but I don't understand anything about your tables, data, or what
you are trying to accomplish. Could you share some table designs as well as
sample data and desired outcome?
--
Duane Hookom
Microsoft Access MVP

Thanks for the quick follow up.
I have a table with all assignments. 7 days. "Sunday" "StartSun" "EndSun" I
have this for all seven days, Under the day of the week is the shift number
and employees may work a different shift each day with it's corrensponding
times. Presently I run a query for each day to generate a report listing
employees scheduled to work, shift number and times. This is also linked to a
work order system and it workls well I just want to simplify the assignment
part by running the query just by identifying the weekday by the date, the
date is entered by a pop up calender in the front form and I link the various
queries by using the following in the criteria or when I append the
information to the a master table storing all previous assignments "Forms!
[Operation]![TripDate] The queries add the assignments to a master table were
all records are stored and then I run query to generate the report t by using
the date the criteria or filter.

Thanks

Why 7 buttons and how is "a date displayed in a form"? How about providing
some background about what you want to do and what you are currently doing?

I would like to run queries with just one botton and a date dispalyed in a
form.

[quoted text clipped - 8 lines]

Charles

 

[Charles via AccessMonster.com]

I will give it a try

ShiftTable:
ID# Shift#: RDO: Sunday: StartSun: EndSun: Monday:
StartMon: EndMon: Etc.
12345 001 WT 120 6:35 14:05 125
7:20 3:30

Name Table:
ID#: NameL: NameF : and other employees information in 12
fields;
12345 Jones Doe
On Sunday select ID Sunday Shift and times and RDO
Query result will look like this. Sorry I dont know how to use SQL

Forms![Operation]![TripDate]: 03/10/08 ID#: 12345 Shift:001 RDO: 120
Sunday: 120 StartSun: 6:35 EndSun 14:05

Each day I use a different uppend query for the above information and create
a master table that will store this information and run reports on who warked
what or who is scheduled to work selected by date. I use 7 different buttons
in the operation form where also the date is displayed.

Charles

I'm sorry but I don't understand anything about your tables, data, or what
you are trying to accomplish. Could you share some table designs as well as
sample data and desired outcome?

Thanks for the quick follow up.
I have a table with all assignments. 7 days. "Sunday" "StartSun" "EndSun" I

[quoted text clipped - 20 lines]

 

[Duane Hookom]

IMHO I wouldn't spend much time with this table structure. Rather than 7 sets
of repeating fields, I would create a table that would store this information
in 7 records.

You don't have to "know how to use SQL". You can use the query designer to
build your queries and then switch to the SQL view so you can provide us with
the SQL statement.

BTW: what does RDO mean? What type of values are you storing in the Sunday
and Monday fields?
--
Duane Hookom
Microsoft Access MVP

I will give it a try

ShiftTable:
ID# Shift#: RDO: Sunday: StartSun: EndSun: Monday:
StartMon: EndMon: Etc.
12345 001 WT 120 6:35 14:05 125
7:20 3:30

Name Table:
ID#: NameL: NameF : and other employees information in 12
fields;
12345 Jones Doe
On Sunday select ID Sunday Shift and times and RDO
Query result will look like this. Sorry I dont know how to use SQL

Forms![Operation]![TripDate]: 03/10/08 ID#: 12345 Shift:001 RDO: 120
Sunday: 120 StartSun: 6:35 EndSun 14:05

Each day I use a different uppend query for the above information and create
a master table that will store this information and run reports on who warked
what or who is scheduled to work selected by date. I use 7 different buttons
in the operation form where also the date is displayed.

Charles

I'm sorry but I don't understand anything about your tables, data, or what
you are trying to accomplish. Could you share some table designs as well as
sample data and desired outcome?

Thanks for the quick follow up.
I have a table with all assignments. 7 days. "Sunday" "StartSun" "EndSun" I

[quoted text clipped - 20 lines]

Charles

 

[Charles via AccessMonster.com]

The purpose for the first query is a simle way to combine last and first
names and used by many other reports.

SELECT Names.ID, [Last] & ',' & ' ' & [First] AS Names1, [First] & ' ' &
[Last] AS [Names], Names.License, Names.DOB, Names.Title, Names.Last, Names.
First, Names.[Seniority #], Names.DOH, [City] & ',' & ' ' & [State] & ' ' &
[Zip] AS AddrComb, Names.Address, Names.City, Names.State, Names.Zip, Names.
Status, Names.LicenseExpire, Names.Phone
FROM [Names];

RDO is an acronym for regular day off. Under the day of the week is the work
assignment number for that day and it may vary on some day. when an employee
is not scheduled then the field will state Off. The status fileld is for
other information that may include unscheduled time off such as sick, holiday,
personal day, vacation or a simple message to the employee "see GM"


INSERT INTO AssignmentsAll ( Seniority, Name, Status, DriverID, RDO,
StartTime, Block, EndTime, ScPayHrs, SDate )
SELECT NameComb.[Seniority #], NameComb.Names1, NameComb.Status, Assignments.
ID, Assignments.RDO, Assignments.MoStart, Assignments.Monday, Assignments.
MoEnd, Assignments.Hours, Forms!Operation!TripDate AS Expr1
FROM NameComb RIGHT JOIN Assignments ON NameComb.ID = Assignments.ID
ORDER BY Assignments.MoStart, Assignments.Monday;
Presently I run 7 queries (one each day) and I wonder if it can be done with
one or one command button based on the day of the week or date.

Thanks

Charles

IMHO I wouldn't spend much time with this table structure. Rather than 7 sets
of repeating fields, I would create a table that would store this information
in 7 records.

You don't have to "know how to use SQL". You can use the query designer to
build your queries and then switch to the SQL view so you can provide us with
the SQL statement.

BTW: what does RDO mean? What type of values are you storing in the Sunday
and Monday fields?

I will give it a try

[quoted text clipped - 29 lines]

 

[Duane Hookom]

As I stated earlier, I would normalize the Shift table or use a union query
to normalize it. You can then use a single append query.

--
Duane Hookom
Microsoft Access MVP

The purpose for the first query is a simle way to combine last and first
names and used by many other reports.

SELECT Names.ID, [Last] & ',' & ' ' & [First] AS Names1, [First] & ' ' &
[Last] AS [Names], Names.License, Names.DOB, Names.Title, Names.Last, Names.
First, Names.[Seniority #], Names.DOH, [City] & ',' & ' ' & [State] & ' ' &
[Zip] AS AddrComb, Names.Address, Names.City, Names.State, Names.Zip, Names.
Status, Names.LicenseExpire, Names.Phone
FROM [Names];

RDO is an acronym for regular day off. Under the day of the week is the work
assignment number for that day and it may vary on some day. when an employee
is not scheduled then the field will state Off. The status fileld is for
other information that may include unscheduled time off such as sick, holiday,
personal day, vacation or a simple message to the employee "see GM"


INSERT INTO AssignmentsAll ( Seniority, Name, Status, DriverID, RDO,
StartTime, Block, EndTime, ScPayHrs, SDate )
SELECT NameComb.[Seniority #], NameComb.Names1, NameComb.Status, Assignments.
ID, Assignments.RDO, Assignments.MoStart, Assignments.Monday, Assignments.
MoEnd, Assignments.Hours, Forms!Operation!TripDate AS Expr1
FROM NameComb RIGHT JOIN Assignments ON NameComb.ID = Assignments.ID
ORDER BY Assignments.MoStart, Assignments.Monday;
Presently I run 7 queries (one each day) and I wonder if it can be done with
one or one command button based on the day of the week or date.

Thanks

Charles

IMHO I wouldn't spend much time with this table structure. Rather than 7 sets
of repeating fields, I would create a table that would store this information
in 7 records.

You don't have to "know how to use SQL". You can use the query designer to
build your queries and then switch to the SQL view so you can provide us with
the SQL statement.

BTW: what does RDO mean? What type of values are you storing in the Sunday
and Monday fields?

I will give it a try

[quoted text clipped - 29 lines]

Charles

 

[quinto via AccessMonster.com]

I will give that a shot

Thank you very much

Charles

As I stated earlier, I would normalize the Shift table or use a union query
to normalize it. You can then use a single append query.

The purpose for the first query is a simle way to combine last and first
names and used by many other reports.

[quoted text clipped - 41 lines]