Help for an SQL newbie attempting to learn

[slang]

Hello database gods... Hope you can point me in the right direction.

I am using ms query within Excel 2003 on an external Excel file and am having problems writing the correct sql. The data file is c:\BDR\33activity with a named range "data" that tracks visits to customer sites by sales reps with the following data structure.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Location, Date,BDType, OPType, Cat, Rep <o></o>
<o> </o>
Data I would like is<o></o>
Location<o></o>
Last date with BDType activity="Business Development Visit"<o></o>
Last date with OPType activity<o></o>
Last date with BDType activity="Phone Call"<o></o>
<o> </o>
Output preferred for each Location <o></o>
Header<o></o>
LocationID-Last BD Visit - Last OP Visit - Last Phone Call<o></o>
Data<o></o>
2341, 09/09/2010, 11/07/2010, 09/09/2010<o></o>

Kind of new to the SQL thing but this is what I have so far from reading and surfing that gives me the first column of dates.<o></o>
<o> </o>
SELECT data.Location, Max(data.Date) AS 'Last Dev Call'<o></o>
FROM `C:\BDR\33activity`.data data<o></o>
WHERE (data.BDType='Business Development Visit')<o></o>
GROUP BY data.Location<o></o>
<o> </o>
I just cant get my head around how to get the second column in there without blowing it up. I know thats going to be much more complicated. so I would like to use this as a learning phase and if someone can show me how to do it this far I might be able to modify it to add more column or criteria to the col once I see the context..<o></o>
<o> </o>
Thanks for your patience with a newbie. Any help would be simply GREAT.<o></o>

 

[xenou]

Could you provide some sample data - say about 5-6 rows that would show the data you need to pull and how it would be found in the data file. I'd like to be sure I have the right idea about how the different dates get involved with this: last BDType of "Business Development Visit", last BDType, last OPTYpe. Are these all dates that correspond to different rows in the data file (and need to be brought together into a single result record via your query)?

 

[slang]

Here is a csv dump

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64>
<COLGROUP></COLGROUP>
<COLGROUP><COL style="WIDTH: 48pt" width=64></COLGROUP>
<TBODY>
<TR style="HEIGHT: 15pt" height=20>
<TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=64>Location,Date,Retailername,BDType,BDDuration,OPType,OPDuration,TODuration</TD>

</TR>
<TR style="HEIGHT: 15pt" height=20>
<TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>7817,40896.4183711806,franks var,Business Development Visit,30,,0,30</TD>

</TR>
<TR style="HEIGHT: 15pt" height=20>
<TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>9663,40896.4296135417,store 1,,0,Operational Visit,15,15</TD>

</TR>
<TR style="HEIGHT: 15pt" height=20>
<TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>93302,40896.4548417824,store 4,Business Development Visit,15,,0,15</TD>

</TR>
<TR style="HEIGHT: 15pt" height=20>
<TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>4160,40896.5036310185,a1 conv,Business Development Visit,30,,0,30</TD>

