Query for table, based on criteria from other tables

[PureLoneWolf]

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

[recyan]

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

[PureLoneWolf]

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