Results 1 to 4 of 4
  1. #1
    icontatt is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    2

    Query help

    Hey Guys!

    I`m working on a prject and i don`t know if a query can handl it like i want it to.

    i got 5 tables

    1.: invoice

    id subject amount
    1 Invoice 1 10€
    2 Invoice 2 20€


    2.: productsinvoice

    id product invoiceid (=invoice id)
    1 Desk 1
    2 Table 1




    3.: incominginvoice
    id subject amount
    1 Invoice 2013-1 5€
    2 Invoice 2013-2 2€


    4.: productsincominginvoice
    id product invoiceid (=incoming invoice id)
    1 food 1
    2 food 2


    5. tax informations about table 1 and 2

    id prodid IsIncomingInvoice(bool) (false -> invocie, true ->...) taxinformation
    1 1 false xyz
    2 1 false abc

    i want to join all information, so that i see every product, the products invoice subject and the tax informations.

    i can easily (left f.a.) join 2 with 1
    and also
    join 4 with 3

    sow at least i got to join the tax informations. 1 problem is, that there can be "n" taxinformations for the same product (i can overwork my taxinformations for a product and also need the old settings to be saved in the table)

    so if i do a single join on prodid, i can`t differ with my "isincomminginvoice" if the prodID ist prodID of 2 or 4.

    and this is the part i don`t know how to handl it.

    is it posible to first select for example all invoices of 5, and joining with this selected table?
    like using a filter....

    pls give me tips

    mario
    Last edited by icontatt; 06-09-2013 at 09:42 AM.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Which tax information do you want for a product - the last one entered to table?

    Want to provide db for analysis? Follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    icontatt is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    2
    Quote Originally Posted by June7 View Post
    Which tax information do you want for a product - the last one entered to table?
    That are just some information about how to tax the product. Which VAT and so on.
    The main Problem is, that if i go:

    SELECT productsinvoice
    FROM 2
    LEFT JOIN 5
    ON 2.id = 5.prodid;

    it joins all prodid with id's. thats clear to me. but i only what to join those, where 5.IsIncomingInvoice = false

    mario

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I suspect will need to do selective query before joining. Queries can be joined to tables or other queries. So do a query that returns the desired tax records then join query to table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Tags for this Thread

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