Make Table Query Question 2

[brahmabull525]

Under thread701-997176, a helpful person provided me with the following to answer an Access question that I had:

SELECT A.ID, A.SaleDate-Max(B.SaleDate) AS Diff
FROM tblSaleDate A INNER JOIN tblSaleDate B
ON A.ID=B.ID AND B.SaleDate<A.SaleDate
GROUP BY A.ID, A.SaleDate

Unfortunately, I have a new, related question. What do I need to add to this in order to create make a Table from it? I have the above in Query SQL View, and it won't allow me to go into Design View to select Make Table. The message I get is, "Microsoft Access can't represent the join expression B.SaleDate<A.SaleDate in Design View".

I'm assuming that there's something I can add in SQL. Can someone provide some guidance.

Thanks!

 

[PHV]

Either try this:
SELECT A.ID, A.SaleDate-Max(B.SaleDate) AS Diff INTO NewTable
FROM tblSaleDate A INNER JOIN tblSaleDate B
ON A.ID=B.ID AND B.SaleDate<A.SaleDate
GROUP BY A.ID, A.SaleDate

Or save it and create a maketable query based on it

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

You can just save your above query and then create a make table query based on the saved query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[brahmabull525]

The "INTO NewTable" did the trick. Sadly, if I'd thought about it more I might have stumbled upon the second, Make Table query feeding off the first method.

Thank you both for the help!!!