Query Between Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Between Dates

Author

Topic

pottspotts
Starting Member

9 Posts

Posted - 2007-10-23 : 01:45:47

I have two tables: Sales, Consigners

The Sales table includes: date, description, price, consigner
The Consignment table includes: lastday, consigner

I need a query that will display all rows in Sales assuming Sales.date is less than or equal to Consigner.lastday. The .consigner fields need to be joined.

I have this and it isn't working:

SELECT DISTINCT [Sales Data].co1, [Sales Data].amount, [Sales Data].description, Consignment_Data.date AS Expr1
FROM Consignment_Data INNER JOIN [Sales Data] ON Consignment_Data.co1 = [Sales Data].co1
WHERE ((([Sales_Data].[date])=[Consignment_Data].[date]));

This is for MS Access 2003. Can anyone help me? I would appreciate some advice, as my code isn't working at all

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 01:49:35

Moderator, PLEASE MOVE THIS POST TO PROPER MS ACCESS FORUM.

SELECT DISTINCT	[Sales Data].co1,
		[Sales Data].amount,
		[Sales Data].description,
		Consignment_Data.date AS Expr1
FROM		Consignment_Data
INNER JOIN	[Sales Data] ON Consignment_Data.co1 = [Sales Data].co1
WHERE		[Sales Data].Consigner = Consignment_Data.Consigner
		AND [Sales_Data].[date] <= Consignment_Data.[date]

E 12°55'05.25"
N 56°04'39.16"

pottspotts
Starting Member

9 Posts

Posted - 2007-10-23 : 01:55:34

You are a God among men, my friend.

pottspotts
Starting Member

9 Posts

Posted - 2007-10-23 : 02:06:45

I am asked to "enter parameter value" for consignment_date.date and [sales data].date.

The query will show all rows from sales with whatever number in input listed in the Expr1 column.

SELECT DISTINCT	[Sales Data].co1, [Sales Data].amount, [Sales Data].description, Consignment_Data.date AS Expr1
FROM Consignment_Data
INNER JOIN [Sales Data] ON Consignment_Data.co1 = [Sales Data].co1
WHERE [Sales Data].co1 = Consignment_Data.co1 AND [Sales_Data].[date] <= Consignment_Data.[date]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 02:10:17

All column names in your query NOT found in any referenced table or view are treated as parameters.

E 12°55'05.25"
N 56°04'39.16"

pottspotts
Starting Member

9 Posts

Posted - 2007-10-23 : 02:40:49

It appears to be working. There is an elusive glitch though.

It is omitting dates like 2/15/2007 but including dates like 10/13/2007

when the end date is set to 10/15/2007.

Am I storing my dates incorrectly?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 02:49:50

Probably.
If you are using DATETIME as datatype, the query should work.
But if you are using STRING/VARCHAR, you are sorting string, not dates, which gives you

1/1/2008 < 12/31/2000

E 12°55'05.25"
N 56°04'39.16"

pottspotts
Starting Member

9 Posts

Posted - 2007-10-23 : 20:45:53

Okay this seems to work fine. I am having another issue now.

This is a linked Excel file, and I have a 'primarykey' field with unique numbers in it. I cannot set it as a primary key in access though. I believe that a side effect of this problem is that duplicate entries (there are many of them in the Sales_Data table) are only displayed once.

What is the solution to this problem?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources