SQL query to Insert Data Into a table based on listbox in a form

[AccessHelp123]

Hi all,

I have a query that looks like this. I have been struggling to write something that will modify the query as per written below. Thanks a lot.


INSERT INTO Table1 ( [Date], DCR )
SELECT tableData.Date, tableData.LostCalls AS DCR
FROM tableData
WHERE (((tableData.Date) Between [Forms]![frmGraph]![cmbStartDate] And [Forms]![frmGraph]![cmbEndDate]) AND ((tableData.SiteName)=[Forms]![frmGraph]![ListSiteName]))
GROUP BY tableData.Date, tableData.LostCalls;

This gives me two columns in Table1 with Date and DCR from tableData where SiteName is from a listbox ListSiteName

Table1

Date | DCR

04/12/2005 | 2.3345
04/12/2005 | 2.4356
04/12/2005 | 2.3454
04/13/2005 | 3.344
04/13/2005 | 2.334
04/13/2005 | 2.98797



For each Date in tableData the SiteName is listed three times with an entry in ajdacent column (tableData.Sector) as 0,1,2.


tableData

Date | DCR | SiteName | Sector

04/12/2005 | 2.3345 | Test123 | 0
04/12/2005 | 2.4356 | Test123 | 1
04/12/2005 | 2.3454 | Test123 | 2
04/13/2005 | 3.344 | Test123 | 0
04/13/2005 | 2.334 | Test123 | 1
04/13/2005 | 2.98797 | Test123 | 2



I am struggling to write a query that gives me
Date, Dcr for SiteName(Sector0), Dcr for SiteName(Sector1), Dcr for SiteName(Sector2)

So query I am trying to write should give me.


Table1

Date | DCR(Sector 0) | DCR(Sector 1) | DCR(Sector 1)

04/12/2005 | 2.3345 | 2.4356 | 2.3454
04/13/2005 | 3.344 | 2.334 | 2.98797



Please let me know how to go about this. Thanks.

 

[PHV]

A starting point:
SELECT [Date]
,Sum(IIf(Sector=0,LostCalls,0)) AS [DCR(Sector 0)]
,Sum(IIf(Sector=1,LostCalls,0)) AS [DCR(Sector 1)]
,Sum(IIf(Sector=2,LostCalls,0)) AS [DCR(Sector 2)]
FROM tableData
WHERE [Date] Between [Forms]![frmGraph]![cmbStartDate] And [Forms]![frmGraph]![cmbEndDate] AND SiteName=[Forms]![frmGraph]![ListSiteName]
GROUP BY [Date]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

I don't think the SUM is necessary

SELECT [Date]
,IIf(Sector=0,LostCalls,0) AS [DCR(Sector 0)]
,IIf(Sector=1,LostCalls,0) AS [DCR(Sector 1)]
,IIf(Sector=2,LostCalls,0) AS [DCR(Sector 2)]
FROM tableData
WHERE [Date] Between [Forms]![frmGraph]![cmbStartDate] And [Forms]![frmGraph]![cmbEndDate] AND SiteName=[Forms]![frmGraph]![ListSiteName]
GROUP BY [Date]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

Leslie, you HAVE to use an aggregate function for each field in the SELECT list not in the GROUP BY clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[AccessHelp123]

Thanks all,

If I dont want to use SUM then I add Sector and Lost calls in the GROUP BY Clause. Is that correct.

 

[PHV]

I add Sector and Lost calls in the GROUP BY Clause
And you get the Date listed three times ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

are you trying to SUM the DCR for each sector or just list it for each test site?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

If I dont want to use SUM
Have you tried my suggestion ?
This should be a fake sum ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[AccessHelp123]

I am trying to list DCR for each site.

I tried this query it did not display anything.

SELECT [tableData].[Date], IIf([Sector]=0,[LostCalls],0) AS [DCR(Sector 0)], IIf([Sector]=1,[LostCalls],0) AS [DCR(Sector 1)], IIf([Sector]=2,[LostCalls],0) AS [DCR(Sector 2)]
FROM tableData
WHERE ((([tableData].[Date]) Between [Forms]![frmGraph]![cmbStartDate] And [Forms]![frmGraph]![cmbEndDate]) And (([tableData].[SiteName])=[Forms]![frmGraph]![ListSiteName]))
GROUP BY [tableData].[Date], IIf([Sector]=0,[LostCalls],0), IIf([Sector]=1,[LostCalls],0), IIf([Sector]=2,[LostCalls],0);

 

[PHV]

I am trying to list DCR for each site
PARAMETERS [Forms]![frmGraph]![cmbStartDate] DateTime, [Forms]![frmGraph]![cmbEndDate] DateTime;
SELECT SiteName, [Date]
,Sum(IIf(Sector=0,LostCalls,0)) AS [DCR(Sector 0)]
,Sum(IIf(Sector=1,LostCalls,0)) AS [DCR(Sector 1)]
,Sum(IIf(Sector=2,LostCalls,0)) AS [DCR(Sector 2)]
FROM tableData
WHERE [Date] Between [Forms]![frmGraph]![cmbStartDate] And [Forms]![frmGraph]![cmbEndDate]
GROUP BY SiteName, [Date];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[AccessHelp123]

Thanks PH,

It works perfect now. Appreciate everyones help.