</TR>
<TR style="HEIGHT: 15pt" height=20>
<TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>7492,40896.5365069444,MCTAVY`S GENERAL STORE,Business Development Visit,15,Operational Visit,5,20</TD>

</TR>
<TR style="HEIGHT: 15pt" height=20>
<TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>90271,40896.540177662,main street var,Business Development Visit,5,,0,5</TD>

</TR>
<TR style="HEIGHT: 15pt" height=20>
<TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>2305,40896.5720326389,etc,,0,Operational Visit,15,15</TD>

</TR>
<TR style="HEIGHT: 15pt" height=20>
<TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>6612,40896.5777986111,etc2,,0,Operational Visit,5,5</TD>

</TR>
<TR style="HEIGHT: 15pt" height=20>
<TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>7817,40896.5880405092,franks var,,0,Operational Visit,10,10</TD>

</TR>
<TR style="HEIGHT: 15pt" height=20>
<TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>483,40896.6225725694,etc,Phone Call,10,,,15</TD>

</TR>
<TR style="HEIGHT: 15pt" height=20>
<TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>93302,40896.6601457176,etc,,0,Operational Visit,20,20</TD>

</TR>
<TR style="HEIGHT: 15pt" height=20>
<TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>93703,40896.674130787,etc,,0,Operational Visit,15,15</TD>

</TR>

</TBODY>

</TABLE>

The date in Excel is a number so I guess thats why it looks the way it does.



There is only one date per record that can have both Business Development and Operational information.


There are about 5,000 records in the data file.





If I had to do it all over again it would be in Access


Thanks for the guidence.

 

[xenou]

This is what I come up with - it seems a bit complicated for a single query since there's a lot of nesting that way so I'd create 4 queries. If a picture is worth thousand words here's the basic structure:

<img alt="queries" width="577" height="350" src="http://northernocean.net/etc/mrexcel/20111231_queries.png" />

The results:

<img alt="queries" width="596" height="220" src="http://northernocean.net/etc/mrexcel/20111231_result.png" />

The first query is to get a distinct list of all locations, each of the other three is joined to it on Location to get the details for each thing you are interested in.

Query0:
SELECT DISTINCT Table1.Location
FROM Table1;

Query1:
SELECT Table1.Location, Max(Table1.[Date]) AS [Last X]
FROM Table1
WHERE (((Table1.[BDType])='Business Development Visit'))
GROUP BY Table1.Location;

Query2:
SELECT Table1.Location, Max(Table1.Date) AS [Last Y]
FROM Table1
WHERE (((Table1.OPType) Is Not Null))
GROUP BY Table1.Location;

Query3:
SELECT Table1.Location, Max(Table1.Date) AS [Last Z]
FROM Table1
WHERE (((Table1.BDType)="Phone Call"))
GROUP BY Table1.Location;

Query4 (Final rollup):
SELECT DISTINCT Query0.Location, Query1.[Last X], Query2.[Last Y], Query3.[Last Z]
FROM ((Query0 LEFT JOIN Query1 ON Query0.Location = Query1.Location) LEFT JOIN Query2 ON Query0.Location = Query2.Location)
LEFT JOIN Query3 ON Query0.Location = Query3.Location;

Problem is I have no idea how to translate this into MSQuery using a text data source. Maybe you'd have to create the queries as data sources. Not sure ... anyone else want to give this a try? I'm not going to be able to invest a lot of time in it.

Probably I'd pull all the raw data in Excel and just use Excel formulas, assuming that there's not hundreds of thousands of records.

 

[xenou]

Okay, poured a cup of coffee and realized why my single query wasn't working (I was getting confused on handling getting distinct locations). Here's a single query that works, though perhaps the above post still helps to clarify what it's doing. I'll leave getting this into correct syntax for MSQuery up to you - it's dreadful dealing with all those backticks but otherwise it should work the same I think.

SELECT DISTINCT t1.Location, t2.[Last X], t3.[Last Y], t4.[Last Z]

FROM (((Table1 t1

Left Join

(SELECT b.Location, Max(b.[Date]) AS [Last X]
FROM Table1 b
WHERE (((b.[BDType])='Business Development Visit'))
GROUP BY b.Location) t2

ON

t1.Location = t2.Location)

Left Join

(SELECT c.Location, Max(c.Date) AS [Last Y]
FROM Table1 c
WHERE (((c.OPType) Is Not Null))
GROUP BY c.Location) t3

ON

t1.Location = t3.Location)

Left Join

(SELECT d.Location, Max(d.Date) AS [Last Z]
FROM Table1 d
WHERE (((d.BDType) = "Phone Call"))
GROUP BY d.Location) t4

ON t1.Location = t4.Location)


;

Result is the same as before:
<img alt="query results" width="594" height="221" src="http://northernocean.net/etc/mrexcel/20111231_result2.png" />

 

[guerillaunit]

My friend, use the below SQL:


Query1:
SELECT location, max(date) as ‘BD Date’ FROM 33activity
WHERE BDType = ‘Business Development Visit’
GROUP BY location

Query2:
SELECT location, max(date) as ‘OP Date’ FROM 33activity
WHERE OPType IS NOT NULL
GROUP BY location

Query3:
SELECT location, max(date) as ‘Phone Call Date’ FROM 33activity
WHERE BDType = ‘Phone Call’
GROUP BY location

Query4:
SELECT 33activity.location, [bd date], [op date], [phone call date] FROM
(((33activity LEFT JOIN query1 ON 33activity.location = query1.location) LEFT JOIN query2 ON
33activity.location = query2.location) LEFT JOIN query3 ON 33activity.location = query3.location)

 

[xenou]

Query4:
SELECT 33activity.location, [bd date], [op date], [phone call date] FROM
(((33activity LEFT JOIN query1 ON 33activity.location = query1.location) LEFT JOIN query2 ON
33activity.location = query2.location) LEFT JOIN query3 ON 33activity.location = query3.location)

Would this return duplicate records -- one row for every time a location occurs in 33activity table?
Perhaps instead:

Query4:
SELECT DISTINCT 33activity.location, [bd date], [op date], [phone call date] FROM
(((33activity LEFT JOIN query1 ON 33activity.location = query1.location) LEFT JOIN query2 ON
33activity.location = query2.location) LEFT JOIN query3 ON 33activity.location = query3.location)

But I'm not sure how you define stored queries in MSQuery on a text data source.

 

[guerillaunit]

ay xenou, there needs to be a distinct value after select

 

[slang]

Thanks database gods!!!
Got it working, Thanks so much