Finding Values in table

M

mbing916

I have a big report that is listing customers and the reps/companies they
worked with. I need to find out which of the customers have worked with a
company more than once and if it was with the same company multiple times or
different companies. Any ideas how to do this?
I tried to do a duplicate query that shows when each customer shows up more
than once, but then I need to find out if it is the same company or a
different one. Another dup. query??
The reports are large otherwise I would probably have just used excel.

THanks in advance!
 
K

KARL DEWEY

Post your table structure - field names and datatype along with a sample of
your data.
 
M

mbing916

The table looks something like this:
Col. A Col B Col C Col D
Col E
Customer # Customer Name Cust. address Booking Date rep/
Company
1234 Joes Trucking Main Street 2/5/07
ABC Company
5678 Mabels Diner Elm Street 3/14/07
XYZ Industries
3456 Java Shop Fifth Ave 4/12/07
1234 Inc
1234 Joes Trucking Main Street 8/1/07
ABC Company
5678 Mabels Diner Elm Street 916/07
1234 Inc

I need query help to find out Joes Trucking has two deals with ABC Company
and that Mabels Diner has two deals, one with XYZ and the other with 1234.
It can be done in mulitple queries if necessary, but the simpler the better
since I need to duplicate this for seven years of booking reports.

The data consists of names and numbers.
 
K

KARL DEWEY

Try this ---
SELECT Format([BookingDate],"yyyy") AS [Booking year], mbing916.CustomerID,
mbing916.Name, mbing916.Address, mbing916.Rep_Company,
Count(mbing916.Rep_Company) AS [Number of deals]
FROM mbing916
GROUP BY Format([BookingDate],"yyyy"), mbing916.CustomerID, mbing916.Name,
mbing916.Address, mbing916.Rep_Company;
 
M

mbing916

Okay, maybe I just didn't understand what you were trying to tell me, because
I am having trouble getting that to work. Please clarify exactly where I
need to put that formula for it to work. THanks!

KARL DEWEY said:
Try this ---
SELECT Format([BookingDate],"yyyy") AS [Booking year], mbing916.CustomerID,
mbing916.Name, mbing916.Address, mbing916.Rep_Company,
Count(mbing916.Rep_Company) AS [Number of deals]
FROM mbing916
GROUP BY Format([BookingDate],"yyyy"), mbing916.CustomerID, mbing916.Name,
mbing916.Address, mbing916.Rep_Company;

--
KARL DEWEY
Build a little - Test a little


mbing916 said:
The table looks something like this:
Col. A Col B Col C Col D
Col E
Customer # Customer Name Cust. address Booking Date rep/
Company
1234 Joes Trucking Main Street 2/5/07
ABC Company
5678 Mabels Diner Elm Street 3/14/07
XYZ Industries
3456 Java Shop Fifth Ave 4/12/07
1234 Inc
1234 Joes Trucking Main Street 8/1/07
ABC Company
5678 Mabels Diner Elm Street 916/07
1234 Inc

I need query help to find out Joes Trucking has two deals with ABC Company
and that Mabels Diner has two deals, one with XYZ and the other with 1234.
It can be done in mulitple queries if necessary, but the simpler the better
since I need to duplicate this for seven years of booking reports.

The data consists of names and numbers.
 
K

KARL DEWEY

What I posted was a SQL statement for a query. Create a new query and then
click on menu VIEW - SQL View to open another window. Paste what I posted in
the window but edit it to replace mbing916 with your table name.
This query will give you what you asked for.
This is the sample results --
Booking year CustomerID Name Address Rep_Company Number of deals
2007 1234 Joes Trucking Main Street ABC Company 2
2007 3456 Java Shop Fifth Ave 1234 Inc 1
2007 5678 Mabels Diner Elm Street 1234 Inc 1
2007 5678 Mabels Diner Elm Street XYZ Industries 1

From this raw data ---
CustomerID Name Address BookingDate Rep_Company
1234 Joes Trucking Main Street 02/05/2007 ABC Company
5678 Mabels Diner Elm Street 03/14/2007 XYZ Industries
3456 Java Shop Fifth Ave 04/12/2007 1234 Inc
1234 Joes Trucking Main Street 08/01/2007 ABC Company
5678 Mabels Diner Elm Street 09/16/2007 1234 Inc
--
KARL DEWEY
Build a little - Test a little


mbing916 said:
Okay, maybe I just didn't understand what you were trying to tell me, because
I am having trouble getting that to work. Please clarify exactly where I
need to put that formula for it to work. THanks!

KARL DEWEY said:
Try this ---
SELECT Format([BookingDate],"yyyy") AS [Booking year], mbing916.CustomerID,
mbing916.Name, mbing916.Address, mbing916.Rep_Company,
Count(mbing916.Rep_Company) AS [Number of deals]
FROM mbing916
GROUP BY Format([BookingDate],"yyyy"), mbing916.CustomerID, mbing916.Name,
mbing916.Address, mbing916.Rep_Company;

--
KARL DEWEY
Build a little - Test a little


mbing916 said:
The table looks something like this:
Col. A Col B Col C Col D
Col E
Customer # Customer Name Cust. address Booking Date rep/
Company
1234 Joes Trucking Main Street 2/5/07
ABC Company
5678 Mabels Diner Elm Street 3/14/07
XYZ Industries
3456 Java Shop Fifth Ave 4/12/07
1234 Inc
1234 Joes Trucking Main Street 8/1/07
ABC Company
5678 Mabels Diner Elm Street 916/07
1234 Inc

I need query help to find out Joes Trucking has two deals with ABC Company
and that Mabels Diner has two deals, one with XYZ and the other with 1234.
It can be done in mulitple queries if necessary, but the simpler the better
since I need to duplicate this for seven years of booking reports.

The data consists of names and numbers.
:

Post your table structure - field names and datatype along with a sample of
your data.
--
KARL DEWEY
Build a little - Test a little


:

I have a big report that is listing customers and the reps/companies they
worked with. I need to find out which of the customers have worked with a
company more than once and if it was with the same company multiple times or
different companies. Any ideas how to do this?
I tried to do a duplicate query that shows when each customer shows up more
than once, but then I need to find out if it is the same company or a
different one. Another dup. query??
The reports are large otherwise I would probably have just used excel.

THanks in advance!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top