Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query to Form (A2k)

    I'm trying to create a continuos form from a query built up of two tables.

    The two tables have relationships via EstimateNo & Supp.
    On starting the query, I have EstimateNo, Supp, New, R-R, Rep, Paint.
    This part of the query is ok, and when summed, shows one line of data with the totals added.

    Now when I try to add data from tblOtherDetails, the totals from tblEstimateDetails duplicate vertically and no matter how I address this, the same happens.
    The query should read:

    EstimateNo
    Supp
    New: (Total)
    R-R: (Total)
    Rep: (Total)
    Paint: (Total
    Airbag: (Total)
    AirCon: (Total)
    Consu: (Total)

    From here, once the query is correct, I can then build my continuous form.

    Any idea's on how to rectify this query would be appreciated.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query to Form (A2k)

    With tblEstimateDetails, you want to calculate the sum of values in a field such as New and R-R, grouped by EstimateNo and Supp. The fields are next to each other in the table, and the sums are next to each other in the query.

    With tblOtherDetails, you want to calculare the sum of values in SpecCharge for each value of the Code field, grouped by EstimateNo and Supp. The values of Code are below each other in the table, but you want the sums next to each other in the query. That is a fundamental difference.

    The way to do this is a crosstab query based on tblOtherDetails. (But see the remarks below!)
    <UL><LI>Start creating a query in design view.
    <LI>Select tblOtherDetail and click Add, then Close.
    <LI>Add EstimateNo, Supp, Code and SpecCharge to the query grid.
    <LI>Select Query | Crosstab Query.
    <LI>Change the Total option for SpecCharge to Sum; leave the other fields as Group By.
    <LI>Set the Crosstab option for EstimateNo and Supp to Row Heading.
    <LI>Set the Crosstab option for Code to Column Heading.
    <LI>Set the Crosstab option for SpecCharge to Value.[/list]Switch to datasheet view. Lo and behold: the values of Code are now displayed next to each other as column headings.

    The next steps would be:
    - Save this query.
    - Remove tblOtherDetails from qrySummary.
    - Create a third query based on these two queries; join them on EstimateNo and Supp, and add the fields you need.

    BUT

    I don't know how many values Code can have. In your demo database, there are only distinct 3 values, so it contributes 3 columns to the end result. If you have 75 distinct values, you get 75 columns. Not very handy in a continuous form. If you have 260 distinct values, you're in trouble, since a query can have 255 columns max. Perhaps you should rethink what you want to accomplish with this.

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to Form (A2k)

    Hans
    Thanks once again, and I will try your method as suggested in a few minutes.
    I had tried various combinations of queries to the point of posting.
    The items from tblOtherDetails I am certain will possibly never exceed 10 so this method might be just what I'm looking for.

    I'll let you know how I get on.

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to Form (A2k)

    Hans

    So close !!
    If I add another Item to tblOtherDetails, it doesn't show in the form, unless I create a new form every time.
    I have added "Ru" and "SGC"
    Or am I missing something ?
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query to Form (A2k)

    No, you're not missing anything. When you create a form based on a crosstab query, it only includes the items available at design time.

    If you know in advance all values that Code in tblOther Details can possibly have, you can specify them in the crosstab query:

    Open the crosstab query in design view.
    Click in an empty part of the upper half of the query window.
    Activate the Properties window. It should display the Query Properties.
    Enter the list of potential values of the Code field in the Column Headings property, enclosed in quotes and separated by commas, for example

    "Airbag", "Aircon", "Consu", "Crisps", "Fan"

    This will make the crosstab query display the specified columns in the specified order, regardless of whether there are data in each column. If you create the form now, it will have all columns needed.

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to Form (A2k)

    Yes I see now !!

    This would only cause a problem, if, some body decided another code is required to be added to the tblOtherItems lookup.
    I fear maybe that would be difficult at a later stage, although, this is the format I needed.

    I'll give this further thought, I may have to use a different method.

    Have fun

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query to Form (A2k)

    To create a dynamic crosstab form is complicated. There was a thread about it recently. A dynamic crosstab report is also complicated, but many examples are available.

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to Form (A2k)

    Thanks for the additional info, I'll do a google.
    That should come up with something.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query to Form (A2k)

    Here is a link to the thread on dynamic crosstab forms: <post#=297489>post 297489</post#>. And you'll find a demo of a dynamic crosstab report attached to <post#=296915>post 296915</post#>.

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to Form (A2k)

    Hans
    I didn't have time to thank you earlier for the links (manic day in the bodyshop) but thanks for the extra support.

    I've not had chance to study the demo but I will later.

    Have a good weekend

Posting Permissions

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