Dear Harry:
The only thing I see has changed is that you want to filter
Material.Status = "active". Otherwise, I think what I posted before
would be about right. However, you apparently didn't try it, I think
because you didin't know where to enter it.
On the Database Window, select Queries then click New. Leave the next
dialog on the Design View selection and click OK. Close the Add Table
dialog. At the upper left, click on SQL. Paste in the code of the
query I am sending below, save it and name it, then run it to see what
happens.
SELECT M.Material, MT.LastUpdated, M.Type
FROM Material M
INNER JOIN Material_Trans MT ON MT.Material = M.Material
WHERE MT.Tran_Type = "Issue" AND M.Status = "Active"
AND MT.LastUpdated = (SELECT MAX(LastUpdated)
FROM Material_Trans MT1
WHERE MT1.Material = M.Material
AND MT1.Tran_Type = "Issue")
ORDER BY M.Material
Please let me know how you get along with this.
I would remark that you are apparently not too familiar with the
Access facilities for creating queries since it seems you don't know
about the SQL view of queries. This is a fairly complex query for you
to deal with unless you have some experience reading and writing SQL,
which it seems you may not. Perhaps you have SQL experience in some
other context than Access. But dealing with the above query may be
daunting - it's not a good learning tool for a beginner, but is
perhaps moderately advanced. So, I'll forego explaining what I did
for now, unless you tell me you're ready.
I have omitted displaying the Tran_Type and Status since, using the
filtering you have described, these would always be "Issue" and
"Active". You can certainly add them if desired, but there is really
no information there. This is a kind of "policy" I keep in order to
simplify things when I code.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
OK Tom I have some more info. Kelly and I have been looking at it and I
didnt give you complete info before. Actually some more demands have been
put on me so it gets more complicated.
Here is the SQL code of my initial query:
SELECT Material.Material, Material_Trans.Tran_Type,
Material_Trans.Last_Updated, Material.Status, Material.Type
FROM Material INNER JOIN Material_Trans ON Material.Material =
Material_Trans.Material
WHERE (((Material_Trans.Tran_Type)="Issue") AND
((Material_Trans.Last_Updated)<#1/1/2002#) AND ((Material.Status)="active"))
ORDER BY Material.Material;
Then from this I want the single last updated date for each material code.
For example:
Material Tran Type Status Last Updated
Type
123435 Issue Active 9/12/00
Hardware
3456 Issue Active 6/20/98
Raw
45671 Issue Active 4/7/99
Raw
tr56746 Issue Active 11/14/98
FG
67834 Issue Active 3/5/98
FG
Hopefully this makes more sense and hopefully you didn't give up on me.
Harry
Harry said:
hahahahaha I am laughing cuz you asked how we are doing. I say YOU are
probably doing great. Unfortunately I have yet to take the time to learn
step 1 of this coding stuff. I know, I know, I must learn it if I want to
be any good. I was sorta holding out, cuz our new version of our shop
management package moves over to crystal reports and it is supposedly easier
to use.
I do have a guy that comes in part time and has taken some VB classes. He
is more familiar with it than I am. We will go over it this afternoon
and
I
will let you know what we come up with.
At the risk of sounding like a complete NOOB, where would I enter this info
at. Do I just click on the code button and input it there? Do I enter it
in the expression area?
BTW is there a book you would recommend that could help get me started on
this? I am not sure I have time for classes with 2 little kids at home and
the company not allowing me out during the day.
Thx again,
Harry
Dear Harry:
This isn't quite what I'd hoped for, but I'll give it my best:
SELECT M.Material, MT.Tran_Type, MT.LastUpdated
FROM Material M
INNER JOIN Material_Trans MT ON MT.Material = M.Material
WHERE MT.Tran_Type = "Issue"
AND MT.LastUpdated = (SELECT MAX(LastUpdated)
FROM Material_Trans MT1
WHERE MT1.Material = M.Material
AND MT1.Tran_Type = "Issue")
ORDER BY M.Material
In the above, the phrase:
SELECT MAX(LastUpdated)
FROM Material_Trans MT1
WHERE MT1.Material = M.Material
AND MT1.Tran_Type = "Issue"
is a correlated subquery. "Correlated" is the thing that says
WHERE MT1.Material = M.Material
because it correlates the value of material between the outer query
and this subquery.
To function in a WHERE clause as a criterion, the subquery must be
guaranteed to return only one row and one column. This construction
does guarantee that.
By being correlated, it returns a potentially different value for each
Material being reported. If the combination of Material and
LastUpdated is unique, then Material will not duplicate.
I disagree, at least theoretically, that LastUpdated will be unique
for each value of Material. Unless you build a unique index on the
table to guarantee that, it won't be guaranteed to be unique. I agree
it has a high probability of being unique if the amount of data is
relatively small and if the users posting LastUpdated don't round it
off to the nearest hour or minute by being lazy (or perhaps it is
automatically being stamped, which might be better IF that is an
accurate way of recording it - by posting the time it was recorded
rather than the time the physical transaction occurred. But if you do
this, it would affect which transaction is most recent, being the one
most recently posted, rather than the one that occurred physically
most recently. Consider what happens if a transaction gets lost, and
is posted a week after it "happened"!)
So, how did we do so far?
OK I tried to take a SS of the query, but when i save di out to a
PDF
it
came out like crap. It was really small and barely legible. It is
pretty
simple so I think I can write it out.
Here is the query:
2 tables Material and Material_Trans. Material.Material =
Material_Trans.Material
Columns are Material.Material ; Material_Trans.Tran_Type ;
Material_Trans.LastUpdated
The tran_Type is set to equal "Issue"
*** Note: The last updated field is Date/Time so it is unique ***
Is this enough info? I hope so, but I can always get you more if you
need.
Thank you so much for your help Tom.
Harry
P.S. We only need a Dick and we got a full crew! lol
Dear Harry:
This is typically done using a correlated subquery. If you'll post a
query of your database that shows the data you want to see, I can
modify it to do what you want.
I need to know what column(s) are unique for your "part number"
(probably just a PartNumber column) and the column with the date.
Please understand that if any part has more than one record with the
same date latest date, then all those records for the part will
appear. If there is any unique combination of columns by which you
want to determine which row is to appear (that is, other columns in
addition to the date) you can specify these as well.
Or, perhaps your date column is a date/time and is likely to be unique
(though not absolutely guaranteed).
