Hi,
I have two tables which are as below tbl_EquipmentList
EquipID (Text)(PK)
EquipmentName (Text)
Manufacturer (Text) tbl_CalibrationRecords
CalibrationID (PK)
EquipID (FK)
CalibrationDone (Date)
CalibratedBy (Text)
How would i set up a report to show me the latest calibration date for each piece of equipment ?. I've tried designing a query but it only shows me the latest record in the table and not for each item. I've seen other replies for this problem but can't get it to work for me.
Thanks
1) Perhaps my Post in #4 was a bit unclear, so that's my fault.
2) Rather than just start out by giving you the answer, I want you to think about what you are trying to do: a) You want to find the latest calibration dates for all your pieces of equipment, yes? b) AND, for all of those dates, you want to see if they are expired, yes?
Now, he first query we discussed did what? It fulfilled the first requirement a) above. It lists all the latest dates for all equipment.
Logically, how would you accomplish part b) above? It makes it more difficult that you can't calculate that value with the single query. SO, we join the first query to the table JOINING LIKE FIELDS (as you would with any other table or query).
When I said, "joining EquipID and CalibrationDone/LastCal", I in no ways meant "joining EquipID TO CalibrationDone/LastCal". Again, my post was a bit unclear, so, again, my fault.
What I meant was JOIN EquipID TO EquipID. This is the only logical join between the Table and the Query based upon EquipID, isn't it?
Then, JOIN CalibrationDone TO LastCal. Again, this is the only logical join between those two Date Values, isn't it?
What are we doing by making this join? We are limiting the records displayed from the table to only those records that match the query. Yes, the values will be identical! But now, you can manipulate those values and determine if a re-calibration is due anytime soon.
Make sense?
My apologies for the longer response, but I wanted to work through with you the logic behind what I was recommending for you.
Hope this hepps!
9 2789
Create an aggregate query on Table tbl_CalibrationRecords, grouped on EquipID, finding the Max of CalibrationDone. This will give you a list of the latest calibrations done for each piece of equipment.
I'm going to wing this by hand, so it might need some tweaks: - SELECT tbl_CalibrationRecords.EquipID,
-
Max(CalibrationDone) AS LastCal
-
FROM tbl_CalibrationRecords
-
GROUP BY tbl_CalibrationRecords.EquipID;
That might do the trick. Not sitting in front of Access right now, so I can't duplicate this in real time.
Hope this hepps!
By the way: a freebie, here. Why is your EquipID a text value? You can always have a PK as numerical (and should) and if you have a text identifier that you use, you can still retain it in a text field. It is just a better design to use numeric PKs.
Thanks for your help. Your suggestion works but the report i have is supposed to list all the instruments which are overdue for calibration. The table tbl_CalibrationRecords actually has another field called 'NextScheduled' which contains the next calibration date. When i add the criteria below to the query, it shows all the records again instead of just the last record which is what i need.
Overdue: IIf([NextScheduled]<=Date(),"Overdue")
My thought is that you will have to JOIN that query with the table (joining EquipID and CalibrationDone/LastCal) and then perform your desired calculation.
Hope this makes sense.
I'm not really sure what you mean by joining a query to a table ? Are you saying i should set up a particular JOIN between the two tables ?
Yes, you will be creating a second query based upon the table and the query you have already made, joining the fields I’ve described.
Ok i've created another query using the table and the previous query. Can you explain what you mean by joining the EquipID and MaxOfCalibrationDone fields...they're different data types
1) Perhaps my Post in #4 was a bit unclear, so that's my fault.
2) Rather than just start out by giving you the answer, I want you to think about what you are trying to do: a) You want to find the latest calibration dates for all your pieces of equipment, yes? b) AND, for all of those dates, you want to see if they are expired, yes?
Now, he first query we discussed did what? It fulfilled the first requirement a) above. It lists all the latest dates for all equipment.
Logically, how would you accomplish part b) above? It makes it more difficult that you can't calculate that value with the single query. SO, we join the first query to the table JOINING LIKE FIELDS (as you would with any other table or query).
When I said, "joining EquipID and CalibrationDone/LastCal", I in no ways meant "joining EquipID TO CalibrationDone/LastCal". Again, my post was a bit unclear, so, again, my fault.
What I meant was JOIN EquipID TO EquipID. This is the only logical join between the Table and the Query based upon EquipID, isn't it?
Then, JOIN CalibrationDone TO LastCal. Again, this is the only logical join between those two Date Values, isn't it?
What are we doing by making this join? We are limiting the records displayed from the table to only those records that match the query. Yes, the values will be identical! But now, you can manipulate those values and determine if a re-calibration is due anytime soon.
Make sense?
My apologies for the longer response, but I wanted to work through with you the logic behind what I was recommending for you.
Hope this hepps!
Thank you very much.....i have it working now. Your explanation was spot on even for an amateur like me to understand. I appreciate you taking the time to help !
Wonderful! I'm glad we could hepp!
Let us know if you stumble across any other struggles!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Nicola |
last post by:
Hi Everyone,
I am new to programming and would like to know how to
open an access Report from within vb 6. I am trying to write a program
to organise cross stitch threads. I have found out how...
|
by: Tom Weddell |
last post by:
Can I call an Access report from VB.Net? (I'm using access as the backend.)
Thanks in advance.
|
by: Vikrant |
last post by:
Hey Friends
I need to export an Access Report using a filter.
I am using the method OutputTo
m_pDoCmd->OutputTo(
3, // asOutputReport
COleVariant(strReportName), // <report name>...
|
by: Mark C |
last post by:
All,
I have exhaustingly been looking through the newsgroups in search of a way
to systemically output an Access 97 report to a pdf file using the full
version of Adobe Acrobat. I want the user...
|
by: Mike MacSween |
last post by:
tblCourses one to many to tblEvents.
A course may have an intro workshop (a type of event), a mid course
workshop, a final exam. Or any combination. Or something different in the
future.
At...
|
by: Greg Teets |
last post by:
I am running an Access report from VB via ADO.
The report has a field that shows the time it was run.
Is it possible to return the data from this field to VB when the
report runs? Or to find...
|
by: Newbie |
last post by:
This is my first try at running Access Report by Visual Basic
I have the following code in my button press event:
' 2 - Show print preview
objAccess.DoCmd.OpenReport "Invoices", 2, ,
".=" & _...
|
by: M P |
last post by:
Hi!
I am currently working on a project that will migrate MS Access DB and Front
End to web-base solution to reduce MS Access installation. Previously, users
are using MS Access as frontend to...
|
by: DeniseY |
last post by:
I have an Access report that is created on the fly by the user selecting the fields to be included. The Access report comes out fine, but I want it to automatically output to an Excel spreadsheet....
|
by: sonicfun2006 |
last post by:
I have SQL Server Database and MS Access 2003 is connected with ODBC. I have very large amount of records in various tables. The database is very dynamic as it changes every minute. I’m trying to...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |