|
| |
|
|
patricksims
Posts: 37
Joined: 7/7/2005
Status: offline
 |
Inner join two tables in DRW - 7/8/2005 17:26:29
I am trying to use a custom SQL query in DRW to retrieve records/data from two tables into one results area. Below is the syntax I am using, but I get the following error:
Description: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
Number: -2147217904 (0x80040E10)
Source: Microsoft OLE DB Provider for ODBC Drivers
I know I usually get this when I don't provide default date values, but that is not the case here. Any help would be greatly appreciated.
SELECT Sales.agent_name AS Agent,COUNT(Sales.home_phone) AS "MCC",COUNT(Sales.birth_city) AS "MCC Upsells",round((COUNT(Sales.birth_city)/COUNT(Sales.home_phone)*100),1) AS "MCC Upsell %",COUNT(eclublog.home_phone) AS PVC,COUNT(eclublog.upsold) AS "PVC Upsell",round((COUNT(eclublog.upsold)/COUNT(eclub.home_phone)*100),1) AS "PVC Upsold %"
FROM eclublog INNER JOIN Sales ON eclublog.user_name=Sales.agent_name
WHERE (Sales.start BETWEEN '::begin::' AND '::end::' AND (DateValue([eclublog.timestamp]) BETWEEN DateValue('::begin::') AND DateValue('::end::')))
GROUP BY Sales.agent_name ORDER BY COUNT(Sales.home_phone) DESC
|
|
|
|
patricksims
Posts: 37
Joined: 7/7/2005
Status: offline
|
RE: Inner join two tables in DRW - 7/8/2005 21:38:56
I don't think the alias is the root of the problem with my DRW results. I will gladly change the alias (even if it is less cosmetically clean), but I think there is something wrong with my query syntax. Thanks for your insight.
| |
|
|
|
patricksims
Posts: 37
Joined: 7/7/2005
Status: offline
|
RE: Inner join two tables in DRW - 7/11/2005 13:40:00
Ok. I replaced the quotes around the aliases with brackets and I still get the same error. If you will notice in my query, I am using a date related criteria for each table. Could that be the problem? In one table I am querying dates that are in ISO format as text (I know I could change it to date/time but I have my reason for not doing so). In the second table, I am querying a date/time value. I have set the default values within the DRW as 1/1/00. I can query each separately with no problem. When I try to build the query in SQL view of Access, it tells me the query is too complex to be evaluated.
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Inner join two tables in DRW - 7/11/2005 18:44:14
Have you tried breaking it down to narrow the source of the problem? For example, does this work?
SELECT Sales.agent_name AS Agent,COUNT(Sales.home_phone) AS "MCC",COUNT(Sales.birth_city) AS "MCC Upsells",round((COUNT(Sales.birth_city)/COUNT(Sales.home_phone)*100),1) AS "MCC Upsell %",COUNT(eclublog.home_phone) AS PVC,COUNT(eclublog.upsold) AS "PVC Upsell",round((COUNT(eclublog.upsold)/COUNT(eclub.home_phone)*100),1) AS "PVC Upsold %"
FROM eclublog INNER JOIN Sales ON eclublog.user_name=Sales.agent_name
GROUP BY Sales.agent_name ORDER BY COUNT(Sales.home_phone) DESC
What about this?
SELECT Sales.agent_name AS Agent,COUNT(Sales.home_phone) AS "MCC",COUNT(Sales.birth_city) AS "MCC Upsells",round((COUNT(Sales.birth_city)/COUNT(Sales.home_phone)*100),1) AS "MCC Upsell %",COUNT(eclublog.home_phone) AS PVC,COUNT(eclublog.upsold) AS "PVC Upsell",round((COUNT(eclublog.upsold)/COUNT(eclub.home_phone)*100),1) AS "PVC Upsold %"
FROM eclublog INNER JOIN Sales ON eclublog.user_name=Sales.agent_name
WHERE (Sales.start BETWEEN '::begin::' AND '::end::')
GROUP BY Sales.agent_name ORDER BY COUNT(Sales.home_phone) DESC
or this?
SELECT Sales.agent_name AS Agent,COUNT(Sales.home_phone) AS "MCC",COUNT(Sales.birth_city) AS "MCC Upsells",round((COUNT(Sales.birth_city)/COUNT(Sales.home_phone)*100),1) AS "MCC Upsell %",COUNT(eclublog.home_phone) AS PVC,COUNT(eclublog.upsold) AS "PVC Upsell",round((COUNT(eclublog.upsold)/COUNT(eclub.home_phone)*100),1) AS "PVC Upsold %"
FROM eclublog INNER JOIN Sales ON eclublog.user_name=Sales.agent_name
WHERE (DateValue([eclublog.timestamp]) BETWEEN DateValue('::begin::') AND DateValue('::end::'))
GROUP BY Sales.agent_name ORDER BY COUNT(Sales.home_phone) DESC
That should at least give you an idea as to where to focus your attention.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Inner join two tables in DRW - 7/12/2005 12:06:48
Does this return any results?
SELECT Sales.agent_name AS Agent, COUNT(Sales.home_phone) AS MCC,COUNT(Sales.birth_city) AS MCC_Upsells, round((COUNT(Sales.birth_city)/COUNT(Sales.home_phone)*100),1) AS MCC_Upsell_Percentage", COUNT(eclublog.home_phone) AS PVC, COUNT(eclublog.upsold) AS PVC_Upsell, round((COUNT(eclublog.upsold)/COUNT(eclub.home_phone)*100),1) AS PVC_Upsold_Percentage
FROM eclublog INNER JOIN Sales ON eclublog.user_name=Sales.agent_name
If not, look closely at each field name and make sure the spelling is accurate.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
patricksims
Posts: 37
Joined: 7/7/2005
Status: offline
|
RE: Inner join two tables in DRW - 7/12/2005 12:35:50
That query contains an extra quotation mark after the word Percentage (not trying to point out errors- just noting that I removed it) and the DRW gave the following error:
You tried to execute a query that does not include the specified expression 'Agent' as part of an aggregate function.
| |
|
|
|
patricksims
Posts: 37
Joined: 7/7/2005
Status: offline
|
RE: Inner join two tables in DRW - 7/12/2005 14:28:01
I really appreciate all of your effort, Duane!
That query returns:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
Number: -2147217904 (0x80040E10)
Source: Microsoft OLE DB Provider for ODBC Drivers
Do you think it would help if you new the details of my tables or for that matter had sample tables? By everything I've seen and read it these queries should be working.
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Inner join two tables in DRW - 7/14/2005 17:41:21
Hi Patrick. Sorry for the delay. Yahoo put your message in my junkmail folder. Glad I decided to look there. I haven't played with it too much, but the first glaring issue I see is this; You query contains this both in the SELECT and the ORDER BY:
COUNT(Sales.home_phone)
Unfortunately, the table named Sales has no field named home_phone, although the eclublog table does. So, the following works great, but only you can tell if it is the results you hoped for:
SELECT Sales.agent_name AS Agent, COUNT(eclublog.home_phone) AS MCC,COUNT(Sales.birth_city) AS MCC_Upsells, round((COUNT(Sales.birth_city)/COUNT(eclublog.home_phone)*100),1) AS MCC_Upsell_Percentage, COUNT(eclublog.home_phone) AS PVC, COUNT(eclublog.upsold) AS PVC_Upsell, round((COUNT(eclublog.upsold)/COUNT(eclublog.home_phone)*100),1) AS PVC_Upsold_Percentage
FROM eclublog INNER JOIN Sales ON eclublog.user_name=Sales.agent_name GROUP BY Sales.agent_name ORDER BY COUNT(eclublog.home_phone) DESC
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
patricksims
Posts: 37
Joined: 7/7/2005
Status: offline
|
RE: Inner join two tables in DRW - 7/14/2005 17:48:25
I removed the phone numbers because they are real. Let me substitute fake numbers and send it again.
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Inner join two tables in DRW - 7/14/2005 19:41:48
The following seems to work. The main problem is a typo in your original SQL string. Look at the fourth line of the SQL in your original post and you will see where you used, eclub.home_phone instead of eclublog.home_phone. I would also use the CDate function on the field that is not formatted as a date.
SELECT Sales.agent_name AS Agent, COUNT(Sales.home_phone) AS MCC, COUNT(Sales.birth_city) AS MCC_Upsells, round((COUNT(Sales.birth_city)/COUNT(Sales.home_phone)*100),1) AS MCC_Upsell_Percentage, COUNT(eclublog.home_phone) AS PVC, COUNT(eclublog.upsold) AS PVC_Upsell, round((COUNT(eclublog.upsold)/COUNT(eclublog.home_phone)*100),1) AS PVC_Upsold_Percentage FROM eclublog INNER JOIN Sales ON eclublog.user_name=Sales.agent_name WHERE (CDate(Sales.start) BETWEEN #::begin::# AND #::end::#) AND ([eclublog.timestamp] BETWEEN #::begin::# AND #::end::#) GROUP BY Sales.agent_name ORDER BY COUNT(Sales.home_phone) DESC
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
patricksims
Posts: 37
Joined: 7/7/2005
Status: offline
|
RE: Inner join two tables in DRW - 7/15/2005 14:15:56
I tried that query and got the following error:
Syntax error in date in query expression '(CDate(Sales.start) BETWEEN #1# AND #2#) AND ([eclublog.timestamp] BETWEEN #3# AND #4#)'.
Would it help to know that the form field values for begin and end are ISO (yyyy-mm-dd) format as is the Sales.start values, but the timestamps values in the database are date/time? That is why I was trying to use the DateValue() function.
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Inner join two tables in DRW - 7/15/2005 15:36:27
OK, so what if you go back to the query in your first post and just fix the typo? Does that work?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
patricksims
Posts: 37
Joined: 7/7/2005
Status: offline
|
RE: Inner join two tables in DRW - 7/15/2005 16:41:09
Eureka! The first query sort of works when I corrected that table name from eclub.home_phone to eclublog.home_phone. Only problem now is the query is returning the count of records in the sales table instead of the eclublog table :(.
quote:
ORIGINAL: BeTheBall
OK, so what if you go back to the query in your first post and just fix the typo? Does that work?
quote:
SELECT Sales.agent_name AS Agent,COUNT(Sales.home_phone) AS "MCC",COUNT(Sales.birth_city) AS "MCC Upsells",round((COUNT(Sales.birth_city)/COUNT(Sales.home_phone)*100),1) AS "MCC Upsell %",COUNT(eclublog.home_phone) AS PVC,COUNT(eclublog.upsold) AS "PVC Upsell",round((COUNT(eclublog.upsold)/COUNT(eclub.home_phone)*100),1) AS "PVC Upsold %"
FROM eclublog INNER JOIN Sales ON eclublog.user_name=Sales.agent_name
WHERE (Sales.start BETWEEN '::begin::' AND '::end::' AND (DateValue([eclublog.timestamp]) BETWEEN DateValue('::begin::') AND DateValue('::end::')))
GROUP BY Sales.agent_name ORDER BY COUNT(Sales.home_phone) DESC
| |
|
|
|
patricksims
Posts: 37
Joined: 7/7/2005
Status: offline
|
RE: Inner join two tables in DRW - 7/15/2005 17:22:41
Spooky, I am not sure what you mean by this statement. The query pulls results now, but the totals are wrong for the date range. Maybe I have an incorrect understand of the logic. I am thinking that a query can count records that fall in a date range in one table and do the same in a different table and all be displayed together in one DRW result.
I will enclose the column name in brackets again now that it at least pulls some result. Can you point out which of the words I have used are reserved words?
After executing my query in Access, it appears that the results are multiplying. For example, if there are 8 records in the results from one table and 2 records in results of the second table, the results of the second table appear as 16.
Thanks a million!
quote:
ORIGINAL: Spooky
Typically when you see the numbers 1,2,3,4 etc as the VALUES in a query, it means that you have used a reserved word in your SQL string.
You can get around this by not using a reserved word, or enclosing the column name in brackets []
quote:
Typically when you see the numbers 1,2,3,4 etc as the VALUES in a query, it means that you have used a reserved word in your SQL string.
< Message edited by patricksims -- 7/15/2005 17:52:50 >
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 
