Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Union Query Problem (A2K SR1)

    <P ID="del"><FONT SIZE=-1>Post deleted by rhconley</FONT>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Union Query Problem (A2K SR1)

    You'll have to post the query. You say it works perfectly on another machine, so what is different between the two? Are you running the same service releases and patches? Are you running the same operating systems and builds?

    By the way, 'Description' is not a good choice for a field name, since it is also the name of a built-in property. It's best to avoid confusing Access by using names it might think are something else.
    Charlotte

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query Problem (A2K SR1)

    I read that you don't use memo, ole or hyperlink but for what it is worth, have a look at <A target="_blank" HREF="http://support.microsoft.com/search/preview.aspx?scid=/search/viewDoc.aspx?docID=KC.Q208926&dialogID=9257638&ite rationID=1&sessionID=anonymous|8184803&url=">Error Using OLE or Memo Field in Union Query (Q208926)
    </A>
    Francois

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query Problem (A2K SR1)

    Charlotte,

    Both machines are identical physically and software-wise, running A2K SR1 under Windows 2000 SR2.

    Here's the query. It's rather long:

    SELECT qryPOMOD.PRNumber, qryPOMOD.Company, qryPOMOD.Attention, qryPOMOD.Address, qryPOMOD.Address2, qryPOMOD.City, qryPOMOD.State, qryPOMOD.ZipCode, qryPOMOD.Phone, qryPOMOD.Fax, qryPOMOD.Requestor, qryPOMOD.SCA, qryPOMOD.PoPStart, qryPOMOD.PoPEnd, qryPOMOD.AgreementType, qryPOMOD.PONumber, qryPOMOD.ModificationNumber, qryPOMOD.CostBasis, qryPOMOD.LineItemNo, qryPOMOD.CWBS, qryPOMOD.Description, qryPOMOD.Unit, qryPOMOD.Quantity, qryPOMOD.UnitCost, qryPOMOD.TotalCost, qryPOMOD.SumOfTotalCost, qryPOMOD.SumOfSumOfTotalCost, qryPOMOD.TIN, qryPOMOD.ShipContact, qryPOMOD.ShipCompany, qryPOMOD.ShipStreet1, qryPOMOD.ShipStreet2, qryPOMOD.ShipCity, qryPOMOD.ShipState, qryPOMOD.ShipZip, qryPOMOD.ShipPhone, qryPOMOD.ShipFax, qryPOMODVALUES.SumOfTotalCost AS HappyValue
    FROM qryPOMOD INNER JOIN qryPOMODVALUES ON qryPOMOD.PONumber = qryPOMODVALUES.PONumber
    WHERE (((qryPOMOD.ModificationNumber)=[qryPOMOD.ModificationNumber]) AND ((qryPOMODVALUES.ModificationNumber)<[qryPOMOD.ModificationNumber]))
    UNION SELECT qryPOMOD.PRNumber, qryPOMOD.Company, qryPOMOD.Attention, qryPOMOD.Address, qryPOMOD.Address2, qryPOMOD.City, qryPOMOD.State, qryPOMOD.ZipCode, qryPOMOD.Phone, qryPOMOD.Fax, qryPOMOD.Requestor, qryPOMOD.SCA, qryPOMOD.PoPStart, qryPOMOD.PoPEnd, qryPOMOD.AgreementType, qryPOMOD.PONumber, qryPOMOD.ModificationNumber, qryPOMOD.CostBasis, qryPOMOD.LineItemNo, qryPOMOD.CWBS, qryPOMOD.Description, qryPOMOD.Unit, qryPOMOD.Quantity, qryPOMOD.UnitCost, qryPOMOD.TotalCost, qryPOMOD.SumOfTotalCost, qryPOMOD.SumOfSumOfTotalCost, qryPOMOD.TIN, qryPOMOD.ShipContact, qryPOMOD.ShipCompany, qryPOMOD.ShipStreet1, qryPOMOD.ShipStreet2, qryPOMOD.ShipCity, qryPOMOD.ShipState, qryPOMOD.ShipZip, qryPOMOD.ShipPhone, qryPOMOD.ShipFax, qryPOMODVALUES.SumOfTotalCost AS HappyValue
    FROM qryPOMOD INNER JOIN qryPOMODVALUES ON qryPOMOD.PONumber = qryPOMODVALUES.PONumber
    WHERE (((qryPOMOD.ModificationNumber)=[qryPOMOD.ModificationNumber]) AND ((qryPOMODVALUES.ModificationNumber)='1'));

    I hope this helps. Thank you.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Union Query Problem (A2K SR1)

    Well, it probably has nothing to do with your problem, but why is this in there: qryPOMOD.ModificationNumber=[qryPOMOD.ModificationNumber]? It's always going to be equal to itself, so I'm not sure what this is saying.

    You're joining queries here. If you do a maketable on each of those queries and join the resulting tables, do you see the same problem?
    Charlotte

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Union Query Problem (A2K SR1)

    Charlotte,

    I would be the colleague Roy references (we work for the same firm) and re-wrote the SQL statement using the Union query. The qryPOMOD.ModificationNumber is equal to the parameter entered by the user. This drives a number of other queries that Roy has set up. The query POMOD actually joins 5 different tables.

    The interesting part is that this SQL statement works on my machine without error and we have the same updates, patches, and setup. I also had Roy email me the code from his machine and put it in a new query on may machine and it worked again without error.

    The query POMOD was originally set up to produce data for a report. However, this query did not obtain all of the data necessary for all of the fields. The UNION query obtains the other data via the field HappyValue.

    Another query then gets the data from the Union query and groups it to produce the correct subtotal for the HappyValue value to produce the report.

    I am not sure the results of the make table query.
    Regards,

    Gary
    (It's been a while!)

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Union Query Problem (A2K SR1)

    Build a maketable query from each of your joined queries and then create a new SQL statement using those tables instead of the queries. The idea is to see whether the problem is triggered by data returned or by something in the query syntax for that particular machine. You might try doing a detect & repair on that machine to see if something has gone askew with Access as well.
    Charlotte

  8. #8
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Union Query Problem (A2K SR1)

    I worked with Roy yesterday afternoon and we solved the problem based on input from both Charlotte and Francois, Thanks. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Here are some interesting items we encountered:

    1. In the query we posted yesterday, we received the stated error message. However, when we removed the one field with the name .description, the query did not produce the error. The .description field is a memo field.

    2. Using the query posted yesterday, based on the article Francois Referenced, we changed the Union to Union All. The query worked with the .description field, however, it provided the wrong data set. (i.e., it extracted all of the data which is not what we wanted.

    To get the query to work, we used the Union All statement and modified the second half of the statemement such that the value of 'HappyValue' will always be zero based on Roy's logic.

    The query is working fine right now. Thanks for your help.

    It is interesting that the query using the Union clause worked on my machine but not Roy's.

    Again, thanks everyone for your help. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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