Query criteria not returning results - "Data type mismatch..."

Hi Kate,
I think (I hope) your [OnlyScheduling] table has FK rather than [LastName] on
which to Join.
[Store Detectives].[StoreDetectiveID] =[OnlyScheduling].[StoreDetective];

instead of:
[Store Detectives].[LastName] =[OnlyScheduling].[StoreDetective];



KateCee wrote:
Hi RG & Wayne,

I followed Wayne's suggestions to change the relationships, and RG's
suggestion to post my SQL for the troublesome query. I am now seeing one
record that I had started to enter on another table, but I am not able to
type anything in this query datasheet, I just get an error noise.

I made the changes in my relationships, hoping that helped some. Please
help! Any suggestions would be greatly appreciated, as this database is due
by Friday and I am nowhere near being able to deliver a functioning database.

SELECT [OnlyScheduling].[ScheduleID], [OnlyScheduling].[StoreDetective],
[OnlyScheduling].[Week-ending date], [OnlyScheduling].[SundayStart],
[OnlyScheduling].[SundayEnd], [OnlyScheduling].[SunStore],
[OnlyScheduling].[MondayStart], [OnlyScheduling].[MondayEnd],
[OnlyScheduling].[MonStore], [OnlyScheduling].[TuesdayStart],
[OnlyScheduling].[TuesdayEnd], [OnlyScheduling].[TuesStore],
[OnlyScheduling].[WednesdayStart], [OnlyScheduling].[WednesdayEnd],
[OnlyScheduling].[WedStore], [OnlyScheduling].[ThursdayStart],
[OnlyScheduling].[ThursdayEnd], [OnlyScheduling].[ThursStore],
[OnlyScheduling].[FridayStart], [OnlyScheduling].[FridayEnd],
[OnlyScheduling].[FriStore], [OnlyScheduling].[SaturdayStart],
[OnlyScheduling].[SaturdayEnd], [OnlyScheduling].[SatStore], [Store
Detectives].[StoreDetectiveID], [Store Detectives].[LastName], [Store
Detectives].[FirstName], [Store Detectives].[MiddleInitial], [Store
Detectives].[HomeStore], [Store Detectives].[FieldManager], [Store
Detectives].[Position]
FROM [Store Detectives] INNER JOIN OnlyScheduling ON [Store
Detectives].[LastName] =[OnlyScheduling].[StoreDetective];

Hi Kate,
It is not easy to tell where your issue lies. You should now be able to view
[quoted text clipped - 21 lines]
Thanks,
Kate

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200608/1

You are not going to like this - sorry

Can I suggest that you use an autonumber ID field for your table
StoreDetectives and then add a number field to the table OnlyScheduling.
Link these 2 field in the realtionship window.

Use this Parent/Child as the basis of your query.

Told ya you wouldn't like it BUT it really WILL be worth it in the end.

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



"KateCee" wrote:

Of course, here are a few examples:

[StoreDetectives].[LastName] is a "text" data type, display control "text
box" and contains employee's last names (smith, bouchard, cummings)

[OnlyScheduling].[StoreDetective] is a "text" data type, display control
"text box" and should contain the employee's last name as well (smith,
bouchard, cummings). This was how I was trying to make a relationship
between the StoreDetectives table and the OnlyScheduling table. Earlier
entries into this field are showing as #s, but I think that is b/c it was
previously a look-up column, which I then changed to a text box.

Thanks for your quick response and all your help thus far. This database
has been the biggest headache for a while now, and I just want to get it
right, it should not be this complicated!

Kate

"Wayne-I-M" wrote:

Can you give a few samples of the data that is in these fields

[Store Detectives].[LastName]
[OnlyScheduling].[StoreDetective]

Is [StoreDetective] a concencated field / autonumber / The same as LastName
etc
etc

If you're "sure" that these field "type" is OK try change this last section
of your SQL (to alter the join type) to

FROM [Store Detectives] RIGHT JOIN OnlyScheduling ON
[StoreDetectives].[LastName] =[OnlyScheduling].[StoreDetective];



--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

Noo, don't be sorry, these are good suggestions that I obviously did not
realize myself.

Just to clarify - I already have an auto# ID field for the StoreDetectives
table, so that is already established. But for adding a number field to
OnlyScheduling, what number would I be inputting? Would that just be a field
with a "number" data type? Is this field meant to match the autoID# in the
StoreDetectives table?

Please give me more details so I can do this right the first time, as I have
been making many mistakes throughout the construction of this database.

Thanks again,
Kate

"Wayne-I-M" wrote:

You are not going to like this - sorry

Can I suggest that you use an autonumber ID field for your table
StoreDetectives and then add a number field to the table OnlyScheduling.
Link these 2 field in the realtionship window.

Use this Parent/Child as the basis of your query.

Told ya you wouldn't like it BUT it really WILL be worth it in the end.

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



"KateCee" wrote:

Of course, here are a few examples:

[StoreDetectives].[LastName] is a "text" data type, display control "text
box" and contains employee's last names (smith, bouchard, cummings)

[OnlyScheduling].[StoreDetective] is a "text" data type, display control
"text box" and should contain the employee's last name as well (smith,
bouchard, cummings). This was how I was trying to make a relationship
between the StoreDetectives table and the OnlyScheduling table. Earlier
entries into this field are showing as #s, but I think that is b/c it was
previously a look-up column, which I then changed to a text box.

Thanks for your quick response and all your help thus far. This database
has been the biggest headache for a while now, and I just want to get it
right, it should not be this complicated!

Kate

"Wayne-I-M" wrote:

Can you give a few samples of the data that is in these fields

[Store Detectives].[LastName]
[OnlyScheduling].[StoreDetective]

Is [StoreDetective] a concencated field / autonumber / The same as LastName
etc
etc

If you're "sure" that these field "type" is OK try change this last section
of your SQL (to alter the join type) to

FROM [Store Detectives] RIGHT JOIN OnlyScheduling ON
[StoreDetectives].[LastName] =[OnlyScheduling].[StoreDetective];



--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

Thanks for your quick response. I changed my relationships to what you
suggested, as I originaly had [Store Detectives].[LastName]
=[OnlyScheduling].[StoreDetective], just what you had feared. I changed this
is the relationships window, but when I try to re-do the query of both tables
combined, I get an error, something like this:
"Cannot open datasheet view of query, problem could be that another use has
the table open in exclusive view" and when I OK that error and go into the
design view and try to flip over to the datasheet view, I get "data type
mismatch".

Also, what does FK stand for? I haven't heard that used yet, but I'm not
that experienced in Access (obviously).

Thanks again,
Kate

"ruralguy via AccessMonster.com" wrote:

Hi Kate,
I think (I hope) your [OnlyScheduling] table has FK rather than [LastName] on
which to Join.
[Store Detectives].[StoreDetectiveID] =[OnlyScheduling].[StoreDetective];

instead of:
[Store Detectives].[LastName] =[OnlyScheduling].[StoreDetective];



KateCee wrote:
Hi RG & Wayne,

I followed Wayne's suggestions to change the relationships, and RG's
suggestion to post my SQL for the troublesome query. I am now seeing one
record that I had started to enter on another table, but I am not able to
type anything in this query datasheet, I just get an error noise.

I made the changes in my relationships, hoping that helped some. Please
help! Any suggestions would be greatly appreciated, as this database is due
by Friday and I am nowhere near being able to deliver a functioning database.

