Query needs to get data that matches in other tables

[dpk136]

I have a query that i'm trying to make.

Basically, what needs to happen is this query needs to look for all customers that have certain machines or equipment types, and have purchased them in between 2 dates.

The problem that i'm coming to is that the criteria for which machines to compare the customers to are in a table, the equipment types criteria is in another table and the dates are in another.

Can anyone help?
I would want to show any customers(tblComp) that have any of the machines or any of the equipment type and purchased between the dates

the tables look like this

tblMachineCrit
********************
*     MachCode     *
*------------------*
* CROLL2           *
* LATHE3           *
* MILL5            *
********************

tblTypeEquip
********************
*    EquipType     *
*------------------*
* END MILL         *
********************

tblDates
************************
* StartDate | EndDate  *
*  5/10/05  | 10/5/06  *
************************

David Kuhn
------------------

http://www.davidpkuhn.com

 

[rjoubert]

How do these tables link to the customers (tblComp)??? Without a link to a customer, the data in these three other tables is worthless.

 

[dpk136]

the customer table is linked to a machineowned table, this has the machine name. machineowned is linked to a equipment type table that has what type of machine it is. This is connected to the equipment type that i'm looking for.

David Kuhn
------------------

http://www.davidpkuhn.com

 

[rjoubert]

HOW do they link to each other??? For example, does the Customer table have a machine ID code that links to the machine table, or does the machine table have a customer ID field that links back to the Customer table? It might help to post the structure of your tables. That way, someone can actually put together a nice little query to meet your needs.

 

[rjoubert]

David...BTW, are you, by any chance, related to a Steve Kuhn who lives in the Buffalo, NY area?

 

[dpk136]

I figured it out. I actually made this harder than it needed to be. It is just a join between a couple of tables (INNER).

The only problem that i'm having is the dates. How do i get it so i only get the dates purchased between the Start and End date in the tblDates table. I'm not sure how this would work.

David Kuhn
------------------

http://www.davidpkuhn.com

 

[lespaul]

what's the SQL that you're running?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi