If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Query criteria not returning results - "Data type mismatch..."
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. |
#13
|
|||
|
|||
Query criteria not returning results - "Data type mismatch..."
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. |
#14
|
|||
|
|||
Query criteria not returning results - "Data type mismatch..."
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 |
#15
|
|||
|
|||
Query criteria not returning results - "Data type mismatch..."
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 |
#16
|
|||
|
|||
Query criteria not returning results - "Data type mismatch..."
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. |
#17
|
|||
|
|||
Query criteria not returning results - "Data type mismatch..."
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. |
#18
|
|||
|
|||
Query criteria not returning results - "Data type mismatch..."
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 |
#19
|
|||
|
|||
Query criteria not returning results - "Data type mismatch..."
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 |
|
Thread Tools | |
Display Modes | |
|
|