I have the same basic problem northstar has. I am fairly new to SQL
but knowledgeable enough to do myself some harm. I have a DB that
tracks test systems that test electronic hardware. Each 'Tester' has
several Software Tools (SWTs) on them. Each SWT has constant updates
(Revisions). I need to make a quiery to show me the last revision of
each SWT installed on each Tester.
I tryed using the Karl's methiod:
My first Quiry:
SELECT tbl_InstalledSWT.[Installed SWT] AS CurrentSWT, Max
(tbl_InstalledSWT.[Date Installed]) AS LastDate
FROM tbl_InstalledSWT
GROUP BY tbl_InstalledSWT.[Installed SWT];
Second Quiry:
SELECT tbl_InstalledSWT.[InstalledSWT]
FROM tbl_InstalledSWT INNER JOIN qry_InstalledSWT ON (tbl_InstalledSWT.
[InstalledSWT] = qry_InstalledSWT.[CurrentSWT]) AND (tbl_InstalledSWT.
[Date Installed] = qry_InstalledSWT.[LastDate])
ORDER BY tbl_InstalledSWT.[Installed SWT];
Result:
Error: 'The specified field 'tbl_InstalledSWT.[InstalledSWT]' could
refer to more than one table listed in the FROM clause of your SQL
statement.
I know this currently leaves out the Revision which I do need, but
figured if I cant get past the first stage why make things more
complicated. I tried to make a query that used the Revision field as
the filter but due to the naming of the revisions not having a
standard naming convention (sometimes using letters as the first rev
then numbers (A, B, 1), sometimes using numbers first then letters
(001, 2, AA), sometimes using numbers AND letters (1a, 2a, 2b)) made
this method flawed.
Karl,
Thanks for your help. You introduced me to SQL in a very neat way. By the
way, I obtained the results I wanted.
KARL DEWEY said:
You should be able to do these.
Create a new query, switch to design view, then to SQL view. Copy first
post and change table and field names to your names. Save query as
qryLast_Service. Remove any hard returns the posting/pasting may adddue to
word wraps.
Create a second query the same way, pasting and editing names.
Karl,
I have tried to implement your instructions, but I am a novice and don't
work with SQL, so I will go to the "Beginners" group and pose my question
there. What you have done is very clearly pointed out why I must learn to use
and live with SQL. Thank you
:
Use two queries (if you do not know subqueries).
The first is a totals query like this --
SELECT [CustomerID], Max([ServiceDate]) AS Last_Service
FROM YourTable
GROUP BY [CustomerID];
Then join the totals query in second query --
SELECT YourTable.*
FROM YourTable INNER JOIN qryLast_Service ON (YourTable.[CustomerID] =
qryLast_Service.[CustomerID]) AND (YourTable.[ServiceDate] =
qryLast_Service.[Last_Service])
ORDER BY CustomerID;
:
I have a list of customers and multiple service dates with each. I want to
report on only the latest (last,max) visit. I add grouping for the visit date
field and select either last or max. I get the result of filtering for latest
year, but not within the year, e.g. 2008. The query gives me all visits to
all customers within the last year. I want the most recent visit.I want a
list of all customers and their last visit. What am I overlooking/not
doing/doing incorrectly?
