Access to Sql Query

[Arejan]

This access query returns the result in a single row for each customer

SELECT NewTable.cid, MembersTable.Customer, Place.Place,
MembersTable.Tel, Max(IIf(NewTable.expid=1,NewTable.idno,0)) AS 1, Max
(IIf([NewTable].[expid]=1,Format([NewTable].[NewDate],'mm/dd/
yyyy'),Null)) AS DateID1, Sum(IIf([NewTable].[expid]=1,Format
([NewTable].[amount],'Fixed'),'0.00')) AS [Subs Charges], Max(IIf
(NewTable.expid=100,NewTable.idno,0)) AS 4, Max(IIf([NewTable].[expid]
=100,Format([NewTable].[NewDate],'mm/dd/yyyy'),Null)) AS DateID100, Sum
(IIf([NewTable].[expid]=100,Format([NewTable].
[amount],'Fixed'),'0.00')) AS [Other Charges], [Subs Charges]-[Other
Charges] AS Total
FROM (NewTable LEFT JOIN MembersTable ON NewTable.cid =
MembersTable.Regno) LEFT JOIN Place ON MembersTable.Placeid = Place.ID
GROUP BY NewTable.cid, MembersTable.Customer, Place.Place,
MembersTable.Tel, MembersTable.id, Year(NewTable.NewDate), Month
(NewTable.NewDate)
HAVING (((Year([NewTable].[NewDate]))=2000) AND ((Month([NewTable].
[NewDate]))=1));

But in Sql 2000 doesn’t return a single row
each time its displayed in seperate rows
here is the query

SELECT NewTable.cid, MembersTable.Customer, Place.Place,
MembersTable.Tel,
CASE WHEN NewTable.expid=1 then max(NewTable.idno) else max
(NewTable.idno) end as '1',
CASE WHEN NewTable.expid=1 then CONVERT(varchar,NewTable.NewDate,
103) end as [DateID1],
CASE WHEN NewTable.expid=1 then sum(NewTable.amount) end AS [Subs
Charges],

CASE WHEN NewTable.expid=100 then max(NewTable.idno) else max
(NewTable.idno) end as '100',
CASE WHEN NewTable.expid=100 then CONVERT(varchar,NewTable.NewDate,
103) end as [DateID100],
CASE WHEN NewTable.expid=100 then sum(NewTable.amount) end AS [Cab
Charges]

FROM (NewTable LEFT JOIN MembersTable ON NewTable.cid =
MembersTable.Regno) LEFT JOIN Place ON
MembersTable.Placeid = Place.ID
GROUP BY NewTable.cid, MembersTable.Customer, Place.Place,
MembersTable.Tel,

NewTable.idno,
NewTable.expid,NewTable.NewDate,
MembersTable.id,
Year(NewTable.NewDate), Month(NewTable.NewDate)
HAVING (((Year(NewTable.NewDate))=2000) AND ((Month(NewTable.NewDate))
=1))

And how to [Subs Charges]-[Other Charges] AS Total in Sql Server 2000?

thank you

[Erland Sommarskog]

Arejan (areej...@yahoo.com) writes:
> This access query returns the result in a single row for each customer

>...

Since I don't know your tables, I can't tell whether there is any
particular reason to expect only one row per customer. But from what
I see of the query, I don't see any reasons for such expectations.
If there are multiple rows in NewTable for the same MembersTable.Regno,
obviously there will be more than row per MembersTable.Customer.

What is the primary key of NewTable? What is the primary key of
Member Table?

> And how to [Subs Charges]-[Other Charges] AS Total in Sql Server 2000?

I don't see [Other Charges] defined in the SQL query. I notice that
it appears in the Access query, but I'm afraid that the since the
Access query is totally void of formatting it is unreadable. But
essentially you have to repeat the expressions.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Sylvain Lafontaine]

There is a big, big, big difference betweeen putting the IIF() or the Case
statement inside the call to the Max() function or outside it:

Max(IIf(NewTable.expid=1,NewTable.idno,0)) as 1

versus:

CASE WHEN NewTable.expid=1 then max(NewTable.idno) else max
(NewTable.idno) end as '1'

The ELSE part is also different between these two. To correct for these two
errors, you should write something like:

Max (CASE WHEN NewTable.expid=1 then NewTable.idno Else 0 End) as '1'

The same can be told about your Sum(). Also, you shouldn't use an HAVING
statement but a WHERE statement. You should use the HAVING only when
working against the results of the Group By; which is not the case here.

For your questio about [Subs Charges]-[Other Charges] AS Total, you cannot
reuse the aliase of an expression in another expression on SQL-Server (but
you can with Access). You will have to either repeat the full original
expressions or push this query inside a sub-query and make the computation
on the outside query.

Finally, your mix of Left Join and of Group By looks very suspicious to me.
I'm not sure if you really need to use a Left Join instead of a simpler
Inner Join and I won't forget to mention the apparent lack of uniformity in
your naming convention: you only have three tables but it's already hard to
follow the joins.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

"Arejan" <areej...@yahoo.com> wrote in message
news:f7cf4ef2-67ce-49ef...@q2g2000yqd.googlegroups.com...

[Arejan]

MembersTable

id Regno Customer Address Placeid Tel

1 1120 Jake Address 1 14
587879977

2 1122 Mitch Address 34 5 899798090

New Table

idno cid expid amount NewDate

