Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2005
    Posts
    144

    Red face Unanswered: Combine queries to make one query

    Hi all,
    i am trying improve few things in my database and would like your help on it.
    I would like to print report by click only one button instead of two.
    I have single order report and multiple order report that i print depending upon which order i enter into my database and both are connected to single and multiple tables, which works fine for now but instead i would like to print report from both tables by simply typing the sales order even though they are being entered in two different tables.
    I was trying to make a universal query which will combine them all and i can get the desired result by clicking it once but i couldnt do it.
    here are my queries.

    for single order i have

    SELECT tblEmployees.FirstName, tblEmployees.LastName, tbl1ySingle.SalesOrder, tbl1ySingle.ShipDate, tbl1yParts.PartNumber, tbl1ySingleDetail.Serials, tbl1yParts.PartDescription
    FROM tblEmployees INNER JOIN ((tbl1yParts INNER JOIN tbl1ySingle ON tbl1yParts.PartID = tbl1ySingle.fkPartsID) INNER JOIN tbl1ySingleDetail ON tbl1ySingle.SingleOrderID = tbl1ySingleDetail.SingleOrderID) ON tblEmployees.EmployeeID = tbl1ySingle.EmployeeID
    WHERE (((tbl1ySingle.SalesOrder)=[Enter Sales Order]));

    For Multiple Orders i have

    SELECT tblEmployees.FirstName, tblEmployees.LastName, tbl1yMultiple.SalesOrder, tbl1yMultiple.ShipDate, tbl1yParts.PartNumber, tbl1yParts.PartDescription, tbl1yMultipleDetail.Serials, tbl1yMultiple.OD1
    FROM tblEmployees INNER JOIN (tbl1yParts INNER JOIN (tbl1yMultiple INNER JOIN tbl1yMultipleDetail ON tbl1yMultiple.MultipleID = tbl1yMultipleDetail.MultipleID) ON tbl1yParts.PartID = tbl1yMultipleDetail.fkPartsID) ON tblEmployees.EmployeeID = tbl1yMultiple.EmployeeID
    WHERE (((tbl1yMultiple.SalesOrder)=[Enter Sales Order]));

    Is there a way to combine them both so i can print orders from with in by simply typing the order number?
    Attached Thumbnails Attached Thumbnails Relation.JPG  

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i loathe/hate/despise queries that prompt for criteria, so it gives me perverse pleasure to tell you that even if you succeed in building a UNION between the two queries your happy users will have the doubtful pleasure of typing in the order number twice. when the query fails, they will have no trace at all of the two order numbers they typed so they will have no idea why the query failed.

    please put the wretched order number on a form so that:
    -your long-suffering users only need to type it once
    -your code can check that it is a valid order number before running the query
    -your code and your user can see what was typed when something goes wrong.

    anyhow, back to your question: once you are collecting the criteria from a form you could have a go with something along the lines of:
    SELECT tblEmployees.FirstName As fName, tblEmployees.LastName As lName,
    "N/A" As ODI FROM blah blah blah
    UNION ALL
    SELECT tblEmployees.FirstName As fName, tblEmployees.LastName As lName,
    tbl1yMultiple.OD1 As ODI FROM blah blah blah

    above is for OD1 is text, if OD1 is numeric, use 000 As ODI (or some other 'impossible' OD1 value)

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jul 2005
    Posts
    144

    Red face here is my sample

    Izy,
    Many thanks for replying and I agree with 100% its just i didnt know any other way of doing it.
    let me explain a bit of what i am doing in that database.

    first tbl is tbl1ysingle with that i have tbl1ysingledetail. Second tbl is tbl1ymultiple and tbl1ymultipledetail.

    Now we have two kinds of orders
    First type is where you have say 100 drives and all you need on that order is partnumber and serial for all of the drives so i would have one partnumber attached to the combo box coming from parts tbl and serialnumber field in the subfrm. Now all i do is choose one partnumber and keep on scanning or entering the serials for the hard drives.

    Second type of orders we have are orders with enclosures and the drives that go in it. for example you have an order of 2 enclosures and 5 drives. Enclosure 1 will have 3 drives and enclosure 2 will have 2 drives.

    first enclosure
    partnumber serial
    hard drives going in the enclosure
    partnumber serialnumber partnumber serialnumber partnumber serialnumber

    Second enclosure
    partnumber serial
    hard drives going in the enclosure
    partnumber serialnumber partnumber serialnumber

    So now you tell me how can i combine them in a relationship better than what i have which will let me print report simply by entering the order number.
    I am attaching a sample of that as well so you can see how its done here.
    PS: OD1 means installed and I used it only in multiple tbl since it has enclosure which require hard drive installed, and its a check box.
    Thanks again.
    Attached Files Attached Files

Posting Permissions

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