SELECT [OnlyScheduling].[ScheduleID], [OnlyScheduling].[StoreDetective],
[OnlyScheduling].[Week-ending date], [OnlyScheduling].[SundayStart],
[OnlyScheduling].[SundayEnd], [OnlyScheduling].[SunStore],
[OnlyScheduling].[MondayStart], [OnlyScheduling].[MondayEnd],
[OnlyScheduling].[MonStore], [OnlyScheduling].[TuesdayStart],
[OnlyScheduling].[TuesdayEnd], [OnlyScheduling].[TuesStore],
[OnlyScheduling].[WednesdayStart], [OnlyScheduling].[WednesdayEnd],
[OnlyScheduling].[WedStore], [OnlyScheduling].[ThursdayStart],
[OnlyScheduling].[ThursdayEnd], [OnlyScheduling].[ThursStore],
[OnlyScheduling].[FridayStart], [OnlyScheduling].[FridayEnd],
[OnlyScheduling].[FriStore], [OnlyScheduling].[SaturdayStart],
[OnlyScheduling].[SaturdayEnd], [OnlyScheduling].[SatStore], [Store
Detectives].[StoreDetectiveID], [Store Detectives].[LastName], [Store
Detectives].[FirstName], [Store Detectives].[MiddleInitial], [Store
Detectives].[HomeStore], [Store Detectives].[FieldManager], [Store
Detectives].[Position]
FROM [Store Detectives] INNER JOIN OnlyScheduling ON [Store
Detectives].[LastName] =[OnlyScheduling].[StoreDetective];

Hi Kate,
It is not easy to tell where your issue lies. You should now be able to view
[quoted text clipped - 21 lines]
Thanks,
Kate

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200608/1

Kate,
The new field needs to be a long integer! Then fill it by hand and then set
up your relationship and turn on Referential Integrity.

KateCee wrote:
Noo, don't be sorry, these are good suggestions that I obviously did not
realize myself.

Just to clarify - I already have an auto# ID field for the StoreDetectives
table, so that is already established. But for adding a number field to
OnlyScheduling, what number would I be inputting? Would that just be a field
with a "number" data type? Is this field meant to match the autoID# in the
StoreDetectives table?

Please give me more details so I can do this right the first time, as I have
been making many mistakes throughout the construction of this database.

Thanks again,
Kate

You are not going to like this - sorry

[quoted text clipped - 38 lines]
FROM [Store Detectives] RIGHT JOIN OnlyScheduling ON
[StoreDetectives].[LastName] =[OnlyScheduling].[StoreDetective];

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200608/1

FK stands for ForeignKey and PK is PrimaryKey. The best way to relate tables is
to put one table's PK in the other table as a FK and have both keys as
AutoNumbers/LongIntegers. The Access can figure out the relations ship when you
tell it to relate the tables and it is easy to enforce RI (Referential
Integrity).

On Wed, 16 Aug 2006 14:14:02 -0700, KateCee
wrote:

Thanks for your quick response. I changed my relationships to what you
suggested, as I originaly had [Store Detectives].[LastName]
=[OnlyScheduling].[StoreDetective], just what you had feared. I changed this
is the relationships window, but when I try to re-do the query of both tables
combined, I get an error, something like this:
"Cannot open datasheet view of query, problem could be that another use has
the table open in exclusive view" and when I OK that error and go into the
design view and try to flip over to the datasheet view, I get "data type
mismatch".

Also, what does FK stand for? I haven't heard that used yet, but I'm not
that experienced in Access (obviously).

Thanks again,
Kate

"ruralguy via AccessMonster.com" wrote:

Hi Kate,
I think (I hope) your [OnlyScheduling] table has FK rather than [LastName] on
which to Join.
[Store Detectives].[StoreDetectiveID] =[OnlyScheduling].[StoreDetective];

instead of:
[Store Detectives].[LastName] =[OnlyScheduling].[StoreDetective];



KateCee wrote:
Hi RG & Wayne,

I followed Wayne's suggestions to change the relationships, and RG's
suggestion to post my SQL for the troublesome query. I am now seeing one
record that I had started to enter on another table, but I am not able to
type anything in this query datasheet, I just get an error noise.

I made the changes in my relationships, hoping that helped some. Please
help! Any suggestions would be greatly appreciated, as this database is due
by Friday and I am nowhere near being able to deliver a functioning database.