3 1120 1 $140.00 01/01/2000
11 1120 1 $140.00 15/02/2000
17 1120 1 $140.00 10/03/2000
18 1120 100 $200.00 15/01/2000
19 1120 5 $100.00 15/02/2000
20 1122 1 $140.00 15/02/2000
21 1123 5 $100.00 15/02/2000
22 1122 5 $200.00 15/02/2000
23 1122 100 $800.00 19/01/2000

the query returns

cid Name Address Tel Place 1 Dateid1 Subs Charges 100 Dateid100
Other Charges Total

1120 Jake Address 1 587879977 London 3 01/01/2000 140 18
15/01/2000 200 -60

1122 Mitch Address 34 899798090 Bristol 23 19/01/2000
800 800

and [Cab Charges] should be read [Other Charges] in sql .plz.

[Sylvain Lafontaine]

If you want people to do something with that, your best option would be to
post the design of the tables and the insert queries required to populate
these tables so that can people can Copy&Paste this code (tables design +
data) into their EM or SSMS and run the script. Taking the time of
describing what result you are expecting and in what way the result you are
actually getting - along with the code of your latest attempt for the
query - is not what you want to get would also be a good idea.

Also, if there is something that you didn't understand or was not clear in
my last post, you should mention it.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

"Arejan" <areej...@yahoo.com> wrote in message

news:a042a551-49a7-4399...@p8g2000yqb.googlegroups.com...

>
> MembersTable
>
> id Regno Customer Address Placeid
> Tel
>
> 1 1120 Jake Address 1
> 14 587879977
>
> 2 1122 Mitch Address 34
> 5 899798090
>
>
> New Table
> idno cid expid amount NewDate
>
> 3 1120 1 $140.00 01/01/2000
> 11 1120 1 $140.00 15/02/2000
> 17 1120 1 $140.00 10/03/2000
> 18 1120 100 $200.00 15/01/2000
> 19 1120 5 $100.00 15/02/2000
> 20 1122 1 $140.00 15/02/2000
> 21 1123 5 $100.00 15/02/2000
> 22 1122 5 $200.00 15/02/2000
> 23 1122 100 $800.00 19/01/2000
>
> the query returns
>
> cid Name Address Tel Place 1
> Dateid1 Subs Charges 100 Dateid100 Other Charges
> Total
>

> 1120 Jake Address 1 7879977 London 3 01/01/2000

[Arejan]

just trying to get a single row of expns details for each customer on
a perticular date from New Table thats all

[Sylvain Lafontaine]

There is already a single row in the expns details that you are showing us.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

"Arejan" <areej...@yahoo.com> wrote in message

news:f6213005-c580-4d96...@h9g2000yqa.googlegroups.com...

[Arejan]

thats what the access query returns

the sql returns multiple rows

[Arejan]

Access returns

cid Name Address Tel Place 1
Dateid1 Subs Charges 100 Dateid100
Other Charges Total

1120 Jake Address 1 587879977 London 3

01/01/2000 140 18 15/01/2000
200 -60

-------------------------------------------------------------------------------------------------------------------------------------------------
while sql returns

cid Name Address Tel Place 1
Dateid1 Subs Charges 100 Dateid100
Other Charges Total

1120 Jake Address 1 587879977 London
3 01/01/2000 140
-60

1120 Jake Address 1 587879977 London

3 18 15/01/2000
200 -60
--------------------------------------------------------------------------------------------------------

the problem is of these two dates as it cant be listed in a single row

NewTable.NewDate

3 1120 1 $140.00 01/01/2000

18 1120 100 $200.00 15/01/2000

[Sylvain Lafontaine]

It's because you have added the field NewTable.NewDate to the Group By in
the case of the SQL query. This will force a Group By for each day, making
useless the Group By on the year and the month.

You don't have this fiedl in the Group By in the Access query because you
are using the Max function to extract its maximum value.

For the SQL query, you have put the Max() function inside the Case statement
instead of putting it outside; hence your problem.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

"Arejan" <areej...@yahoo.com> wrote in message

news:fa5526dc-0f39-401b...@h9g2000yqa.googlegroups.com...

[Arejan]

For the SQL query, you have put the Max() function inside the Case
statement
instead of putting it outside; hence your problem.

tried it but all the same

[Sylvain Lafontaine]

No, you must remove the NewTable.NewDate from the Group By but you can keep
the Year([NewTable].[NewDate])and the Month([NewTable].[NewDate]) in the
Group By.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

"Arejan" <areej...@yahoo.com> wrote in message

news:0b8d4416-80e3-4567...@m3g2000yqf.googlegroups.com...
> may be i should use pivot ?
>
>

[Sylvain Lafontaine]

Yes, it's invalid to remove it if you keep your current Select's list. If
you take a look, you have hidden its value inside a call to Max() in the
Access query:

Max(IIf([NewTable].[expid]
=100,Format([NewTable].[NewDate],'mm/dd/yyyy'),Null))

but you didn't do the same thing in your SQL query. Maybe there are other
errors as well, I didn't look.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

"Arejan" <areej...@yahoo.com> wrote in message

news:1fb40924-e946-47bf...@35g2000yqa.googlegroups.com...
> removing the NewTable.NewDate from the Group
> Column 'NewTable.NewDate' is invalid in the select list because it is
> not contained in either an aggregate function or the GROUP BY clause.
>
> what would be the ideal query for row to column in sql 2000
>