Create report header name from ID field (1 Viewer)

GoodyGoody

Registered User.
Local time
Today, 05:35
Joined
Aug 31, 2019
Messages
120
Hi, I have a really simple issue but can't figure it out. I have a table with a Race ID in it and I want to use this ID to get the race name and date and put them as the header on the report 'cos all results relate to this race. Simples! If I use a text box I just have a field in the underlying recordsource or an expression relating to it but can't do a table lookup. If I use a list or combo box, it still requests a record source and then in the Row source I can put a SQL expression but I don't know how to connect the output of the row source SQL expression to the Control Source. Does that make sense? Say my race event ID is 62. I want to look up the race name and date and display those as the header on the report rather than the number but can't work it out. Help please! Thanks Stephen
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:35
Joined
Sep 21, 2011
Messages
14,048
Why not bring them in with the report source data and just use the field as the control source?
Just join the tables in a query.?
 

GoodyGoody

Registered User.
Local time
Today, 05:35
Joined
Aug 31, 2019
Messages
120
Well I guess I'd first like to know if what I want to do is possible. So, are you saying that what I am trying to do is not possible?
 

GinaWhipp

AWF VIP
Local time
Today, 01:35
Joined
Jun 21, 2011
Messages
5,901
Hmm, I guess you could use a DLookup() but seems easier to just bring that table into the Recordsource of the Report.
 

GoodyGoody

Registered User.
Local time
Today, 05:35
Joined
Aug 31, 2019
Messages
120
It's a joined table so not sure Dlookup would work. Just seems bizarre that you can't just do a simply query on a header field in access. If I have 10000 records in my input file it's having to do 10000 reads on the table (and then the joined table) to put the race name and date on every record as opposed to a single read on the top of the report. I'm happy to go with the experts but I'm sort of amazed that something seemingly so obvious is beyond an Access report.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:35
Joined
May 7, 2009
Messages
19,169
you simply create a query that will join the Race ID to the Race ID of the table that has the Race Name and Date on it.
use the resulting query as the record source of your report.
 

GinaWhipp

AWF VIP
Local time
Today, 01:35
Joined
Jun 21, 2011
Messages
5,901
Not sure what you mean by a Joined Table but a DlookUp() would be a single read at the top of the report. It is quite common to put the Look Up Table within the Recordsource of the Report to get the value instead of the ID so not sure why you think that is wrong or there is a better way. Since one only stores the ID from the Look Up Table it makes perfect sense.
 

GoodyGoody

Registered User.
Local time
Today, 05:35
Joined
Aug 31, 2019
Messages
120
.. it's not wrong but seems inefficient to have 10000 reads when one at the top of the report would do. Dlookup won't work as it looks up a single field on a table and I need to get the info from 2 tables so need a join query. I can easily do a query just want it to be as efficient as possible. It seems from the kick back that a simple read on a header field is not possible in access. hey ho!
 

GinaWhipp

AWF VIP
Local time
Today, 01:35
Joined
Jun 21, 2011
Messages
5,901
Well, you could use two DLookUp's or create a query with the two tables and base your DLookUp on that or just add them to the Recordsource. Not sure why you think this is inefficient, it works and technically one read if part of the Recordsouce as it opens the Table once when you run the Report.

You could get crazy and write a Module and then use a Function but that is inefficient, if you ask me and a lot more work for little results.
 

Users who are viewing this thread

Top Bottom