Solved

Access 97: How to write this query efficiently?

Posted on 2014-01-21
15
273 Views
Last Modified: 2014-01-21
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!!
0
Comment
Question by:SOTA
  • 8
  • 7
15 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39797929
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
 

Author Comment

by:SOTA
ID: 39798044
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39798124
can you copy the SQL in Access 97  and post here.
0
 

Author Comment

by:SOTA
ID: 39798137
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39798152
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
 

Author Comment

by:SOTA
ID: 39798206
Still gives the same error.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39798241
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:SOTA
ID: 39798254
Nope as MinShipDate is not a valid column name.

This works:

Select
  A.ShipDate,
  A.ProductModelNumber,
  A.ShipCountry
From
  InvoiceOrderEntry As A
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39798276
how about this one

Select
    Min(B.ShipDate) As MinShipDate,
    B.ProductModelNumber,
    B.ShipCountry
  From
    InvoiceOrderEntry As B
0
 

Author Comment

by:SOTA
ID: 39798289
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39798320
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
 

Author Comment

by:SOTA
ID: 39798354
nope. sorry!

"Syntax error in FROM clause".
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39798388
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
 

Author Comment

by:SOTA
ID: 39798521
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
 

Author Closing Comment

by:SOTA
ID: 39798524
Absolutely perfect!!!! Thanks!!!
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now