Help with complicated query

[Abbey Normal]

(If this message is a duplicate, apologies. I got anerror when submitting the
first one.)
Need help in finding out the right way to do this:
I have a report whose data source is qrySpec. This qrySpec has an inner join
on 3 fields, all in an 1-to-1 relationship. I want to add a field from
another file. there may not always be records in that file for that
particular sku. But if there is, there could be multiples. But I only want to
get the latest one. I accomplished this by adding another report to the
original report and using this as the data source:

SELECT TOP 1 Auditlog.Sku, Auditlog.Reason, Auditlog.user, Auditlog.chgdate
FROM Auditlog

(I change the sku by doing: CurrentDb.QueryDefs("qrySpecAuditLogs").SQL =
stNewQry)
********************************************************
This method works if a user is on a form viewing one sku and then selecting
the print report button. But there is another form where the user can enter
multiple skus. He does a preview first, then hits control p and then selects
his PDF printer.
This will only print one sku, and he needs to get the multiple reports all
in one PDF file.
Can anyone show me what's the best way of doing this?
I appreciate your help!

WHERE (((Auditlog.Sku)='66323027'))
ORDER BY Auditlog.chgdate DESC - I change the sku by the query

 

[Jeff Boyce]

One approach to getting only the 'latest one' of a recordset is to use a
totals query, grouping by some ID field and getting the "Max" of the date
field.

You could then use this totals query as a source in a second query, joining
your first set of records to (only) the latest in your second set.

By the way, if there are none in the second set, you might want to use a
"Left" or "Right" JOIN to get all of the first set and any matching in the
second.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Aliotsha Grawe]

que

 

[Abbey Normal]

If you can show me how to modify this query in that way, that would solve all
my problems:
This statement:***********************************************
SELECT Color.Version, Glass.GlassID, Glass.VendorID, Glass.PrintingID,
Printing.Printing, Glass.HangtagID, Glass.LensStickerID, Glass.SpecialtyID,
Glass.VendorNo, Glass.CostPerDozen, Glass.SKU, Glass.UPC, Glass.Check,
Glass.Name, Glass.Collection, Glass.Catalog, Glass.CatalogSection,
Glass.Current, Glass.IntroYear, Glass.LensType, Glass.LensMaterial,
Glass.BackCoating, Glass.ANSI, Glass.GlassDetails, Glass.GlassNotes,
Glass.CartonInner, Glass.Image, Glass.LogoSpec, Glass.LogoSize,
Glass.CreationDate, Glass.ChangedDate, Color.DateModified, Color.Quantity,
Color.Metal, Color.MetalCode, Color.Plastic, Color.PlasticCode, Color.Tips,
Color.TipsCode, Color.Temple, Color.TempleCode, Color.Parts,
Color.SpringHinge, Color.Lens, Color.LensCode, Color.Requirements,
Color.Notes, Glass.FrontMaterial, Glass.TempleMaterial,
Packaging.PackagingID, Packaging.PackagingDesc,
GenRequirements.RequirementsID, GenRequirements.RequirementsDesc
FROM ((Printing INNER JOIN (Glass INNER JOIN Color ON Glass.GlassID =
Color.GlassID) ON Printing.PrintingID = Glass.PrintingID) INNER JOIN
Packaging ON Glass.PackagingID = Packaging.PackagingID) INNER JOIN
GenRequirements ON Glass.RequirementsID = GenRequirements.RequirementsID
ORDER BY Color.Version DESC;
***********************************************************
needs the the 'max' of auditlog, and the join field is SKU. I'm guessing I
want to use a left join? But don't know how to add it to this big dog query
and how to specify max.
Thanks,

 

[Jeff Boyce]

Abbey

Perhaps one of the other newsgroup readers has worked directly in the SQL
.... my experience has been via the user interface/design view.

I didn't notice in your SQL statement where "auditlog" is... how is this a
part of your (current) query? What table is that in?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Abbey Normal]

The AUDITLOG is the table that I am trying to join to this big query. When I
did it initally, i got duplicates. But i did not use Left Join or MAx or
Select TOP 1.

 

[Jeff Boyce]

If you put tables into a query without joining them, you get a "cartesian
product" (all of the rows in one table "multiplied by" all of the rows in
the other(s)). Why are you not joining them?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Abbey Normal]

Because I don't know how. The way it worked(before I added that other file)
is a user could enter multiple skus at once and then select preview to view
all of the muliple selections. It's done with a built sql statement which is
then used on the DoCmd.OpenReport where parameter. ("SKU = aaaaa or SKU =
bbbbb"). I was thinking to simplify it by inserting the skus into a workfile
instead of building an SQL statement. Then I could easily run two queries and
join them. Hopefully.

 

[Jeff Boyce]

Abbey

You may want to spend some time familiarizing yourself with queries in
Access. Although they end up as SQL statements, you may find working
through the user interface (query design view) to be a helpful starting
point.

If you want a field in a query to select based on a list of values, one
approach is to use the In() function. In your example, that statement might
look like (untested):

In('aaaaa','bbbbb','ccccc',...)

Or you might want to look into sub-queries if the 'list' is actually in a
table.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP