473,320 Members | 1,600 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Is this possible with a query??

7
Hi,

I have been building a small CRM application in Access + mysql that will be used to keep track of customers and opportunities.

I have a one-to-many relation between customers and opportunities and would like to create a query that gives me an overview off all opportunities per customer.

example:
Customer 1
opp1
opp2

Customer 2
Opp1
....

I know this is possible in a report but than I cannot export the data to excel.

All help or advise on how to achieve this differently would be greatly appreciated.

siech
Oct 28 '07 #1
8 1401
Jim Doherty
897 Expert 512MB
Hi,

I have been building a small CRM application in Access + mysql that will be used to keep track of customers and opportunities.

I have a one-to-many relation between customers and opportunities and would like to create a query that gives me an overview off all opportunities per customer.

example:
Customer 1
opp1
opp2

Customer 2
Opp1
....

I know this is possible in a report but than I cannot export the data to excel.

All help or advise on how to achieve this differently would be greatly appreciated.

siech
Is it possible with a query? the answer is 'yes' however post the structure of your two tables ie: names and datatypes that way any response can take into consideration explicitly the names of your tables and fields in any SQL or advice that we might provide

Regards

Jim :)
Oct 28 '07 #2
siech
7
Hi Jim,

Thank you for your fast response and sorry for my late reply :(.

Hereunder an export of the table structure from mysql:

CREATE TABLE `customer` (
`CustomerID` int(11) NOT NULL auto_increment,
`SectorID` int(11) default '0',
`StatusID` int(11) default '0',
`CustHandledByID` int(11) default '0',
`CustCreatedByID` int(11) default '0',
`CountryID` int(11) default '0',
`CategoryID` int(11) default '0',
`SubsidiaryID` int(11) default '0',
`CustName` varchar(40) NOT NULL,
`Address` varchar(60) default NULL,
`City` varchar(50) default NULL,
`ZipCode` varchar(10) default NULL,
`Phone` varchar(24) default NULL,
`Fax` varchar(24) default NULL,
`Email` varchar(50) default NULL,
`siteurl` varchar(50) default NULL,
`e-commerce` tinyint(1) default NULL,
`create_date` datetime default NULL,
`LastCall_date` datetime default NULL,
`Recall_date` datetime default NULL,
`priority` varchar(50) default NULL,
`Leadfrom` varchar(50) default NULL,
`VAT` varchar(50) default NULL,
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`CustomerID`),
UNIQUE KEY `CustName` (`CustName`),
KEY `CategoryID` (`CategoryID`),
KEY `CountryID` (`CountryID`),
KEY `CustCreatedByID` (`CustCreatedByID`),
KEY `CustHandledByID` (`CustHandledByID`),
KEY `StatusID` (`StatusID`),
KEY `SectorID` (`SectorID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `opportunities`
--
CREATE TABLE `opportunities` (
`OpportunityID` int(11) NOT NULL auto_increment,
`CustomerID` int(11) default NULL,
`OppcreateDate` datetime default NULL,
`OppCreatedByID` int(11) default NULL,
`OppHandledByID` int(11) default NULL,
`OppTargetDate` datetime default NULL,
`OppStatusID` tinyint(11) default NULL,
`OppSubject` varchar(255) default NULL,
`OppDescription` mediumtext,
`OppNrOfCoupons` int(11) default NULL,
`OppPricePerCoupon` double default NULL,
`OppOtherRevenue` double default NULL,
PRIMARY KEY (`OpportunityID`),
KEY `CustomerID` (`CustomerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Many thanks for your help and any comments/recommendations for the used datatypes is also always more than welcome.

Siech
Oct 30 '07 #3
Jim Doherty
897 Expert 512MB
Hi Jim,

Thank you for your fast response and sorry for my late reply :(.

Hereunder an export of the table structure from mysql:

CREATE TABLE `customer` (
`CustomerID` int(11) NOT NULL auto_increment,
`SectorID` int(11) default '0',
`StatusID` int(11) default '0',
`CustHandledByID` int(11) default '0',
`CustCreatedByID` int(11) default '0',
`CountryID` int(11) default '0',
`CategoryID` int(11) default '0',
`SubsidiaryID` int(11) default '0',
`CustName` varchar(40) NOT NULL,
`Address` varchar(60) default NULL,
`City` varchar(50) default NULL,
`ZipCode` varchar(10) default NULL,
`Phone` varchar(24) default NULL,
`Fax` varchar(24) default NULL,
`Email` varchar(50) default NULL,
`siteurl` varchar(50) default NULL,
`e-commerce` tinyint(1) default NULL,
`create_date` datetime default NULL,
`LastCall_date` datetime default NULL,
`Recall_date` datetime default NULL,
`priority` varchar(50) default NULL,
`Leadfrom` varchar(50) default NULL,
`VAT` varchar(50) default NULL,
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`CustomerID`),
UNIQUE KEY `CustName` (`CustName`),
KEY `CategoryID` (`CategoryID`),
KEY `CountryID` (`CountryID`),
KEY `CustCreatedByID` (`CustCreatedByID`),
KEY `CustHandledByID` (`CustHandledByID`),
KEY `StatusID` (`StatusID`),
KEY `SectorID` (`SectorID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `opportunities`
--
CREATE TABLE `opportunities` (
`OpportunityID` int(11) NOT NULL auto_increment,
`CustomerID` int(11) default NULL,
`OppcreateDate` datetime default NULL,
`OppCreatedByID` int(11) default NULL,
`OppHandledByID` int(11) default NULL,
`OppTargetDate` datetime default NULL,
`OppStatusID` tinyint(11) default NULL,
`OppSubject` varchar(255) default NULL,
`OppDescription` mediumtext,
`OppNrOfCoupons` int(11) default NULL,
`OppPricePerCoupon` double default NULL,
`OppOtherRevenue` double default NULL,
PRIMARY KEY (`OpportunityID`),
KEY `CustomerID` (`CustomerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Many thanks for your help and any comments/recommendations for the used datatypes is also always more than welcome.

Siech

OK!... well I assume you have your ODBC connection to your MySQL database setup correctly and that you have 'Linked' your tables properly to the Access database window and that you can see your data merely by double clicking on the tables to open them up.

You should see them depicted by a World Globe indicating a linked ODBC datasource. The purpose then is to introduce queryability from Access directly with the MySQL database.

Now without going into the design of your database to advise you on data normalisation principles (a huge topic) together with correct useage of datatypes (again a huge topic) I am going to focus on how you would simply query that data from within Access.

Now that your tables are available in the Access interface you can query them and also 'insert' and 'edit' the data as though it were physically present in the Access database itself. (we know that not to be the case of course its stored in the MYSQL database)

You can then using the query button, create queries and your requirement at the moment is very basic indeed, in other words "I have a customer table and I want to query and list their opportunities."

The potential combination of separate questions of the data could be huge to the magnitude of a comparison of every field by every field so logically you are going to define your requirement as time goes by and build those queries to suit your purpose.

I have created some simple queries below which you can (and this is because you helpfully posted your table structure which I have replicated and tested it on an MYSQL box so I know it works!!) copy and paste directly into the query SQL window in Access and then you switch to the 'design view ' to see a graphical representation of that query as it is.

I have purposefully kept the included columns brief (because I do not obviously know what you wish to output) but you will see that the general principle is to join both tables together and produce a matrix display of column data that is in accordance with 'criteria' you supply to restrict the resultant dataset.

The below examples as you will see involve INNER JOINS and RIGHT JOINS. These JOIN properties are represented in the Access query graphical display as the LINE between the tables displayed with or without an 'arrow' displayed.

This 'Arrow' is an important distinction between the records you want to actually see returned (to put it simply) In the case of an INNER JOIN in will only display customers who actually have opportunities together with those opportunities and in the case of a RIGHT JOIN it will display all customers irrespective of whether they have an opportunity or not.


You can get ALL of these queries outputting to Excel automatically from within Access either using a macro (if you really must and are a beginner learning - the macro command is the OutputTo Action in macros) but the better option is using VBA code (where you will have much more control over any error handling that might become evident in time. You will have to use VBA if you wish to invoke AUTOMATION which is the power programming aspect of Microsoft Office and Access in how it communicates with the likes of WORD, EXCEL and so on)

Below are the examples for you to cut and paste into your Access database SQL window.

You can save each one as a separate query if you wish its a matter for you. As an aside you can create forms and reports based on everything you see here. In addition, when it comes to the automation of records between the likes of Access and Excel I like many others on this forum have examples of how you can do that however, get your head round this querying aspect first, and how to do that and then introduce the automation bit later should you need to as a separate thread. People including myself will be only to willing to help you on that.

1) All Customers only if they have an opportunity

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT customer.CustomerID, customer.CustName, opportunities.OpportunityID, opportunities.OppDescription
  3. FROM opportunities INNER JOIN customer ON opportunities.CustomerID = customer.CustomerID;
  4.  
2) All Customers who have had an opportunity on 31st October 2007 (notice the USA format in the SQL but reversed according to your regional settings))

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT customer.CustomerID, customer.CustName, opportunities.OpportunityID, opportunities.OppDescription, opportunities.OppcreateDate
  3. FROM opportunities INNER JOIN customer ON opportunities.CustomerID = customer.CustomerID
  4. WHERE (((opportunities.OppcreateDate)=#10/31/2007#));
  5.  
3) All Customers whether or not they have an opportunity

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT customer.CustomerID, customer.CustName, opportunities.OpportunityID, opportunities.OppDescription
  3. FROM opportunities RIGHT JOIN customer ON opportunities.CustomerID = customer.CustomerID;
  4.  
4) A Customer who must exist called 'Jim Doherty' and all if any of his opportunities

Expand|Select|Wrap|Line Numbers
  1. SELECT customer.CustomerID, customer.CustName, opportunities.OpportunityID, opportunities.OppDescription
  2. FROM opportunities RIGHT JOIN customer ON opportunities.CustomerID = customer.CustomerID
  3. WHERE (((customer.CustName)="Jim Doherty"));
5) All Customers whose names begin with 'Jim' and all if any of their opportunities

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT customer.CustomerID, customer.CustName, opportunities.OpportunityID, opportunities.OppDescription
  3. FROM opportunities RIGHT JOIN customer ON opportunities.CustomerID = customer.CustomerID
  4. WHERE (((customer.CustName) Like "Jim*"));
  5.  
6) A list of all customers together with a count of all the opportunities for each customer sorted by the customer with the most opportunities to the top

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT customer.CustomerID, customer.CustName, Count(opportunities.OpportunityID) AS CountOfOpportunityID
  3. FROM opportunities RIGHT JOIN customer ON opportunities.CustomerID = customer.CustomerID
  4. GROUP BY customer.CustomerID, customer.CustName
  5. ORDER BY Count(opportunities.OpportunityID) DESC;
  6.  
I apologise for the rather long thread but I think I might have covered the basics for you

Hope this helps you

Regards

Jim :)
Oct 31 '07 #4
siech
7
Jim,

Many thanks for the great reply, I think that this post allone could replace many complicated tutorials for anybody wishing to start with sql.

I red your post carefully for a few times and learned some interesting new things but could not find the answer to my initial question :(

L'ets say I use your example "3) All Customers whether or not they have an opportunity".

SELECT customer.CustomerID, customer.CustName, opportunities.OpportunityID, opportunities.OppDescription
FROM opportunities RIGHT JOIN customer ON opportunities.CustomerID = customer.CustomerID;
This query gives an overview of all opportunities with in the first column the customer name.

What I would like to achieve is to have an overview on a per customer base of all opportunities. so the output I want to achieve is:

Line 1: cust1
Line 2: opp 1 of cust 1
Line 3: opp 2 of cust 1
Line 4: opp 3 of cust 1
Line 5: cust2
Line 6: opp 1 of cust 2
Line 7: opp 2 of cust 2
....


Or did you mean that I can only achieve this when exporting to excel ??

Many thanks,

Siech
Oct 31 '07 #5
What you could to is define two queries and 'UNION' the results.
You have to make sure the results of the queries have the same number of columns and formats.

e.g.

Customer querie:
select customer_name, " " as oppportunity from customer

Opportunitie querie:
select customer.customer_name, opportunity.opdescription
from customer left join opportunity.

Join querie:
select * from cust querie
union all select * from oppportunity querie

sort the join querie on customer name and opportunity.description

this results into

cust 1
cust 1 opp1
cust 1 opp2
cust2
cust3
cust3 opp3

----- or

Customer querie:
select customer_name as sortname, customer_name, " " as oppportunity from customer

Opportunitie querie:
select customer.customer_name as sortname, " " as customer_name opportunity.opdescription
from customer left join opportunity.

Join querie:
select * from cust querie
union all select * from oppportunity querie

sort the join querie on sortname and opportunity.description
and hide the sortname

this results into

cust 1
............. opp1
............. opp2
cust2
cust3
............. opp3

(the dots are not shown, only to represent <spaces>)
Oct 31 '07 #6
Jim Doherty
897 Expert 512MB
Jim,

Many thanks for the great reply, I think that this post allone could replace many complicated tutorials for anybody wishing to start with sql.

I red your post carefully for a few times and learned some interesting new things but could not find the answer to my initial question :(

L'ets say I use your example "3) All Customers whether or not they have an opportunity".



This query gives an overview of all opportunities with in the first column the customer name.

What I would like to achieve is to have an overview on a per customer base of all opportunities. so the output I want to achieve is:

Line 1: cust1
Line 2: opp 1 of cust 1
Line 3: opp 2 of cust 1
Line 4: opp 3 of cust 1
Line 5: cust2
Line 6: opp 1 of cust 2
Line 7: opp 2 of cust 2
....


Or did you mean that I can only achieve this when exporting to excel ??

Many thanks,

Siech
A single column only as output? Had I of known that from the outset I would have rounded out my reply posting as per Albert's. Go with that!

Jim :)
Oct 31 '07 #7
siech
7
Great Alber,

This gives me exactly what I wanted to achieve.

I add the full queries hereunder as a future reference for others:

Customer querie [custnameqry]:
SELECT customer.CustName AS sortname, customer.CustName, "" AS opportunity
FROM customer;

Opportunitie querie [custnameqry2]:
SELECT customer.CustName AS sortname, "" AS customername, opportunities.OppDescription
FROM customer LEFT JOIN opportunities ON customer.CustomerID = opportunities.CustomerID;

Join querie:
select * from custnameqry2
union all select * from custnameqry order by sortname, opportunities.OppDescription

NOTE: The second query must be selected first in the join.


Although this gives exactly the result that I wanted, this method has raised another question.
The field "opportunities.Description" is in a "rich text" format which means the content is saved in mysql with all tags "<div> blabla <div>".
I set this field to rich text in access so the output in forms,queries is clean but in the join query, all the tags are visible??

Can I set this field property from within the query?

Many thanks,

Siech
Oct 31 '07 #8
siech
7
Jim,

You are right, I might have replied to quickly! I will need of course more colums in the final report but I wanted to simplify the question.

The objective is to obtain the result of this query:
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT customer.CustName, handlers.handlers AS CustHandler, handlers_1.handlers AS OppHandler, opportunities.OppcreateDate, opportunities.OppTargetDate, opportunitiesstatus.Status, opportunities.OppSubject, opportunities.OppDescription, opportunities.OppNrOfCoupons, opportunities.OppPricePerCoupon, opportunities.OppOtherRevenue, ([opportunities]![OppNrOfCoupons]*[opportunities]![OppPricePerCoupon])+[opportunities]![OppOtherRevenue] AS [Opp Turnover]
  3. FROM (((customer INNER JOIN opportunities ON customer.CustomerID = opportunities.CustomerID) INNER JOIN opportunitiesstatus ON opportunities.OppStatusID = opportunitiesstatus.OppStatusID) INNER JOIN handlers ON customer.CustHandledByID = handlers.HandlerID) INNER JOIN handlers AS handlers_1 ON opportunities.OppHandledByID = handlers_1.HandlerID
  4. ORDER BY customer.CustName;
  5.  
But with the opportunities classified by customer as stated above. But when I try to add colums to the solution above, It doesn't work??

Am I miss something here?

Many thanks,

siech
Oct 31 '07 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Frank Maestas | last post by:
Hello, I have been trying to figure this out but no luck. Lets say I have a query that searches for people living in Colorado. That results in a list on a "results.php" page. Now I want to...
22
by: Robert Brown | last post by:
suppose I have the following table: CREATE TABLE (int level, color varchar, length int, width int, height int) It has the following rows 1, "RED", 8, 10, 12 2, NULL, NULL, NULL, 20...
4
by: sah | last post by:
I need some help with the following query: DECLARE @SRV VARCHAR(20), @date smalldatetime SET @SRV = (select @@servername) SET @date = '20040901' select Srv_Name = @SRV, DB_Name = 'DB_NAME',...
3
by: Jonathan | last post by:
Hi all! For a match schedule I would like to find all possible combinations of teams playing home and away (without teams playing to themselves of course). I now the simple version works...
4
by: wnstnsmith | last post by:
Dear all, Suppose there is a table whose records contain a textfield TXT, and a query that returns a subset of that table. Is it possible to have that query return all the TXT-fields gathered...
3
by: Pea | last post by:
Is it possible to get the average in a query where I have multiple criteria? Here's an abbreviated example of the query in design view: Fields: USERID DATE TIME ID_TYPE...
5
by: Chris Ochs | last post by:
It doesn't currently seem possible to switch between different users using SET SESSION AUTHORIZATION. If I log in as the superuser and switch to another user that works, but if I then switch to a...
2
by: jim Bob | last post by:
Hi, I have a query with the criteria of (!..) Or !.. When I provide the value from qmylist, a second pop up appears waiting for the value from lstResults. If I enter nothing and click ok, it...
0
by: hdogg | last post by:
I am using oracle with php. I am trying to accomplish a left outer join. Here are the 3 queries. Query 1, contains all the data on the left the will show up with data on the right. Query 1 =...
2
by: DuncanIdaho | last post by:
Hi Apologies if this is similar to a (very) recent post. I was wondering if it is possible to execute an update query that contains a select statement on an MS access 2000 database. I have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.