Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2007
    Posts
    49

    Unanswered: Fields from two Tables on Report.

    Hi,
    My project is in MS Access 2002.
    In that I have one Form which enter data in two different Tables, when I click on Submit Button it will run two diffrent queries one by one.
    Now I want to generate Report from that Tables.
    But I don't know how can I put Fields from two different Tables in Report's Detail Section.
    If Feilds are from only one Table than I can set Report's Record Source property to that TableName.\
    But I don't know how to set it for two tables?

    Even I put fields in Report and set there Control Source Property to that [TableName].[FieldName]. But when I open report It will ask for the value of that TableName

    like some of my Field's Control Source Properties are
    =QCDatasheetForBottlesCTC!BODet
    =QCDatasheetForBottlesCTC!TargetBO
    =QCDatasheetForBottlesCTC!CapTorqueChartTime

    but when I open Report it will ask for value of QCDatasheetForBottlesCTC.
    I don't know what to enter in it.I tried the TableName but nothing happen.Report only shows values from Table which is set as Report's Record Source Property.
    And whatever remain Fields from another Table in that it shows #ERROR.
    Plz tell me how to do this thing?
    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You need to create a query joining the two tables and including the two fields. Set this to your report control source.

  3. #3
    Join Date
    Dec 2007
    Posts
    49
    Can U give me one example of this thing.
    I never Used such thing in my project.
    like i have two tables.

    table1
    name
    no


    table2
    name
    address

    if i want all fields from both table then how to write query?
    is it like this?
    SELECT * FROM table1;
    UNION
    SELECT * FROM table2;
    or something else?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Something else Along the right lines
    http://www.w3schools.com/sql/default.asp

    More specifically:
    http://www.w3schools.com/sql/sql_join.asp
    Go straight to INNER JOIN - the first example is generally considered bad practice these days

  5. #5
    Join Date
    Dec 2007
    Posts
    49

    Cool put more than 255 field on report from two tables

    Hi,
    My project is in MS Access 2002.
    In that I want to generate Report which contain fields 258.I can put 255 fields but how can i add more 3 fields.
    when i generate report for 258 fields than it will show msg too many fields.
    how can i solve this problem?

  6. #6
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    That is a lot of fields!

    Can you concatenate some fields? (i.e. place more than one field into a text box.)
    for example, if you have contact info..
    • first name
    • middle initial
    • last name
    • city
    • state
    • zip


    now on the report you could use one textbox for multiple fields

    txtName= [first name] & " " & [middle initial] & " " & [last name]
    txtAddress = address
    txtCityStateZip = [city] & ", " & [State] & " " & [zip]

    This is an example of seven fields that are displayed in three controls on the report.
    Last edited by jpshay; 01-31-08 at 13:14. Reason: formatting
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Did someone merge two threads here or did I misread the original title?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, the former
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2007
    Posts
    49

    Cool

    ya by mistake,i don't know how , this two thread merge.if anyone can seprate it then please do it.

    By the way concatenation is good option but i don;t have any field like that which i can concatenate it.Even there is another option using subreport but think there must be some other option.But I think I have to use Subreport.
    Thanks for ur reply.
    Last edited by billy_pit; 01-31-08 at 15:53.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •