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 |
#1
|
|||
|
|||
greater than or less than zero problems
I'm having this weird issue, or maybe i'm just missing something. but I
have a table like so... (edited,shorter version of table) ID CLASS GROUP DIFF WEEK 62 759 MPT 0 WEEK 1 83 769 AB0 0 WEEK 1 82 769 AB0 0 WEEK 1 84 769 AB0 0 WEEK 1 23 750 M50 0 WEEK 1 50 759 MPT -10 WEEK 1 19 750 MT2 -10 WEEK 1 18 750 M16 -10 WEEK 1 15 750 M16 -10 WEEK 1 14 750 M16 -10 WEEK 1 10 617 CT5 -10 WEEK 1 9 617 CT5 -10 WEEK 1 7 612 BT5 -10 WEEK 1 39 757 MR8 11 WEEK 1 54 759 MPT 11 WEEK 1 I created 2 separate queries. One where it counts where the diff is 0 and the other where the diff is 0. For some reason when I count 0 it includes all of the negatives plus all of the positives(-10,11) and when I run the query for 0 it returns nothing. I also tried using the filter to sort the table to make sure it wasn't a data error and it also did the same thing. What am I missing? Here's my queries SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS EARLY FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT YR].WEEK = WEEKS.Week WHERE ((([Shipments CURRENT YR].DIFF)"0")) GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK ORDER BY WEEKS.[W View Order]; SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS LATE FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT YR].WEEK = WEEKS.Week WHERE ((([Shipments CURRENT YR].DIFF)"0")) GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK ORDER BY WEEKS.[W View Order]; |
#2
|
|||
|
|||
greater than or less than zero problems
Hi
Change your WHERE ((([Shipments CURRENT YR].DIFF)"0")) to WHERE ((([Shipments CURRENT YR].DIFF)0)) -- Wayne Manchester, England. Enjoy whatever it is you do " wrote: I'm having this weird issue, or maybe i'm just missing something. but I have a table like so... (edited,shorter version of table) ID CLASS GROUP DIFF WEEK 62 759 MPT 0 WEEK 1 83 769 AB0 0 WEEK 1 82 769 AB0 0 WEEK 1 84 769 AB0 0 WEEK 1 23 750 M50 0 WEEK 1 50 759 MPT -10 WEEK 1 19 750 MT2 -10 WEEK 1 18 750 M16 -10 WEEK 1 15 750 M16 -10 WEEK 1 14 750 M16 -10 WEEK 1 10 617 CT5 -10 WEEK 1 9 617 CT5 -10 WEEK 1 7 612 BT5 -10 WEEK 1 39 757 MR8 11 WEEK 1 54 759 MPT 11 WEEK 1 I created 2 separate queries. One where it counts where the diff is 0 and the other where the diff is 0. For some reason when I count 0 it includes all of the negatives plus all of the positives(-10,11) and when I run the query for 0 it returns nothing. I also tried using the filter to sort the table to make sure it wasn't a data error and it also did the same thing. What am I missing? Here's my queries SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS EARLY FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT YR].WEEK = WEEKS.Week WHERE ((([Shipments CURRENT YR].DIFF)"0")) GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK ORDER BY WEEKS.[W View Order]; SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS LATE FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT YR].WEEK = WEEKS.Week WHERE ((([Shipments CURRENT YR].DIFF)"0")) GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK ORDER BY WEEKS.[W View Order]; |
#3
|
|||
|
|||
greater than or less than zero problems
That makes sense and trust me i've tried it. But everytime I make the
change for some reason, access put the quotes right back Wayne-I-M wrote: Hi Change your WHERE ((([Shipments CURRENT YR].DIFF)"0")) to WHERE ((([Shipments CURRENT YR].DIFF)0)) -- Wayne Manchester, England. Enjoy whatever it is you do " wrote: I'm having this weird issue, or maybe i'm just missing something. but I have a table like so... (edited,shorter version of table) ID CLASS GROUP DIFF WEEK 62 759 MPT 0 WEEK 1 83 769 AB0 0 WEEK 1 82 769 AB0 0 WEEK 1 84 769 AB0 0 WEEK 1 23 750 M50 0 WEEK 1 50 759 MPT -10 WEEK 1 19 750 MT2 -10 WEEK 1 18 750 M16 -10 WEEK 1 15 750 M16 -10 WEEK 1 14 750 M16 -10 WEEK 1 10 617 CT5 -10 WEEK 1 9 617 CT5 -10 WEEK 1 7 612 BT5 -10 WEEK 1 39 757 MR8 11 WEEK 1 54 759 MPT 11 WEEK 1 I created 2 separate queries. One where it counts where the diff is 0 and the other where the diff is 0. For some reason when I count 0 it includes all of the negatives plus all of the positives(-10,11) and when I run the query for 0 it returns nothing. I also tried using the filter to sort the table to make sure it wasn't a data error and it also did the same thing. What am I missing? Here's my queries SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS EARLY FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT YR].WEEK = WEEKS.Week WHERE ((([Shipments CURRENT YR].DIFF)"0")) GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK ORDER BY WEEKS.[W View Order]; SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS LATE FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT YR].WEEK = WEEKS.Week WHERE ((([Shipments CURRENT YR].DIFF)"0")) GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK ORDER BY WEEKS.[W View Order]; |
#4
|
|||
|
|||
greater than or less than zero problems
Have you gone back to the table and checked the field format is a number ?
-- Wayne Manchester, England. Enjoy whatever it is you do " wrote: That makes sense and trust me i've tried it. But everytime I make the change for some reason, access put the quotes right back Wayne-I-M wrote: Hi Change your WHERE ((([Shipments CURRENT YR].DIFF)"0")) to WHERE ((([Shipments CURRENT YR].DIFF)0)) -- Wayne Manchester, England. Enjoy whatever it is you do " wrote: I'm having this weird issue, or maybe i'm just missing something. but I have a table like so... (edited,shorter version of table) ID CLASS GROUP DIFF WEEK 62 759 MPT 0 WEEK 1 83 769 AB0 0 WEEK 1 82 769 AB0 0 WEEK 1 84 769 AB0 0 WEEK 1 23 750 M50 0 WEEK 1 50 759 MPT -10 WEEK 1 19 750 MT2 -10 WEEK 1 18 750 M16 -10 WEEK 1 15 750 M16 -10 WEEK 1 14 750 M16 -10 WEEK 1 10 617 CT5 -10 WEEK 1 9 617 CT5 -10 WEEK 1 7 612 BT5 -10 WEEK 1 39 757 MR8 11 WEEK 1 54 759 MPT 11 WEEK 1 I created 2 separate queries. One where it counts where the diff is 0 and the other where the diff is 0. For some reason when I count 0 it includes all of the negatives plus all of the positives(-10,11) and when I run the query for 0 it returns nothing. I also tried using the filter to sort the table to make sure it wasn't a data error and it also did the same thing. What am I missing? Here's my queries SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS EARLY FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT YR].WEEK = WEEKS.Week WHERE ((([Shipments CURRENT YR].DIFF)"0")) GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK ORDER BY WEEKS.[W View Order]; SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS LATE FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT YR].WEEK = WEEKS.Week WHERE ((([Shipments CURRENT YR].DIFF)"0")) GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK ORDER BY WEEKS.[W View Order]; |
#5
|
|||
|
|||
greater than or less than zero problems
I was working with someone else on this and she was the creator of the
table. i had her go in and check and that was the problem. Something so simple! Thanks Wayne-I-M wrote: Have you gone back to the table and checked the field format is a number ? -- Wayne Manchester, England. Enjoy whatever it is you do " wrote: That makes sense and trust me i've tried it. But everytime I make the change for some reason, access put the quotes right back Wayne-I-M wrote: Hi Change your WHERE ((([Shipments CURRENT YR].DIFF)"0")) to WHERE ((([Shipments CURRENT YR].DIFF)0)) -- Wayne Manchester, England. Enjoy whatever it is you do " wrote: I'm having this weird issue, or maybe i'm just missing something. but I have a table like so... (edited,shorter version of table) ID CLASS GROUP DIFF WEEK 62 759 MPT 0 WEEK 1 83 769 AB0 0 WEEK 1 82 769 AB0 0 WEEK 1 84 769 AB0 0 WEEK 1 23 750 M50 0 WEEK 1 50 759 MPT -10 WEEK 1 19 750 MT2 -10 WEEK 1 18 750 M16 -10 WEEK 1 15 750 M16 -10 WEEK 1 14 750 M16 -10 WEEK 1 10 617 CT5 -10 WEEK 1 9 617 CT5 -10 WEEK 1 7 612 BT5 -10 WEEK 1 39 757 MR8 11 WEEK 1 54 759 MPT 11 WEEK 1 I created 2 separate queries. One where it counts where the diff is 0 and the other where the diff is 0. For some reason when I count 0 it includes all of the negatives plus all of the positives(-10,11) and when I run the query for 0 it returns nothing. I also tried using the filter to sort the table to make sure it wasn't a data error and it also did the same thing. What am I missing? Here's my queries SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS EARLY FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT YR].WEEK = WEEKS.Week WHERE ((([Shipments CURRENT YR].DIFF)"0")) GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK ORDER BY WEEKS.[W View Order]; SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS LATE FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT YR].WEEK = WEEKS.Week WHERE ((([Shipments CURRENT YR].DIFF)"0")) GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK ORDER BY WEEKS.[W View Order]; |
Thread Tools | |
Display Modes | |
|
|