SELECT [OnlyScheduling].[ScheduleID], [OnlyScheduling].[StoreDetective],
[OnlyScheduling].[Week-ending date], [OnlyScheduling].[SundayStart],
[OnlyScheduling].[SundayEnd], [OnlyScheduling].[SunStore],
[OnlyScheduling].[MondayStart], [OnlyScheduling].[MondayEnd],
[OnlyScheduling].[MonStore], [OnlyScheduling].[TuesdayStart],
[OnlyScheduling].[TuesdayEnd], [OnlyScheduling].[TuesStore],
[OnlyScheduling].[WednesdayStart], [OnlyScheduling].[WednesdayEnd],
[OnlyScheduling].[WedStore], [OnlyScheduling].[ThursdayStart],
[OnlyScheduling].[ThursdayEnd], [OnlyScheduling].[ThursStore],
[OnlyScheduling].[FridayStart], [OnlyScheduling].[FridayEnd],
[OnlyScheduling].[FriStore], [OnlyScheduling].[SaturdayStart],
[OnlyScheduling].[SaturdayEnd], [OnlyScheduling].[SatStore], [Store
Detectives].[StoreDetectiveID], [Store Detectives].[LastName], [Store
Detectives].[FirstName], [Store Detectives].[MiddleInitial], [Store
Detectives].[HomeStore], [Store Detectives].[FieldManager], [Store
Detectives].[Position]
FROM [Store Detectives] INNER JOIN OnlyScheduling ON [Store
Detectives].[LastName] =[OnlyScheduling].[StoreDetective];

Hi Kate,
It is not easy to tell where your issue lies. You should now be able to view
[quoted text clipped - 21 lines]
Thanks,
Kate

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200608/1


_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

Thanks for your help RG and Wayne. I have been able to successfully run the
query, and I deleted other copies of the database, which was giving me the
"exclusive mode" error.

So far, so good. Now I just need to make the form and run reports.

I do have another question though. I need to run reports on the following
criteria:

StoreDetective schedules by FieldManager (5 Field Managers)
StoreDetective schedules by Store (100 stores)
StoreDetective schedules by District Manager (10 District Managers)
StoreDetective schedules by StoreDetective (100 detectives)

Any suggestions on how to make a format to pull these reports weekly? It
would be virtually impossible and very unadvisable for me to make individual
queries for each, but I have to make sure this is all established so database
operators may access this data through a few clicks.

Thanks, any other input would be greatly appreciated!

Kate


"RuralGuy" wrote:

FK stands for ForeignKey and PK is PrimaryKey. The best way to relate tables is
to put one table's PK in the other table as a FK and have both keys as
AutoNumbers/LongIntegers. The Access can figure out the relations ship when you
tell it to relate the tables and it is easy to enforce RI (Referential
Integrity).

On Wed, 16 Aug 2006 14:14:02 -0700, KateCee
wrote:

Thanks for your quick response. I changed my relationships to what you
suggested, as I originaly had [Store Detectives].[LastName]
=[OnlyScheduling].[StoreDetective], just what you had feared. I changed this
is the relationships window, but when I try to re-do the query of both tables
combined, I get an error, something like this:
"Cannot open datasheet view of query, problem could be that another use has
the table open in exclusive view" and when I OK that error and go into the
design view and try to flip over to the datasheet view, I get "data type
mismatch".

Also, what does FK stand for? I haven't heard that used yet, but I'm not
that experienced in Access (obviously).

Thanks again,
Kate

"ruralguy via AccessMonster.com" wrote:

Hi Kate,
I think (I hope) your [OnlyScheduling] table has FK rather than [LastName] on
which to Join.
[Store Detectives].[StoreDetectiveID] =[OnlyScheduling].[StoreDetective];

instead of:
[Store Detectives].[LastName] =[OnlyScheduling].[StoreDetective];



KateCee wrote:
Hi RG & Wayne,

I followed Wayne's suggestions to change the relationships, and RG's
suggestion to post my SQL for the troublesome query. I am now seeing one
record that I had started to enter on another table, but I am not able to
type anything in this query datasheet, I just get an error noise.

I made the changes in my relationships, hoping that helped some. Please
help! Any suggestions would be greatly appreciated, as this database is due
by Friday and I am nowhere near being able to deliver a functioning database.

SELECT [OnlyScheduling].[ScheduleID], [OnlyScheduling].[StoreDetective],
[OnlyScheduling].[Week-ending date], [OnlyScheduling].[SundayStart],
[OnlyScheduling].[SundayEnd], [OnlyScheduling].[SunStore],
[OnlyScheduling].[MondayStart], [OnlyScheduling].[MondayEnd],
[OnlyScheduling].[MonStore], [OnlyScheduling].[TuesdayStart],
[OnlyScheduling].[TuesdayEnd], [OnlyScheduling].[TuesStore],
[OnlyScheduling].[WednesdayStart], [OnlyScheduling].[WednesdayEnd],
[OnlyScheduling].[WedStore], [OnlyScheduling].[ThursdayStart],
[OnlyScheduling].[ThursdayEnd], [OnlyScheduling].[ThursStore],
[OnlyScheduling].[FridayStart], [OnlyScheduling].[FridayEnd],
[OnlyScheduling].[FriStore], [OnlyScheduling].[SaturdayStart],
[OnlyScheduling].[SaturdayEnd], [OnlyScheduling].[SatStore], [Store
Detectives].[StoreDetectiveID], [Store Detectives].[LastName], [Store
Detectives].[FirstName], [Store Detectives].[MiddleInitial], [Store
Detectives].[HomeStore], [Store Detectives].[FieldManager], [Store
Detectives].[Position]
FROM [Store Detectives] INNER JOIN OnlyScheduling ON [Store
Detectives].[LastName] =[OnlyScheduling].[StoreDetective];

Hi Kate,
It is not easy to tell where your issue lies. You should now be able to view
[quoted text clipped - 21 lines]
Thanks,
Kate

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200608/1


_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

KateCee,
I would suggest you start a new thread with your last question so others will
see it and jump in.

KateCee wrote:
Thanks for your help RG and Wayne. I have been able to successfully run the
query, and I deleted other copies of the database, which was giving me the
"exclusive mode" error.

So far, so good. Now I just need to make the form and run reports.

I do have another question though. I need to run reports on the following
criteria:

StoreDetective schedules by FieldManager (5 Field Managers)
StoreDetective schedules by Store (100 stores)
StoreDetective schedules by District Manager (10 District Managers)
StoreDetective schedules by StoreDetective (100 detectives)

Any suggestions on how to make a format to pull these reports weekly? It
would be virtually impossible and very unadvisable for me to make individual
queries for each, but I have to make sure this is all established so database
operators may access this data through a few clicks.

Thanks, any other input would be greatly appreciated!

Kate

FK stands for ForeignKey and PK is PrimaryKey. The best way to relate tables is
to put one table's PK in the other table as a FK and have both keys as
[quoted text clipped - 65 lines]
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200608/1

Thanks! I actually posted it in the reports category....waiting for responses.

Kate

"ruralguy via AccessMonster.com" wrote:

KateCee,
I would suggest you start a new thread with your last question so others will
see it and jump in.

KateCee wrote:
Thanks for your help RG and Wayne. I have been able to successfully run the
query, and I deleted other copies of the database, which was giving me the
"exclusive mode" error.

So far, so good. Now I just need to make the form and run reports.

I do have another question though. I need to run reports on the following
criteria:

StoreDetective schedules by FieldManager (5 Field Managers)
StoreDetective schedules by Store (100 stores)
StoreDetective schedules by District Manager (10 District Managers)
StoreDetective schedules by StoreDetective (100 detectives)

Any suggestions on how to make a format to pull these reports weekly? It
would be virtually impossible and very unadvisable for me to make individual
queries for each, but I have to make sure this is all established so database
operators may access this data through a few clicks.

Thanks, any other input would be greatly appreciated!

Kate

FK stands for ForeignKey and PK is PrimaryKey. The best way to relate tables is
to put one table's PK in the other table as a FK and have both keys as
[quoted text clipped - 65 lines]
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200608/1