Find Multiple Records w/ 1 matching field & 1 non-matching field

[Mancolt]

Hey everyone,



I am very new to Access, and have little to no working knowledge of SQL or programming in general. My background is finance/accounting, but I've been given a database at work to take over. We've been using this database to store all of the information about equipment we're purchasing. All of this is stored in 1 table (this is how it was before I got it). I have been asked to maintain this table (update as new equipment is added, prices change, etc) and to create reports based off of this data.

I've searched google extensively for an answer to my question, but haven't been able to find anything. I'm not even sure how to phrase it to find the result, which is definitely part of the problem.

One of the reports that I was asked to create is to find the same equipment that occurs in multiple departments. So I'm looking for records with the same "Model #" that have different values in the "Department" field. Doesn't matter if it shows up in 2 different departments, or 20 different departments, we'd want to see all of those records. Conversely, if there are 5 records with the same "Model #" but they all have the same "Department" as well, we wouldn't want to see those.

I think my issue is with the query syntax. I believe I'll be able to format the data in the report once I have the query pulling the correct information.

I apologize if this was not a great description...I'm slowly figuring my way through Access.

Any help you all could provide would be greatly appreciated!

[TheShabz]

Before you go any further, I'd highly recommend normalizing your database. All that information should not be stored in one table. Do a search for "normalization" and "relational database" for more info. It will save you many headaches down the line.

What you would want to do is first create a query that will give you all of the model numbers with their departments. I assume each combination has it's own record, for example:
Model# | Dept
1 | A
1 | B
2 | C

instead of:
1| A, B
2| C

Start with a query:
SELECT ModelNumber, Department
FROM tblName
GROUP BY ModelNumber, Department

Save this query as whatever, let's call it qryModels

Now run a second query:
SELECT ModelNumber, Count(ModelNumber) AS CountOfModelNumber
FROM qryModels
GROUP By ModelNumber
HAVING Count(ModelNumber) > 1

That will return you a list of all Model Numbers that are in multiple departments and tell you how many departments they are in.