Re: How do I translate a number to the code in a table it refers to?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 12/08/04

  • Next message: Steve Schapel: "Re: IIF statement in Control Source" 
    Date: Wed, 8 Dec 2004 15:40:17 +0800

    Base the report on a query that reads the fields from both tables.
    1. Create a query that has both tblTapes and tblRatingCodes.

    2. In the upper pane of query design, you see a line joining the 2 tables.
    Double-click that line. Access offers a dialog with 3 options. Choose the
    one that says:
        All records from tblTapes, and any from ...
    (If you do not do this, any tape that has no rating will not be included.)

    3. Drag the fields you need from both tables into the grid.

    4. Save the query.

    5. Use this query as the RecordSource for your report.

    It is also possible to use a DLookup() expresion in the Control Source of a
    text box on the report, e.g.:
        =DLookup("RatingsCode", "tblRatingCodes", "RatingsID = " & [Rating])
    However that will be much less efficient than the query. 

    -- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"LadyAmethyst" <LadyAmethyst@discussions.microsoft.com> wrote in message
news:B2866009-7F8A-4981-818C-14950ED74E5E@microsoft.com...
> Hi,
>
> I'm using Access 97 and I want to create an Access report on a
> table that has the following table & fields:
>
> tblTapes
>    ProgramID
>    ProgramName
>    YearReleased
>    Rating   (this is actually the RatingsID number from the following
>                table selected on a form from a combobox.)
>
> tblRatingCodes
>    RatingsID
>    RatingsCode
>    RatingsExplaination
>
> When I use the data to print to an existing form, I use a DLookup
> in Visual Basic. I just can't figure out how to use it on a Access
> Report.
>
> Any help would be appreciated.
>
> Thanks,
> LadyAmethyst
  • Next message: Steve Schapel: "Re: IIF statement in Control Source"

    Relevant Pages

    • Re: How do I translate a number to the code in a table it refers t
      ... Create a query that has both tblTapes and tblRatingCodes. ... > (If you do not do this, any tape that has no rating will not be included.) ...
      (microsoft.public.access.reports)
    • Re: Populating a list -- table structure?
      ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
      (microsoft.public.access.forms)
    • Re: Fields in Label dont give correct information
      ... You need to create a query based on tblRegEvent and tblRegPayment. ... -If your DCountis to count values in your report, ... half the time with very few errors (other than that label problem I had.) ... For example in the Room assignment on the report an idividual will be ...
      (microsoft.public.access.reports)
    • Re: Tips on domain aggregate replacements
      ... This already is a split db, but the users are 400 miles away from the server hosting the data:) They are all running the same copy of the FE locally on a single terminal server via TS/RDP sessions. ... It doesn't seem to have much impact on performance; the report takes just about as long to run if they are all logged in as it does when I am logged on testing it at night. ... I would do DSums from the controls on the report on the data returned by the query. ... The biggest offender is a certain report that needs to Sum a particular complex total for each of the next twelve months (the DSum in VBA was a sideline to this issue) and present these as items on each line of output. ...
      (microsoft.public.access.modulesdaovba)
    • Re: using a form with combo box to input criteria
      ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
      (microsoft.public.access.forms)