Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Location
    Miami, Florida, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Joining two queries (2000)

    I have two separate queries. The fields are:
    Invoice Date grouped by Month, Branch, ServParts
    Transaction Date grouped by Month, Branch, AllParts

    I need to perform a calculation with the two types of parts, so I'm creating a third query.

    I tried to join the two queries by dates and branch because I want Month, Branch, ServParts, All Parts, then I'll add my calculation
    After adding the second query and the fields in the design view, I try to preview the data and I get an error message:
    "The field is too small to accept the amount amount of data you attempted to add. Try inserting or pasting less data."

    I haven't had this problem with my other query combinations (which only needed to be joined by branch) so I removed the date join.
    After the months aren't joined anymore, the query displays the data (but of course, I have an undesirable Cartesian product)
    This leads me to believe it doesn't actually have anything to do with column size.

    I'm not quite sure what to try... has anyone seen this before??

    Thanks.
    Chris

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Joining two queries (2000)

    I am puzzled by this question because I would expect that a query whose fields are:
    invoice Date grouped by Month, Branch, ServParts
    would probably not contain any dates.
    When you run a grouping query, the field that is not grouped has an aggregreate function (usually sum or count) and ends up returning a number. (It could be max, min. first etc in which case it would be a date).
    What aggregate function do you have on the dates?
    Regards
    John



  3. #3
    New Lounger
    Join Date
    Nov 2002
    Location
    Miami, Florida, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Joining two queries (2000)

    Each query is built from a more detailed query that has work order #, branch, invoice date, quantity, cost...
    I use the detailed query as a source and choose date, branch and cost as my columns
    When I choose sum for the cost, I get a choice about grouping the dates - unique date/time, week, month, year...
    After I choose month, the query is displayed with the month, the branch and the cost at that branch for that month.
    I think it might see the month columns as text, but it is using the dates to figure out what to sum.
    There was no need put any functions on the actual dates.

    Let me know if you need more information. I still haven't figured this out!
    Thanks
    Chris

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Joining two queries (2000)

    <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15> to jump in here, but why don't you post the queries here and we can have a look at them.
    Sometimes it is a lot easier to solve problems like this if we can actually see what you are trying to do.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    New Lounger
    Join Date
    Nov 2002
    Location
    Miami, Florida, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Joining two queries (2000)

    Hi patt - good idea.
    It took me awhile to get below 100 K though!!
    When you try to preview, the date range still in the data is June 29 2001 - July 2 2001

    I want to give them a trend report with a graph that shows them the value of the measurement every month between the range. I left a few of the working reports to show you what I mean.
    My problem is joining the DCAL OTC Part Trend query to the DCAL Service Part Trend query.
    I need to do that so I can add a calculation using fields from both tables
    (See the DCAL Summary query for the working version of the equation - no date columns...)

    The results of this effort are DCAL Trend and DCAL Trend 2
    (In one I started with service and added the OTC, in the other I started with the OTC and added service - it made no difference)

    When I try to preview the data in either DCAL Trend query, I get an error message about field size
    If I remove the join relationship between JHINDT by month and HDATE by month, the query will display data - but I have a Cartesian product.

    Thanks in advance for any help!!
    Chris
    Attached Files Attached Files

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Joining two queries (2000)

    Go into queries "DCAL Service Part Trend" and "DCAL OTC Part Trend" and set the right hand field's show condition.
    Then go into query "DCAL Trend" and delete the relationship between "JHINDT by Month" and "HDATE by Month", then introduce a relationship between the new fields (EXPR1003 I think, it may be different on yours).
    This should then give you the correct results, I hope.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Joining two queries (2000)

    I have another suggestion.
    In both the queries "DCAL Service Part Trend" and "DCAL OTC Part Trend" it seemed to me that the right hand field, the third group by, is just repeating the grouping of the first field in a different format. Both fields effectively group by month. When I deleted this last field from both queries, the join worked OK.
    Each query gave the same results as previously as well.
    Regards
    John



  8. #8
    New Lounger
    Join Date
    Nov 2002
    Location
    Miami, Florida, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Joining two queries (2000)

    Thank you both for the help!
    I had that GROUP BY in there so the months would sort chronologically (instead of alphabetically by first letter of the month), but it is losing the sort in the reports anyway....
    I'd much rather have the calculation work!!

    Thank you again.
    Chris

Posting Permissions

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