Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have a query which extracts for each plant in an order the location where the plant exists. I need to create some kind of cross tab query that will specify the Customer, Plant and Size but instead of putting location as the col heading it shows location 1, location 2, location 3. e.g.

    Customer name, Plant name, Size, Location 1, Location 2, Location 3
    Cust 1 Plant 1 1 C19 D20
    Cust 1 Plant 2 1 A20
    Cust 2 Plant 2 1 A20
    Cust 3 Plant 1 1 C19 D20


    Location can vary for each Plant
    For some Plants there will be only one location, for others 2 or 3 or more

    A cross tab with Location as Col heading does not work as that would give me 5 different locations across, most of which would have not data, except for the plants in that location.

  2. #2
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts
    As long as the locations you want to use as headings are within a field in the query output, then simply display the query properties and use the property "Column Headings" to specify the precise order there. Press "F1" for details about syntax and examples.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts
    That is the problem. The headings are not in the data. The headings are required to be Location #1, Location #2, Location #3, etc. to the maximum number of Locations of the plants selected. i.e. some plants may have , some 2, some 4, some 5; in which case there would be Location #1 to Location #5. I could determine that maximum number and then presumably set up a query accordingly. The issue then is how do I get the data into those fields when the data field name is Location and the content could be anything.

    It sounds like a classic X-tab query but then I land up with all the locations (approx 50 of them) across the page; when all the user wants to know is the first location, second location etc of a given plant.

    I could create a query with empty fields equating to the heading names, but I don't want more than I need (I.e. if the max is 3 then only 3 fields)

    I am attaching a sample set of data and in it you will see some plants have 2 or more locations. What the user wants to see (using some of the data from the table) is:

    Customer Flag Colour curqty size pltnme, Location 1, Location 2, Location 3
    AARTS Nursery Green 20 1 Hydrangea Quickfire (PW) C19 C23
    AARTS Nursery Green 20 2 Hydrangea quercifolia Alice C29


    Hope that makes sense
    Attached Files Attached Files

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I would create a table with the group by fields and including Customer, Flag Colour, curqty, size, pltnme, Location1 Location2 and Location3 as extra fields as part of a maketable query, this table to be called tblGroups.
    The i would (in VBA in a form) read the tmpSample and apply the following code to each line:

    Read tblGroups and :
    If Location1 is Null then
    Location1 = Location (from tmpSample)
    elseif Location2 is null then
    Location2 = Location (from tmpSample)
    elseif Location3 is null then
    Location3 = Location (from tmpSample)
    endif

    go back to read next tmpSample

    At the end of this you will have tblGroups populated with the locations you want (up to 3 locations)

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    In this little bit of data

    [attachment=88262:PlantLocations.gif]

    does the 20 represent the total of that plant, or the number at that location?

    The result you say you want

    AARTS Nursery Green 20 1 Hydrangea Quickfire (PW) C19 C23
    assumes that the 20 is a total.

    I don't know how the real data is structured, but using the sample data you could
    • Extract the distinct rows of data , ignoring the locations then
    • Add the locations using the Concat function.
    [attachment=88263:concat.txt]

    I attach demo
    [attachment=88264:PlantLocations.zip]

    To make this work I had to remove the single and double quotes from within the plant names. It would be easier if there was an autonumber key field for plants.
    MY solution lists the locations, comma separated within a single field, so you don't get headings such as location1, location2 etc, but you can have any number of locations for each plant.
    Attached Images Attached Images
    Attached Files Attached Files
    Regards
    John



Posting Permissions

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