Create report header name from ID field

[GoodyGoody]

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]

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]

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]

Hmm, I guess you could use a DLookup() but seems easier to just bring that table into the Recordsource of the Report.

 

[GoodyGoody]

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]

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]

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]

.. 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]

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.