0

Sample data:

ORDER    SEQ    IN      OUT
310      1      Jan-20  Jan-22
310      2      Jan-22  Jan-23
207      1      Jan-23  Jan-26
207      2      Jan-26  Jan-27
207      3      Jan-27  Jan-29

I need to create a new table that makes ORDER a primary key and assigns each ORDER entry a new IN and OUT value by retrieving the new IN value from the lowest SEQ of that ORDER and the new OUT value from the greatest SEQ of that ORDER.

For example:

ORDER    IN      OUT
310      Jan-20  Jan-23
207      Jan-23  Jan-29

Any help would be appreciated

blondeonia
  • 9
  • 2
  • Have you tried to create a query that uses 'Totals' with GROUP BY on ORDER and then MIN for In and MAX for Out? That gives you the output you need. Then how do you want to create the new table? Manually? Using VBA? Using SQL? – Wayne G. Dunn Dec 18 '14 at 23:03
  • I will try that, thanks. I wanted to create the new table using SQL. I am confused, what does Totals refer to? – blondeonia Dec 18 '14 at 23:09
  • Here is an example of the SQL to get the results you want: SELECT tblOrder.Order, Min(tblOrder.InDate) AS MinOfInDate, Max(tblOrder.OutDate) AS MaxOfOutDate FROM tblOrder GROUP BY tblOrder.Order; – Wayne G. Dunn Dec 18 '14 at 23:30
  • Example of SQL to make the table (and ditto on the bad field names!!): strSQL = "CREATE TABLE tblOrderSum (OrderNo INT, InDate DATETIME, OutDate DATETIME, PRIMARY KEY (OrderNo))" CurrentProject.Connection.Execute strSQL – Wayne G. Dunn Dec 18 '14 at 23:38
  • Thank you, this worked perfectly! What is the proper way of indicating that this solved my issue? since these are comments (I'm new to SO) – blondeonia Dec 19 '14 at 00:01
  • I really don't know what the rules are regarding marking an answer. Quite often things are solved in just the comments so they never get an official answer. If you want, I think you could post your own answer explaining the steps you took to get it to work. Or I could post a summary of the steps. Whatever is fine by me. And thank you for responding and doing this right!! Too often you never see a response when someone finally gets it to work - leaving you to wonder if they gave up. – Wayne G. Dunn Dec 19 '14 at 00:12
  • I can see you already have an answer in the comments. I am deleting my answer – cha Dec 19 '14 at 00:14
  • cha your edits solved my issue as well, and @dunn I would rather you posted a summary as you could help better than I could, if that's still fine. I could cite it in the answer. – blondeonia Dec 19 '14 at 00:24

1 Answers1

0

You seem to want an aggregation query:

select [order], min([in]) as [in], max(out) as out
from sample
group by [order];

By the way, order and in are lousy names for columns because they are a SQL reserved word.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for letting me know its an aggregation query. I've changed the column names but now I get the following error: "Circular reference caused by alias 'IN1' in query definition's SELECT list." – blondeonia Dec 18 '14 at 23:54
  • Try naming the output aliases to something else. – Gordon Linoff Dec 19 '14 at 13:06