Access 97: How to write this query efficiently?

In Access 97:

I would like a report to show the first time a product has shipped into a new Country.

I have a table called 'InvoiceOrderEntry' with about 50,000 records.

The table has key fields like:

InvoiceID
ShipDate
ProductModelNumber
Country

Of course, there are many different Invoices, Products and Countries.

For example, I want to see the first time a Red Pen has been shipped to Iceland.
Then I want to see the first time a Blue Pen has been shipped to Iceland.
Then I want to see the first time a Black Pen has been shipped to Canada.

As the years go by, more new Products will get shipped to new Countries.

I can code it a brute-force way whereby I run a query on all products shipped and save that to a temp Table. Then I run a second query to see if any new Countries have been shipped to and append the temp Table. And, so on and so forth.

That does not seem to be very efficient.

Is there an elegant way of doing this? I like the idea of saving the results to a temp Table so I can then run reports on that table.

I hope I have made myself clear here.

High points for a speedy solution!

Thanks!!
SOTAAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this query


select A.*
from InvoiceOrderEntry As A
inner join
(select min(B.ShipDate) as MinShipDate, B.ProductModelNumber, B.Country
  from InvoiceOrderEntry As B
  group by B.ProductModelNumber, B.Country) As C
ON A.ShipDate=C.MinShipDate and  A.ProductModelNumber=C.ProductModelNumber and A.Country=C.Country
0
 
SOTAAuthor Commented:
Thanks capricorn1!!

I got a small issue though. I copied the code into SQL Manager as a new View and it worked perfectly!! AWESOME!!!!!

However, when I paste it into Access97 QBE grid in SQL view, I get an error "Syntax error in FROM clause".

Can you help?
Thanks much!!
0
 
Rey Obrero (Capricorn1)Commented:
can you copy the SQL in Access 97  and post here.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
SOTAAuthor Commented:
Select
  C.MinShipDate,
  C.ProductModelNumber,
  C.ShipCountry
From
  InvoiceOrderEntry As A Inner Join
  (Select
    Min(B.ShipDate) As MinShipDate,
    B.ProductModelNumber,
    B.ShipCountry
  From
    InvoiceOrderEntry As B
  Group By
    B.ProductModelNumber, B.ShipCountry) As C On A.ShipDate = C.MinShipDate And
    A.ProductModelNumber = C.ProductModelNumber And A.ShipCountry =
    C.ShipCountry
0
 
Rey Obrero (Capricorn1)Commented:
change this part

Select
  C.MinShipDate,
  C.ProductModelNumber,
  C.ShipCountry


with

Select
  A.MinShipDate,
  A.ProductModelNumber,
  A.ShipCountry


the whole query, copy and paste

Select
  A.MinShipDate,
  A.ProductModelNumber,
  A.ShipCountry
From
  InvoiceOrderEntry As A Inner Join
  (Select
    Min(B.ShipDate) As MinShipDate,
    B.ProductModelNumber,
    B.ShipCountry
  From
    InvoiceOrderEntry As B
  Group By
    B.ProductModelNumber, B.ShipCountry) As C On A.ShipDate = C.MinShipDate And
    A.ProductModelNumber = C.ProductModelNumber And A.ShipCountry =
    C.ShipCountry
0
 
SOTAAuthor Commented:
Still gives the same error.
0
 
Rey Obrero (Capricorn1)Commented:
can you run just this query

Select
  A.MinShipDate,
  A.ProductModelNumber,
  A.ShipCountry
From
  InvoiceOrderEntry As A


see if you will get results or error
0
 
SOTAAuthor Commented:
Nope as MinShipDate is not a valid column name.

This works:

Select
  A.ShipDate,
  A.ProductModelNumber,
  A.ShipCountry
From
  InvoiceOrderEntry As A
0
 
Rey Obrero (Capricorn1)Commented:
how about this one

Select
    Min(B.ShipDate) As MinShipDate,
    B.ProductModelNumber,
    B.ShipCountry
  From
    InvoiceOrderEntry As B
0
 
SOTAAuthor Commented:
Nope, but this works:


Select
    Min(B.ShipDate) As MinShipDate,
    B.ProductModelNumber,
    B.Country
  From
    InvoiceOrderEntry As B
GROUP BY B.ProductModelNumber, B.Country;
0
 
Rey Obrero (Capricorn1)Commented:
ok... this should work


Select
  A.MinShipDate,
  A.ProductModelNumber,
  A.ShipCountry
From
  InvoiceOrderEntry As A
INNER JOIN
(
Select
    Min(B.ShipDate) As MinShipDate,
    B.ProductModelNumber,
    B.Country
  From
    InvoiceOrderEntry As B
GROUP BY B.ProductModelNumber, B.Country
) AS C
On A.ShipDate = C.MinShipDate And
    A.ProductModelNumber = C.ProductModelNumber And A.ShipCountry =
    C.ShipCountry
0
 
SOTAAuthor Commented:
nope. sorry!

"Syntax error in FROM clause".
0
 
Rey Obrero (Capricorn1)Commented:
this could be an Access97 problem.. try to use two queries

create this query and save as  "Q_Min"

Select
    Min(ShipDate) As MinShipDate,
    ProductModelNumber,
    Country
  From
    InvoiceOrderEntry
GROUP BY ProductModelNumber, Country


now create another query using the QBE grid,
add the table InvoiceOrderEntry and the Query  "Q_Min"

create a join on fields  Shipdate and MinShipDate
ProductModelNumber And ProductModelNumber
ShipCountry and ShipCountry

run the query
0
 
SOTAAuthor Commented:
Thanks!!! Yes, that did the trick. Now, having said that...Access97 takes a long time to run this query. So, I will instead make a VIEW in SQL Manager and run the query from the server-side for speed. I will then link the result via ODBC to Access97.

Thanks SO much for this!!!! Amazing!!!
:-)
0
 
SOTAAuthor Commented:
Absolutely perfect!!!! Thanks!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.