Results 1 to 3 of 3
  1. #1
    PureLoneWolf's Avatar
    PureLoneWolf is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Frankfurt, Germany
    Posts
    2

    Query for table, based on criteria from other tables

    Hi all

    Apologies for my first post being a question, but I wonder if someone would be willing to help me. I am very new to access and need to create a query that returns results, based on criteria selected by the user. The database is for a restaurant and I am looking to get information about who sold what based on categories.

    There are 5 tables - 1 that contains the information that needs to be queried and the other 4 contain the criteria needed to return the results.

    Criteria Tables:

    kassi - User table: Contains fields kanr (key field) and Name
    artgrp2 - Main group table: Contains fields artgrp2 (key field) and maingroupname


    artgrp - Sub-Group table: Contains fields artgrp (key field), artgrp2 and subgroupname
    artikel - Menu Items: Contains artnr (key field), itemname and artgrp

    Information Table:

    kasspro - Purchase History table: Contains ID (key field), kanr, artgrp, itemname, numbersold

    I need to return information from the kasspro table, but I need it to prompt for the following criteria:

    Username (Table: kassi, Field: name)
    Main Group (Table: artgrp2, Field: name)
    Sub Group (Table: artgrp, Field: name) Also needs the possibility for ALL

    And then return all records/fields from the kasspro table that meet the criteria.

    I haven't the faintest idea about where to start unfortunately, so I would be very much appreciative of any assistance.

    Thanks

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Till someone comes along, a small question :
    why do we store
    artgrp
    and
    itemname
    in table kasspro.
    Can we not store only
    artnr
    instead ?

    Thanks

  3. #3
    PureLoneWolf's Avatar
    PureLoneWolf is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Frankfurt, Germany
    Posts
    2
    Quote Originally Posted by recyan View Post
    Till someone comes along, a small question :
    why do we store
    artgrp
    and
    itemname
    in table kasspro.
    Can we not store only
    artnr
    instead ?

    Thanks
    Hiya

    Thanks for the response. In short form, I am not sure why it was done this way and I can't change it without changing the way everything works. I have been tasked to provide this extra information from a database that was created by, shall we say, a disgruntled ex-employee..and he isn't willing to assist.

    I am certain that I am not skilled enough to re-write everything, so it would be fantastic if someone could navigate around it

    Thanks again

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

Similar Threads

  1. Query isnt updating table based on criteria
    By shabbaranks in forum Queries
    Replies: 12
    Last Post: 01-10-2012, 11:51 AM
  2. data field in query twice based on criteria
    By sandyg in forum Queries
    Replies: 1
    Last Post: 09-29-2011, 07:47 AM
  3. Matching based on criteria from multiple tables
    By Jonpro03 in forum Database Design
    Replies: 13
    Last Post: 08-04-2011, 10:29 AM
  4. IIF Criteria based on another table
    By BED in forum Queries
    Replies: 6
    Last Post: 11-24-2010, 01:55 PM
  5. query - criteria based on another row field value
    By techexpressinc in forum Queries
    Replies: 1
    Last Post: 05-29-2009, 04:17 PM

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