Help I have gone brain dead

I have create a table called production. Where production information is entered on each shift for each day (3 shifts). When I first created the table, there was no need to track equipment without production. Now it is!! What I need to do is be able to query the table by line, shift and date for a date range and determine which lines, shifts and date are missing production data. The machines are numbered from 2 to 12 and the column name is LineNum. I have columns named Production, Shift, and EntryDate. I know that I am probably going to feel stupid when I see the fix, but for the life of me, I just don't see how to do it without recordset and then processing it in vbscript.
Thanks in advance,
LeeLee, I'm not sure if I eally understand the problem, but it sounds like the following

SELECT LineNum, Shift, EntryDate
FROM production
WHERE Production IS NULL
AND EntryDate BETWEEN @.begindate AND @.enddate

@.begindate and @.enddate represent the date range you're searching for. This seems awfully simple, though, perhaps I'm misunderstanding the problem?|||If you can, post here the tables structure in order for us to have a good understanding of your situation.

ionut|||I keep playing until I found something that give me an "okay" way of getting the information. I created a table called NumLine with all possible lines in it then joined the two tables. This method returns me data (count) as long as the line has ran at least one shift. The problem is if the line does not run at least one shift, I will not know it. My manager said that he would deal with this short term.

SELECT DISTINCT Prod.ProDate, Prod.Line, Max(Prod.Shift) AS MaxOfShift, Count(Prod.Shift) AS CountOfShift, Prod.ProDate, Prod.Dept

FROM Prod RIGHT JOIN tblNumLine ON Prod.Line = tblNumLine.NumLine

GROUP BY Prod.ProDate, Prod.Line, Prod.ProDate, Prod.Dept, tblNumLine.NumLine

HAVING (((Prod.ProDate) Between 'MMColParamBeg' And 'MMColParamEnd') AND ((Count(Prod.Shift))<3) ))

tblNumLine
NumLine

tblProd
LineNum
Shift
Production
EntryDate

Thanks,
Lee|||Yes! When I thought over what I had accomplished by creating a new table and the structure of the query, end result NOTHING! I could have accomplished the same with a simple Select and Count(Shift) Where Count(Shift) < 3 Group by Shift. What I need is to be able to return missing production even if it is all 3 shifts.

Thanks to all for your patience with an knucklehead,
Lee

Help comparing 01234 with 1234 (with complications)

I have to join on two fields, both are of type char(8), but one is entered by the application so it maintains leading 0's while the other is hand entered, so there are no leading 0's (and we're never going to get our users to ALWAYS enter leading 0's).

So, since they're numbers anyway, I figured I would just convert them both to integers and join on that as in:

...CAST(ord_no1 as integer) = CAST(ord_no2 as integer)

Which works :), BUT...

Since the ord_no field is actually a char(8) field, users can enter stuff other than numbers in it. Is there a way that I can use CAST as in the above, but let it ignore values that can not be converted? If it's not all numberals, it won't match anyway so I don't need to worry about them, however, when joining on that field, it has to look at all the records and perform the calculation and it's failing on a handful of them.

Any help would be greatly appreciated, thank you.Did we get past 1st normal form?

:D

SELECT ord_no1, ord_no2
FROM myTable99
WHERE ISNUMERIC(ord_no1) = 1 AND ISNUMERIC(ord_no2) = 1|||How can I either:

1. Convert to Integer, but gracefully ignore values that don't convert

OR

2. Convert to something like varchar or something that will let me join on '01234' and '1234 '

Thank you.|||USE Northwind
GO

SET NOCOUNT OFF
CREATE TABLE myTable99(ord_no1 char(8), ord_no2 char(8))
GO

INSERT INTO myTable99(ord_no1, ord_no2)
SELECT '01234','1234' UNION ALL
SELECT 'X1234','1234' UNION ALL
SELECT '6789','6789' UNION ALL
SELECT 'Brett','Brett'

SELECT *
FROM ( SELECT *
FROM myTable99
WHERE ISNUMERIC(ord_no1) = 1 AND ISNUMERIC(ord_no2) = 1) AS XXX
WHERE CONVERT(int,ord_no1) = CONVERT(int,ord_no2)
UNION ALL
SELECT *
FROM myTable99
WHERE (ISNUMERIC(ord_no1) = 0 OR ISNUMERIC(ord_no2) = 0)
AND ord_no1 = ord_no2
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||THANK YOU!!!!

I can't believe I'm doing this because I have a lot of issues with my accounting systems DB design, but it is in normal form.

When performing a warehouse transfer, a inventory issuance is entered for the warehouse the product is shipping from with ord_no = 1000 and doc_ord_no = 1000.

Then, an inventory receipt is entered for the warehouse the product is shipping to with ord_no = 1001 and doc_ord_no = 1000 which relates that receipt with the issuance.

I am trying to create a transfers report so I can list all the issuances and then do a join between ord_no and doc_ord_no (limiting the second table to only receipts) to show which transfers have arrived and which are in transit.

Thank you again for your help.