Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: SQL help (2000)

  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL help (2000)

    Hi,
    I have a union query which gives me the data that I want, it combines the PROrder table and the CCOrder table. I want to base a form off of this query and I would like more fields from 2 other tables in this query, but I am having trouble figuring out the syntax. Here's my current query:
    SELECT [lngMachineID], [lngVendorID],[dtmDatePartOrdered], [txtCCPRNo] FROM [tbl4172A] UNION ALL SELECT [lngMachineID], [lngVendorID], [dtmDatePrepared],[txtPRNo] FROM [tblPurchaseRequest];

    I'd like to add 6 fields from the tblMachineInformation table (machine name, JON, ID, Model, Serial #, Material ID) and 9 fields from the tblVendor table (vendor name, add1, add2, city, st, zip, ph, fax, email). Any help would be appreciated.
    Thanks,
    Deb

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

    Re: SQL help (2000)

    Save your union query and give it a name.
    Use this query like any other query or table to create a new joined query
    Francois

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL help (2000)

    You mean pull that union query in to regular query design view and add the other tables? I'll give it a try. Thanks, Deb

  4. #4
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL help (2000)

    That seems to be what I want. Sometimes things are so easy. Thank you.
    Deb

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

    Re: SQL help (2000)

    You can't create a union query in design view, you have to create an empty query, without tables and switch to SQL view. Enter there your union sql statement and you can switch to Datasheet view to see if you have what you want. Then you can save it like any other query. If you want to change it later, just click on design and you will see the sql statement.
    Francois

  6. #6
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL help (2000)

    Another question somewhat related. Not sure if I'm causing myself more work. In this database I have to keep track of 2 different types of orders. PR's and CC's. I have 4 tables to keep track of all this data, PR & PROrderDetails and CC and CCOrderDetails. The primary key in the PR table is txtPRNo and the primary key in the CC table is txtCCPRNo. I used the Union select all query to combine these tables and it gives me the output I want except it names the one field txtPRNo, even though some of those numbers are actually txtCCPRNo as well. Now when I use this query on a form I have a subform where the link field is the txtPRNo which it works fine when the I'm dealing with PR orders, but once I get to CC orders it needs the txtCCPRNo field as the link field for the subform. I'm not sure how to fix this. One thought was to just change the field name in the CC table to txtPRNo instead of txtCCPRNo. Can I have the same named field in two different tables that will hold similar data but different? Should I do that or can I fix my problem some other way?
    Thanks,
    Deb

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

    Re: SQL help (2000)

    Do you mean that the id in PR and the id in CC can have the same number and when you use the union query you have the id twice and cannot relate it to the right record in the detail tables ?
    Francois

  8. #8
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL help (2000)

    No, those numbers will never be the same. I have 50 orders between the 2 types and the union shows all 50 correctly.

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

    Re: SQL help (2000)

    Then i'm afraid I don't understand your previous question.
    If you link the union query of PR and CC to an union query from PR-detail and CC-detail where is the problem ?
    Francois

  10. #10
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL help (2000)

    I'd like to use the union query in another query (QueryX) to add those other fields I talked about in the previous post. When I get the output of the union query it gives me 4 fields, lngMachineID, lngVendorID, dtmDatePrepared, txtPRNo. Because of the union query the txtPRNo field is actually the combination of the txtPRNo & txtCCPRNo fields. I get the correct data output. The problem comes in when I'm creating a form based on QueryX, it has the txtPRNo field from the union query. I have a subform and the link field right now is txtPRNo and that works for about half the records, but because the other half are really the txtCCPRNo number the subform does not work because the link field is wrong. I don't know how to get the subform to work for both numbers.

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

    Re: SQL help (2000)

    If it isn't much work to change all the txtCCPRNo in txtPRNo in all object txtCCPRNo has been used you can do that.
    If it is a lot of work, create a new query and in the column that should hold txtCCPRNo type :
    txtPRNo : txtCCPRNo
    And use this query where you need the conversion to txtPRNo
    Francois

  12. #12
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL help (2000)

    That seems to be working, I just changed the txtCCPRNo field. I have to do further testing to see if that was ok though. My next question is can you edit the data on a union query? One of the fields in this union query is a PartReceivedDate that i would like to edit but it tells me that I can't.
    Thanks,
    Deb

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

    Re: SQL help (2000)

    An union query is not updatable. Have a look at this MSDN article : When can I update data from a query?
    A solution would be to put an unbound textbox on the form and use an update query to change the date in the table.

    Maybe a dumb question, but why don't you merge the two tables and use a field to say the record is PR or CC ?
    Francois

  14. #14
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL help (2000)

    Not a dumb question. I asked myself that many times. They are ordered different, one is a credit card order and requires different fields and different print outs the other is what they call purchase request orders, different fields and different print outs. Ok. I'll have to try something different to combine these two areas for receiving. See, one guys receives all parts ordered no matter how they are ordered, so I wanted one screen that had a drop down list of all orders for him to select and then it would show the order. The union query you helped me with does exactly what I want except now he won't be able to edit the form and put in the receive date. Back to the drawing board.
    Thanks,
    Deb

  15. #15
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL help (2000)

    If you are back at the drawing board, here is a different tack to consider: use three tables.

    1 -- all the common fields for both Credit Card and Purchase Order items.
    2 - one table for the fields used ONLY by CC purchases
    3 - one table for the fields used only by PO items.

    Use one-to-one relationships from the main table to either of the "details" tables. Your data-entry form would have the fields from the common table in the main part, then two subforms for the type-specific fields. Your printouts would be kept separate, and both would be based on queries that brought together the appropriate two tables.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Page 1 of 2 12 LastLast

Posting Permissions

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