Repeat orders - 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

Other Forums

MS Access

Repeat orders

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-06-28 : 07:05:12

Hi i've a query to design in access that is already built in excel.

Its for repeat orders,

What i've to do is check to see if the serial number is the same as the pervious one and also check if the material number is the same as the pervious one for each row in my table. If they are set a value to "yes" or "no"

There is a few other querys to build on top of this but i like to start with this one.

Any help would be great.
Thanks in advance.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-28 : 07:27:23

What do you mean by the previous one?
Is there an ID or datetime or something to order by?

select t.orderno , case when t2.serial = t.serial and t2.materialno = t.materialno then 'yes' else 'no' end
from tbl t
join tbl t2
on t2.date = (select max(date) from tbl t3 where t3.date < t.date)

sorry - access

select t.orderno , iif( t2.serial = t.serial and t2.materialno = t.materialno,'yes','no')
from tbl t
join tbl t2
on t2.date = (select max(date) from tbl t3 where t3.date < t.date)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-06-28 : 10:02:46

What i have is the table below sorted by date

Customer name Part no Date and (Value i enter in)

Jim 001 26/06/06 1
Sean 001 26/06/06 1
Jim 002 26/06/06 1
Jim 003 27/06/06 1

The excel formula is below.
What it is doing is comparing 1 row to the next so for C37663=C37664 its seeing if the customer name is the same as the next customer name and for H37663=H37664 its seeing if the part number is the same as the next part number, i have a value i enter in and this is were <$C$2 comes in if the ,(L37664-L37663) which are the date are < X value
then set to YES or NO

X value can be anything the user enters in 1,2,3,4 etc..
Example

=IF(OR(AND(C37663=C37664,H37663=H37664,(L37664-L37663)<$C$2),(AND(C37663=C37662,H37663=H37662,(L37663-L37662)<$C$2))),"Y","N")

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-28 : 10:44:13

Unfortunately in a relational database their is no concept of ordering in a table so "the next row" is a meaningless concept.

next row order by column x ascending is meaningful - although you might have to decide what to do with duplicates.
If you put an autonumber id on the table you could

select t.id , iif( t2.[Customer name] = t.[Customer name] and t2.[Part no] = t.[Part no],'yes','no')
from tbl t
join tbl t2
on t2.id = (select max(id) from tbl t3 where t3.id < t.id)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources