Find Missing Records

[Flanders]

Hello. Thanks in advance for the help!



I have a table containing Three Columns

Price.PN
Price.Date
Price.Price

For a given future Date, some PN’s (Part Numbers) have a price, and some do not. I want to extract all of the PN’s that don’t have Prices on a future Date.

I’ve tried creating a table with all future Dates, and trying to do some outerjoins to it but I haven’t had any success.

Any help would be appreciated. Sample data are attached.


Thanks,

Jeremy

[RuralGuy]

So you want to
SELECT PN
FROM Price
WHERE IsNull([Price ]) AND Date = #[YourDate]#
Correct?
BTW, having a field in a table with the same name as the table is a lousy idea and Date is a reserved word.

[Flanders]

Thanks. Actually I found another way around it, but I'll try your way, too. My way is pretty clunky. I definitely see your point about the naming conventions I used. I'm confusing myself to no end. But hey, this is my first ever Database, and I made sure to make plenty of mistakes so I can learn from them!

I ended up Creating a table with all of the Dates [Date Table], and then I created a compound field with Date and Price, and then did a Where not exits command to pull the info

Query1: DatePart
SELECT [Date Table].Date, [Date Table].[Date] & [Parts].[PN] AS DT_Tag, *
FROM [Date Table], Parts;

Query2atePrice
SELECT Price.Date, Price.PN, Price.Price, [Price].[Date] & [Price].[PN] AS PT_Tag, *
FROM Price;


Query3:Missing Prices
SELECT Date, PN
FROM DatePart
WHERE Not Exists (select * from DatePrice where Dateprice.PT_Tag = DatePart.DT_Tag);



It's the long way around, but it works :-)

Thanks for the help!

[Flanders]

I think the method you proposed assumes that there are records with no entries for price. My problem is that there isn't even a record entry. The record in the Price Table doesn't exist unless there IS a price.

[RuralGuy]

It sounds like you solved your own problem. That's great!

[Flanders]

Yes, but I have only about 85% the hair I had before

[RuralGuy]

85% is much better than none!