473,320 Members | 1,814 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

MS Access Report

27
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
May 22 '20 #1

✓ answered by twinnyfo

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
twinnyfo
3,653 Expert Mod 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_CalibrationRecords.EquipID, 
  2.        Max(CalibrationDone) AS LastCal 
  3. FROM   tbl_CalibrationRecords 
  4. 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.
May 22 '20 #2
Pol53
27
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")
May 25 '20 #3
twinnyfo
3,653 Expert Mod 2GB
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.
May 25 '20 #4
Pol53
27
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 ?
May 26 '20 #5
twinnyfo
3,653 Expert Mod 2GB
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.
May 26 '20 #6
Pol53
27
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
May 26 '20 #7
twinnyfo
3,653 Expert Mod 2GB
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!
May 26 '20 #8
Pol53
27
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 !
May 26 '20 #9
twinnyfo
3,653 Expert Mod 2GB
Wonderful! I'm glad we could hepp!

Let us know if you stumble across any other struggles!
May 26 '20 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

3
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...
2
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.
2
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>...
15
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...
8
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...
4
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...
3
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, , ".=" & _...
5
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...
6
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....
1
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...
0
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...
0
isladogs
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...
1
isladogs
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...
0
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...
1
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)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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
0
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...
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.