Results 1 to 10 of 10
  1. #1
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250

    Question Problem with several queries

    Hello everybody,



    First I will explain some things that are important to understand my database. I store products by their serial number, like this:

    Click image for larger version. 

Name:	db1.png 
Views:	50 
Size:	37.5 KB 
ID:	29998

    I have these queries that I need help with:

    1) I need to list all the serial numbers, only once. Meaning - 1 row will stand for one device with its unique serial number...
    - when it was ordered from a supplier, along with order details
    - when it was ordered by a customer, along with order details
    This is what I have now:

    Click image for larger version. 

Name:	db2.png 
Views:	50 
Size:	34.5 KB 
ID:	29999

    The problem is that it only shows products that have been both BOUGHT AND SOLD by our company. It doesn't show products that we ordered from a supplier but that haven't been assigned to a customer order yet. Can you help me with that please?


    2) The second query is similar to the 1st. I need to list items that are In-stock. The ones that we currently have in the house. In other words, they are assigned to a Supplier order, which we already received, and they are NOT assigned to a customer order (or are, but the order wasn't shipped yet).

    I am trying to work with something like this:

    Click image for larger version. 

Name:	db3.png 
Views:	50 
Size:	43.7 KB 
ID:	30000


    Any help would be highly appreciated! And excuse me for the foreign language, I tried to translate something in Photoshop.

    Thanks,
    Tomas

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Create a saved query:
    qrySNList: SELECT DISTINCT SerialNumber FROM tblObjednavkyDetail

    1. step after that - create a query like this (it is on fly, so I can't quarantee it works without some tweaking, but I think you get the general pricipe):
    SELECT sn.SerialNumber, odet.Cena AS PurchasePrice, prod.Cena As SalesPrice
    FROM ((qrySNList sn LEFT JOIN tblObjednavkyDetail odet ON odet.SerialNumber = sn.SerialNumber AND odet.FKObjednavkaID Is Not Null) LEFT JOIN tblObjednavkyDetail prod ON prod.SerialNumber = sn.SerialNumber AND prod.FKProduktID Is Not Null)
    (NB! I had to correct it a bit!)

    When this works, then continue with joining table tblObjednavky to odet and tblProdukty to prod.

    When this works also, then continue with joining table tblFirmy to tblObjednavky and tblFirmy to tblProdukty. NB! you have to join the table tblFirmy twice, so use different aliases for table tblFirmy in those joins.

    An afterthought: Use only outer joins (LEFT/RIGHT JOIN) in query - inner join excludes all rows where join condition is not valid (i.e. where part is only purchased or only sold).
    Last edited by ArviLaanemets; 08-18-2017 at 11:39 AM.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    This draft model(created for a different poster in March) may be of some value.


    Click image for larger version. 

Name:	QuickDBD_CustOrder_Extended_JED.jpg 
Views:	45 
Size:	47.1 KB 
ID:	30005

    I agree that Left/Right joins may be most appropriate. Some experimenting/testing will help you with a decision(s).

  4. #4
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by ArviLaanemets View Post
    Create a saved query:
    qrySNList: SELECT DISTINCT SerialNumber FROM tblObjednavkyDetail

    1. step after that - create a query like this (it is on fly, so I can't quarantee it works without some tweaking, but I think you get the general pricipe):
    SELECT sn.SerialNumber, odet.Cena AS PurchasePrice, prod.Cena As SalesPrice
    FROM ((qrySNList sn LEFT JOIN tblObjednavkyDetail odet ON odet.SerialNumber = sn.SerialNumber AND odet.FKObjednavkaID Is Not Null) LEFT JOIN tblObjednavkyDetail prod ON prod.SerialNumber = sn.SerialNumber AND prod.FKProduktID Is Not Null)
    (NB! I had to correct it a bit!)

    When this works, then continue with joining table tblObjednavky to odet and tblProdukty to prod.

    When this works also, then continue with joining table tblFirmy to tblObjednavky and tblFirmy to tblProdukty. NB! you have to join the table tblFirmy twice, so use different aliases for table tblFirmy in those joins.

    An afterthought: Use only outer joins (LEFT/RIGHT JOIN) in query - inner join excludes all rows where join condition is not valid (i.e. where part is only purchased or only sold).
    Hi,

    thank you for the answer. I am trying to implement your solution but I don't understand why there is "sn".SerialNumber, "odet", "prod", etc. Could you possibly send me how this would look in MS Access query Design View?

    Thank you.

    Orange, thank you as well, however, I don't want to edit my relationship model as this part of the database is only part of the whole and the rest works well.

    Thank you anyway.

    Tomas

  5. #5
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Can anyone help me, please?

    Thank you.
    Tomas

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Orange, thank you as well, however, I don't want to edit my relationship model as this part of the database is only part of the whole and the rest works well
    .

    You are not the first poster to say--"I've got too much invested and can't go back".

    I wasn't suggesting starting over. I was recommending a sample model that may help you revise what you have to ensure it meets your requirements. If you have a model that satisfies your test data and test scenarios, you end up with a blue print for your database.

    When I look at your tables and relationships and field names, I can not readily identify with them because of the language difference. When you can only deal with products that were ordered and sold, and can not see those that are not yet sold, it seems to be a join issue. With inner join only record that match in both table are selected.
    I think you want to see all that were ordered and those that were not yet sold.
    Research left join.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    I haven't too much free time at work, and I don't have Access at home currently, so I can't test anything complex. I spent 20 - 30 minutes a couple of days ago to design a small test database based on your 1st shcema, and find out, that current structure doesn't work. You have to define your business logick at first, and then redesign your database according with this.

    The main problem:
    It looks like you buy some articles, and then sell them. You have Orders table, which look like Purchase Orders, and you have Products table, which looks like Sold Articles (at first I thought you produce something ant then you sell produced Products/Articles, but after reading your comments is it not likely). But it remains unclear, which articles you buy, and where you register your sellings!

    So you need PurchaseOrders and SellingOrders. And you need an Articles table used by both Orders. And then you need a Storage table(s) to keep overview which Articles and how much you have currently (you cant sell anything you don't have yet, or at least you haven't Ordered to buy and payd yet). For Storage info, you must have Arrived Articles table, where you store info about arrived articles you have Purchase Orders for, and Sold Articles table, where you have info about articles you delivered on Selling orders - or youhave both movements in Storage Transactions table, and identify the movement type there.
    When you want now to follow moving of every individual article by serial number, then you have to add an additional level of complexity to this schema.
    P.S. Unless you order from supplier articles exactly with define serial numbers and clients order from you articles with define serial numbers (not very likely), the serial number info will be stored additionally to Storage Transactions info - the best solution will be additional table(s) with transition id, article id and serial number as minimum of columns.
    Last edited by ArviLaanemets; 08-25-2017 at 01:05 AM. Reason: Additiona consideration

  8. #8
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Hello and thank you for your answers,

    let me try to clarify how the "logic" of my database works. I was actually working on it with someone at this very forum. I have got these tables:
    tblCompanies - there is a foreign key to tblCompanyTypes (either a Supplier or a Customer)
    tblCompanyTypes - 2 records - customer, supplier
    tblOrders - each order is assigned a company, naturally... It is based on the company's type whether it's our order for a supplier of ours or it's a customer's order from us (yes, they look the same so no need to store it separately)
    tblOrderDetails - OrderID, ProductID, SERIAL NUMBER, Price
    (I am then working with a ton of VBA code to make it work as desired... Each Serial number can only be in 1 supplier and 1 customer order, all this works perfectly for me, I was consulting this at this forum)...
    tblProducts - Product info, it is assigned to tblCompany (the supplier/manufacturer - the same for us).
    (got many more tables for other purposes but these are the core ones)

    Perhaps my design makes more sense now as I described it. I understand that it may have brought some confusion the way I described it before. :-)


    Orange: I agree that it probably is a Join issue, I will take a look at it, thank you.

    Thank you very much and please let me know what you think.

    Thomas

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Arvi in that you should explicitly identify PurchaseOrders and SalesOrders to help with the modelling and design.

  10. #10
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    So I am trying to work on the join issue but I can't seem to make any progress... This is how it looks now:

    Click image for larger version. 

Name:	db4.png 
Views:	12 
Size:	50.4 KB 
ID:	30110

    When I try to filter the orders by company type (in the red circles at the bottom) as I was adviced earlier on this forum, it only displays items that have been both bought and sold, despite of setting left joins everywhere. Any advice, please?

    Thank you,
    Tomas

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 08-27-2014, 11:34 AM
  2. problem by subtracting the two queries
    By Koloss in forum Queries
    Replies: 10
    Last Post: 02-15-2013, 03:36 AM
  3. Iif and calculated queries problem
    By erringtonl in forum Queries
    Replies: 1
    Last Post: 01-20-2012, 02:20 PM
  4. Another problem with queries
    By Viggen66 in forum Queries
    Replies: 3
    Last Post: 02-23-2010, 05:01 PM
  5. Queries to Form Problem
    By PnerraD in forum Queries
    Replies: 1
    Last Post: 06-10-2006, 09:13 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums