SQL Help

[Rob]

Hi Guys!

I need some assistance on how to write a code or SQL for the following table:

Date Inv Trnx Seq Tag Date
20051205 0000149110 8113624673 1 S 12/6/2005
20051205 0000149110 8113624673 2 12/6/2005
20051205 0000149110 8113624673 3 E 12/7/2005
20051205 0000149110 8113624673 4 S 12/29/2005
20051205 0000149110 8113624673 5 12/29/2005
20051205 0000149110 8113624673 6 12/29/2005
20051205 0000149110 8113624673 7 E 12/29/2005

What I need to accomplish is to set the min date of each group as the final
date in the table.
Example: Seq 1,2,3 is the 1st group. Seq 4-7 belongs to the 2nd group.
Seq 1 (with “S†tag is the min date (12/6/2005)) that I want to apply to the
whole group (1st group, Seq 1,2,3). That goes the same to group 2 (Seq 4-7),
Seq 4 Tag “S†with min date 12/29/2005) which in this case, its already been
done.

As always, I will appreciate any thoughts you can share.

Rob

 

[Tim Ferguson]

What I need to accomplish is to set the min date of each group as the
final date in the table.
Example: Seq 1,2,3 is the 1st group. Seq 4-7 belongs to the 2nd group.

What we have here is a Major Design Problem. I can hardly imagine how you
would even tell what "group" a particular record belongs to: it's a very
complex SQL select with at least two subqueries.

Why not do things the easy way, and just put in a foreign key pointing at
the Groups table? At that point, your question collapses into a simple
MIN([SomeDate]) aggregate query.

Incidentally, your object naming strategy needs some attention. [Date] is
a reserved word in SQL and VBA and using it here will bite you later. It
seems you have two of them in the same table, too. [Tag] is a control
property name and will also likely cause some difficult bugs unless you
are very careful.

Hope that helps


Tim F

 

[Rob]

Hi Tim

thanks for your quick response.

The Field names here are just for illustration purposes. In real table,
they're named differently. As with the group, the Seq can vary from 1 to 20
and the S,E flags can be anywhere in between those Seq numbers. That's my
dilemna. I tried creating a group table and joined it to this table, but the
result is not favorable. Any more suggestions?

Thanks,
Rob

What I need to accomplish is to set the min date of each group as the
final date in the table.
Example: Seq 1,2,3 is the 1st group. Seq 4-7 belongs to the 2nd group.

What we have here is a Major Design Problem. I can hardly imagine how you
would even tell what "group" a particular record belongs to: it's a very
complex SQL select with at least two subqueries.

Why not do things the easy way, and just put in a foreign key pointing at
the Groups table? At that point, your question collapses into a simple
MIN([SomeDate]) aggregate query.

Incidentally, your object naming strategy needs some attention. [Date] is
a reserved word in SQL and VBA and using it here will bite you later. It
seems you have two of them in the same table, too. [Tag] is a control
property name and will also likely cause some difficult bugs unless you
are very careful.

Hope that helps


Tim F

 

[Tim Ferguson]

I tried creating a group table and joined it to this table, but the
result is not favorable. Any more suggestions?

Apart from Get The Design Right, no.


Sorry.


Tim F