Help with query from FoxPro

[Shanin]

We have a timekeeping system in FoxPro and I linked to it with Access to be
able to view schedules, make reports, etc without having to pay for every
custom report we need. I set up this query below to pull up schedules by
employee:

SELECT tblDayofWeekList.DayNumber, tblDayofWeekList.DayofWeek, [LastName] &
", " & [FirstName] AS Employee, syjobcst.location, tkempsch.job,
tkempsch.department, TimeValue(Format([time_in],"@@\@")) AS StartTime,
TimeValue(Format([Expr1],"@@\@")) AS EndTime,
IIf([time_out]="2400","0000",[time_out]) AS Expr1, tkempsch.hours
FROM (MITCEmployeeList RIGHT JOIN (tkempsch INNER JOIN tblDayofWeekList ON
tkempsch.day_of_wk = tblDayofWeekList.DayNumber) ON MITCEmployeeList.employee
= tkempsch.employee) LEFT JOIN syjobcst ON tkempsch.job = syjobcst.job
WHERE (((tkempsch.date) Between #4/29/2007# And #5/5/2007#) AND
((MITCEmployeeList.EmployeeID)=[Forms]![MITCSchedulebyEmployee]![Combo0]))
ORDER BY tkempsch.date
WITH OWNERACCESS OPTION;


I would like to add one more thing which I've run into a kink with. The
line "WHERE (((tkempsch.date) Between #4/29/2007# And #5/5/2007#)" those
dates can actually be changed down the road in the timekeeping software. I
really don't want to have to come into the query each time and change them.
These dates are however stored in a table name tk_cntrl as Sch_from and
sch_to. If I try and change the date field to have Between sch_frm AND
sch_to, I get the ambiguous outer join error. Is there anyway I'd be able to
get that to pull this in since there is nothing in that table that links to
anything else.

 

[DAVID]

Please show the SQL that fails

(david)

 

[Shanin]

Sorry, should have done that to begin with. Here it is:

SELECT tblDayofWeekList.DayNumber, tblDayofWeekList.DayofWeek, [LastName] &
", " & [FirstName] AS Employee, syjobcst.location, tkempsch.job,
tkempsch.department, TimeValue(Format([time_in],"@@\@")) AS StartTime,
TimeValue(Format([Expr1],"@@\@")) AS EndTime,
IIf([time_out]="2400","0000",[time_out]) AS Expr1, tkempsch.hours
FROM tk_cntrl, (MITCEmployeeList RIGHT JOIN (tkempsch INNER JOIN
tblDayofWeekList ON tkempsch.day_of_wk = tblDayofWeekList.DayNumber) ON
MITCEmployeeList.employee = tkempsch.employee) LEFT JOIN syjobcst ON
tkempsch.job = syjobcst.job
WHERE (((tkempsch.date) Between [sch_from] And [sch_to]) AND
((MITCEmployeeList.EmployeeID)=[Forms]![MITCSchedulebyEmployee]![Combo0]))
ORDER BY tkempsch.date
WITH OWNERACCESS OPTION;

Please show the SQL that fails

(david)

We have a timekeeping system in FoxPro and I linked to it with Access to be
able to view schedules, make reports, etc without having to pay for every
custom report we need. I set up this query below to pull up schedules by
employee:

SELECT tblDayofWeekList.DayNumber, tblDayofWeekList.DayofWeek, [LastName] &
", " & [FirstName] AS Employee, syjobcst.location, tkempsch.job,
tkempsch.department, TimeValue(Format([time_in],"@@\@")) AS StartTime,
TimeValue(Format([Expr1],"@@\@")) AS EndTime,
IIf([time_out]="2400","0000",[time_out]) AS Expr1, tkempsch.hours
FROM (MITCEmployeeList RIGHT JOIN (tkempsch INNER JOIN tblDayofWeekList ON
tkempsch.day_of_wk = tblDayofWeekList.DayNumber) ON MITCEmployeeList.employee
= tkempsch.employee) LEFT JOIN syjobcst ON tkempsch.job = syjobcst.job
WHERE (((tkempsch.date) Between #4/29/2007# And #5/5/2007#) AND
((MITCEmployeeList.EmployeeID)=[Forms]![MITCSchedulebyEmployee]![Combo0]))
ORDER BY tkempsch.date
WITH OWNERACCESS OPTION;


I would like to add one more thing which I've run into a kink with. The
line "WHERE (((tkempsch.date) Between #4/29/2007# And #5/5/2007#)" those
dates can actually be changed down the road in the timekeeping software. I
really don't want to have to come into the query each time and change them.
These dates are however stored in a table name tk_cntrl as Sch_from and
sch_to. If I try and change the date field to have Between sch_frm AND
sch_to, I get the ambiguous outer join error. Is there anyway I'd be able to
get that to pull this in since there is nothing in that table that links to
anything else.

 

[Cindy Winegarden]

......> WHERE (((tkempsch.date) Between #4/29/2007# And #5/5/2007#) .....


I would like to add one more thing which I've run into a kink with. The
line "WHERE (((tkempsch.date) Between #4/29/2007# And #5/5/2007#)" those
dates can actually be changed down the road in the timekeeping software.
I
really don't want to have to come into the query each time and change
them.
These dates are however stored in a table name tk_cntrl as Sch_from and
sch_to.

Hi Shanin,

I don't quite have my brain around your query, especially how many rows
there are in tk_cntrl, but what about a join on "Between Sch_From and
Sch_to"?

Something like:

.... tkempsch Inner Join tk_cntrl On tkempsch.Date Between tk_cntrl.Sch_From
and tk_cntrl.Sch_To ....


--
Cindy Winegarden
(e-mail address removed)

VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx

 

[DAVID]

I see what you mean: Access has always had trouble
with cross joins.

Create a query on tk_ctrl, with a calculated field, with
a value of 1.

Then join to the query instead of joining to tk_ctrl.
Use a RIGHT, LEFT, or INNER join as required, joining
on the calculated field.

Or if tk_ctrl has any other field you can use as a dummy
join field, use that.

You can even join on one of the date fields, using an

= or <= join instead of an = join (only = joins can be

shown in the Access Query By Example grid). Also, >=
and <= joins might not optimize as well when connected.

Alternatively, create a base query from the other tables,
then do the cross join between the base query and tk_cntrl,
but that's not as good.

In either case, you want to avoid having a cross join
in the same query as an inner/outer join. The dummy
join is better because the cross join will also cause
problems if it is in the source query for another query.
Access/Jet optimizes through stacked queries, so if you
have a cross join, it needs to be at the top level.

When you design your own tables, put a dummy join field
in your control tables. I use something like a 'company
id' field: theoretically, it allows me to have multiple
control rows, one for each company, in practice there is
one company, and I use the ID as a dummy join field.

(david)

Sorry, should have done that to begin with. Here it is:

SELECT tblDayofWeekList.DayNumber, tblDayofWeekList.DayofWeek, [LastName] &
", " & [FirstName] AS Employee, syjobcst.location, tkempsch.job,
tkempsch.department, TimeValue(Format([time_in],"@@\@")) AS StartTime,
TimeValue(Format([Expr1],"@@\@")) AS EndTime,
IIf([time_out]="2400","0000",[time_out]) AS Expr1, tkempsch.hours
FROM tk_cntrl, (MITCEmployeeList RIGHT JOIN (tkempsch INNER JOIN
tblDayofWeekList ON tkempsch.day_of_wk = tblDayofWeekList.DayNumber) ON
MITCEmployeeList.employee = tkempsch.employee) LEFT JOIN syjobcst ON
tkempsch.job = syjobcst.job
WHERE (((tkempsch.date) Between [sch_from] And [sch_to]) AND
((MITCEmployeeList.EmployeeID)=[Forms]![MITCSchedulebyEmployee]![Combo0]))
ORDER BY tkempsch.date
WITH OWNERACCESS OPTION;

Please show the SQL that fails

(david)

We have a timekeeping system in FoxPro and I linked to it with Access to be
able to view schedules, make reports, etc without having to pay for every
custom report we need. I set up this query below to pull up schedules by
employee:

SELECT tblDayofWeekList.DayNumber, tblDayofWeekList.DayofWeek, [LastName] &
", " & [FirstName] AS Employee, syjobcst.location, tkempsch.job,
tkempsch.department, TimeValue(Format([time_in],"@@\@")) AS StartTime,
TimeValue(Format([Expr1],"@@\@")) AS EndTime,
IIf([time_out]="2400","0000",[time_out]) AS Expr1, tkempsch.hours
FROM (MITCEmployeeList RIGHT JOIN (tkempsch INNER JOIN tblDayofWeekList ON
tkempsch.day_of_wk = tblDayofWeekList.DayNumber) ON MITCEmployeeList.employee
= tkempsch.employee) LEFT JOIN syjobcst ON tkempsch.job = syjobcst.job
WHERE (((tkempsch.date) Between #4/29/2007# And #5/5/2007#) AND
((MITCEmployeeList.EmployeeID)=[Forms]![MITCSchedulebyEmployee]![Combo0]))
ORDER BY tkempsch.date
WITH OWNERACCESS OPTION;


I would like to add one more thing which I've run into a kink with. The
line "WHERE (((tkempsch.date) Between #4/29/2007# And #5/5/2007#)" those
dates can actually be changed down the road in the timekeeping software. I
really don't want to have to come into the query each time and change them.
These dates are however stored in a table name tk_cntrl as Sch_from and
sch_to. If I try and change the date field to have Between sch_frm AND
sch_to, I get the ambiguous outer join error. Is there anyway I'd be able to
get that to pull this in since there is nothing in that table that links to
anything else.

 

[Shanin]

There are easily over a hundred rows in the table tk_cntrl. That is a table
in the FoxPro database and it's their control file which stores parameters
and stuff for numerous sections which one happens to be the date range for
the master employee schedules.

 

[Shanin]

Thanks. I'll give that a try when I get back from vacation in a week and let
you know if it worked. I've run into several snags along the way, most have
been fixable, with trying to link to the FoxPro database just because of how
they stored their data and what they name some of their fields.

I see what you mean: Access has always had trouble
with cross joins.

Create a query on tk_ctrl, with a calculated field, with
a value of 1.

Then join to the query instead of joining to tk_ctrl.
Use a RIGHT, LEFT, or INNER join as required, joining
on the calculated field.

Or if tk_ctrl has any other field you can use as a dummy
join field, use that.

You can even join on one of the date fields, using an

= or <= join instead of an = join (only = joins can be

shown in the Access Query By Example grid). Also, >=
and <= joins might not optimize as well when connected.

Alternatively, create a base query from the other tables,
then do the cross join between the base query and tk_cntrl,
but that's not as good.

In either case, you want to avoid having a cross join
in the same query as an inner/outer join. The dummy
join is better because the cross join will also cause
problems if it is in the source query for another query.
Access/Jet optimizes through stacked queries, so if you
have a cross join, it needs to be at the top level.

When you design your own tables, put a dummy join field
in your control tables. I use something like a 'company
id' field: theoretically, it allows me to have multiple
control rows, one for each company, in practice there is
one company, and I use the ID as a dummy join field.

(david)

Sorry, should have done that to begin with. Here it is:

SELECT tblDayofWeekList.DayNumber, tblDayofWeekList.DayofWeek, [LastName] &
", " & [FirstName] AS Employee, syjobcst.location, tkempsch.job,
tkempsch.department, TimeValue(Format([time_in],"@@\@")) AS StartTime,
TimeValue(Format([Expr1],"@@\@")) AS EndTime,
IIf([time_out]="2400","0000",[time_out]) AS Expr1, tkempsch.hours
FROM tk_cntrl, (MITCEmployeeList RIGHT JOIN (tkempsch INNER JOIN
tblDayofWeekList ON tkempsch.day_of_wk = tblDayofWeekList.DayNumber) ON
MITCEmployeeList.employee = tkempsch.employee) LEFT JOIN syjobcst ON
tkempsch.job = syjobcst.job
WHERE (((tkempsch.date) Between [sch_from] And [sch_to]) AND
((MITCEmployeeList.EmployeeID)=[Forms]![MITCSchedulebyEmployee]![Combo0]))
ORDER BY tkempsch.date
WITH OWNERACCESS OPTION;

Please show the SQL that fails

(david)


Shanin wrote:
We have a timekeeping system in FoxPro and I linked to it with Access to be
able to view schedules, make reports, etc without having to pay for every
custom report we need. I set up this query below to pull up schedules by
employee:

SELECT tblDayofWeekList.DayNumber, tblDayofWeekList.DayofWeek, [LastName] &
", " & [FirstName] AS Employee, syjobcst.location, tkempsch.job,
tkempsch.department, TimeValue(Format([time_in],"@@\@")) AS StartTime,
TimeValue(Format([Expr1],"@@\@")) AS EndTime,
IIf([time_out]="2400","0000",[time_out]) AS Expr1, tkempsch.hours
FROM (MITCEmployeeList RIGHT JOIN (tkempsch INNER JOIN tblDayofWeekList ON
tkempsch.day_of_wk = tblDayofWeekList.DayNumber) ON MITCEmployeeList.employee
= tkempsch.employee) LEFT JOIN syjobcst ON tkempsch.job = syjobcst.job
WHERE (((tkempsch.date) Between #4/29/2007# And #5/5/2007#) AND
((MITCEmployeeList.EmployeeID)=[Forms]![MITCSchedulebyEmployee]![Combo0]))
ORDER BY tkempsch.date
WITH OWNERACCESS OPTION;


I would like to add one more thing which I've run into a kink with. The
line "WHERE (((tkempsch.date) Between #4/29/2007# And #5/5/2007#)" those
dates can actually be changed down the road in the timekeeping software. I
really don't want to have to come into the query each time and change them.
These dates are however stored in a table name tk_cntrl as Sch_from and
sch_to. If I try and change the date field to have Between sch_frm AND
sch_to, I get the ambiguous outer join error. Is there anyway I'd be able to
get that to pull this in since there is nothing in that table that links to
anything else.

 

[Shanin]

Well I did part of what you said, the second part. I tried to make a query
as you said but then realized that all of the tables with the main data were
all from the FoxPro so I couldn't add a field to them to link to it. I was
using a query that pulled out employee name information as part of this query
and added a field that stays the same in the tk_cntrl table and linked those
two. This now allows me to use the field name of the date range so that if I
change that in the FoxPro down the road, it will automatically pull that up.

Thanks for the help

I see what you mean: Access has always had trouble
with cross joins.

Create a query on tk_ctrl, with a calculated field, with
a value of 1.

Then join to the query instead of joining to tk_ctrl.
Use a RIGHT, LEFT, or INNER join as required, joining
on the calculated field.

Or if tk_ctrl has any other field you can use as a dummy
join field, use that.

You can even join on one of the date fields, using an

= or <= join instead of an = join (only = joins can be

shown in the Access Query By Example grid). Also, >=
and <= joins might not optimize as well when connected.

Alternatively, create a base query from the other tables,
then do the cross join between the base query and tk_cntrl,
but that's not as good.

In either case, you want to avoid having a cross join
in the same query as an inner/outer join. The dummy
join is better because the cross join will also cause
problems if it is in the source query for another query.
Access/Jet optimizes through stacked queries, so if you
have a cross join, it needs to be at the top level.

When you design your own tables, put a dummy join field
in your control tables. I use something like a 'company
id' field: theoretically, it allows me to have multiple
control rows, one for each company, in practice there is
one company, and I use the ID as a dummy join field.

(david)

Sorry, should have done that to begin with. Here it is:

SELECT tblDayofWeekList.DayNumber, tblDayofWeekList.DayofWeek, [LastName] &
", " & [FirstName] AS Employee, syjobcst.location, tkempsch.job,
tkempsch.department, TimeValue(Format([time_in],"@@\@")) AS StartTime,
TimeValue(Format([Expr1],"@@\@")) AS EndTime,
IIf([time_out]="2400","0000",[time_out]) AS Expr1, tkempsch.hours
FROM tk_cntrl, (MITCEmployeeList RIGHT JOIN (tkempsch INNER JOIN
tblDayofWeekList ON tkempsch.day_of_wk = tblDayofWeekList.DayNumber) ON
MITCEmployeeList.employee = tkempsch.employee) LEFT JOIN syjobcst ON
tkempsch.job = syjobcst.job
WHERE (((tkempsch.date) Between [sch_from] And [sch_to]) AND
((MITCEmployeeList.EmployeeID)=[Forms]![MITCSchedulebyEmployee]![Combo0]))
ORDER BY tkempsch.date
WITH OWNERACCESS OPTION;

Please show the SQL that fails

(david)


Shanin wrote:
We have a timekeeping system in FoxPro and I linked to it with Access to be
able to view schedules, make reports, etc without having to pay for every
custom report we need. I set up this query below to pull up schedules by
employee:

SELECT tblDayofWeekList.DayNumber, tblDayofWeekList.DayofWeek, [LastName] &
", " & [FirstName] AS Employee, syjobcst.location, tkempsch.job,
tkempsch.department, TimeValue(Format([time_in],"@@\@")) AS StartTime,
TimeValue(Format([Expr1],"@@\@")) AS EndTime,
IIf([time_out]="2400","0000",[time_out]) AS Expr1, tkempsch.hours
FROM (MITCEmployeeList RIGHT JOIN (tkempsch INNER JOIN tblDayofWeekList ON
tkempsch.day_of_wk = tblDayofWeekList.DayNumber) ON MITCEmployeeList.employee
= tkempsch.employee) LEFT JOIN syjobcst ON tkempsch.job = syjobcst.job
WHERE (((tkempsch.date) Between #4/29/2007# And #5/5/2007#) AND
((MITCEmployeeList.EmployeeID)=[Forms]![MITCSchedulebyEmployee]![Combo0]))
ORDER BY tkempsch.date
WITH OWNERACCESS OPTION;


I would like to add one more thing which I've run into a kink with. The
line "WHERE (((tkempsch.date) Between #4/29/2007# And #5/5/2007#)" those
dates can actually be changed down the road in the timekeeping software. I
really don't want to have to come into the query each time and change them.
These dates are however stored in a table name tk_cntrl as Sch_from and
sch_to. If I try and change the date field to have Between sch_frm AND
sch_to, I get the ambiguous outer join error. Is there anyway I'd be able to
get that to pull this in since there is nothing in that table that links to
anything else.

 

[DAVID]

Shanin wrote:

Glad you got it sorted. Thanks for the feedback :~)

(david)