 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / February 2006SQL Help
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 > 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 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. [quoted text clipped - 17 lines] > > Tim F =?Utf-8?B?Um9i?= <Rob@discussions.microsoft.com> wrote in news:E6868B4F- 9FA9-43C4-B4A2-10448CC1BC8F@microsoft.com: > 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.