Database

5-Jul-06 4:22

Hi,

I have a table like this:


Param           Cat           Payee          Amt1        Amt2          
AS               x1             y1            23          34   
AS               x1             y2            43          53   
BJ               x1             y1            53          33

I want to select row 1 and row 3.

For just row1, I write,
“SELECT * FROM Tab1 WHERE Param=’AS’ AND Cat=’x1’ AND Payee=’y1’”

But how do write the sql if I want both the 1st row and the 3rd row. They both differ in only the Param name.

Please can you tell me.

Thanks,
Tara

Fortitudine Vincimus!

Colin Angus Mackay5-Jul-06 5:10

5-Jul-06 5:10

TheinstruTara wrote:
But how do write the sql if I want both the 1st row and the 3rd row. They both differ in only the Param name.

Your table should have a primary key. What is the primary key?

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Frank Kerrigan5-Jul-06 5:56

Frank Kerrigan

5-Jul-06 5:56

try

SELECT * from Table1 where Payee = 'y1'

or

SELECT Top 2 * from Table1 ORDER BY Amt2

if depends on what you want to filter by.

Look where you want to go not where you don't want to crash.

Bikers Bible

Tara145-Jul-06 6:56

5-Jul-06 6:56

I have to calculate the difference between the amounts in the 1st row and that in the 3rd row.

I understand what a primary key is, but my database does not require it, or so I think!!

Nevermind. I did it differently. Got the 1st row, collected the amounts in an array, then got the 2nd row and did the calculation.

If there is a better approach to doing the calculation, I would be glad to know.

Thanks,
Tara

Fortitudine Vincimus!

Re: sql [modified]

Colin Angus Mackay5-Jul-06 9:29

5-Jul-06 9:29

TheinstruTara wrote:
I have to calculate the difference between the amounts in the 1st row and that in the 3rd row.

First a wee bit about databases. They deal with sets of information, using set based queries. In a set there is no concept of order, although many database systems can impose a concept of order by using the ORDER BY clause in SQL. So, as far as the datbase system is concerned there is no 1st and 3rd row.

TheinstruTara wrote:
I understand what a primary key is, but my database does not require it, or so I think!!

If you want to refer to a specific row you need a primary key. If you understand what a primary key is you should know that is will always uniquely refer to a row.

You mention a calculation, but you never mentioned this before. If you are looking for a better way to do a calculation then you need to let us know what the calculation is in order for us to help you with that.

Tara145-Jul-06 10:36

5-Jul-06 10:36

Thankyou so much for your help.

I am coding a program in vc++ and I use MS Access as the database. We need to know a little bit to sql to interact with the database. I just mentioned 1st row and 3 row as examples.

In my program I do not know which row it actually is. Therefore, even if there is a primary key assosiated with a row, its of now use to my program.

I was trying something like this:

SELECT * FROM Tab1 WHERE param='AS' AND param='BJ' AND cat='x1' AND payee='y1'

But the sql was not accepted.

Fortitudine Vincimus!

Colin Angus Mackay5-Jul-06 12:17

5-Jul-06 12:17

TheinstruTara wrote:
WHERE param='AS' AND param='BJ'

That says that the column called param must be equal to 'AS' and 'BJ' at the same time. This is, naturally, impossible.

I'm guessing what you actually wanted to do was:

SELECT * FROM Tab1 WHERE param IN('AS', 'BJ') AND cat='x1' AND payee='y1'

If MS access cannot handle the IN() operator then it is logically equivalent to

(param = 'AS' OR param = 'BJ')

(note the brackets are also important)

TheinstruTara wrote:
In my program I do not know which row it actually is

You must know something about the rows you want, you are putting stuff in the WHERE clause - Unfortunately it means nothing to me, so it is difficult for me to make meaningful suggestions.

And, you still haven't mentioned anything about the calculation you are doing.

Tara145-Jul-06 20:34

5-Jul-06 20:34

Dose'nt matter. It's OK. Thank you.

Fortitudine Vincimus!

DropDownList

Gowri.M5-Jul-06 0:52

Gowri.M

5-Jul-06 0:52

I am Having DropDownList,GridView1 and GridView2 in my page.

If i select one value in the DropDownList that related data has to display in the GridView.

Please help me out. Very Urgent.

Thanks
Kalpana

Frank Kerrigan5-Jul-06 1:50

Frank Kerrigan

5-Jul-06 1:50

This should not be posted in the SQL forum !

basically you need a SelectedItemChange event on your DropDownList that runs a SQL query and then binds the data to to your gridview.

Look where you want to go not where you don't want to crash.

Bikers Bible

Jerry Hammond6-Jul-06 18:11

Jerry Hammond

6-Jul-06 18:11

Frank Kerrigan wrote:
This should not be posted in the SQL forum !

This could arguably be deemed an ADO.NET question as well as a ASP.NET question. Either forum is correct, imo.

Jerry

“Profanity is the attempt of a lazy and feeble mind to express itself forcefully”

Colin Angus Mackay6-Jul-06 20:26

6-Jul-06 20:26

I have to say that from the initial description I cannot see where the ADO.NET component comes in to it - there is no mention of where the data comes from at all. It could be generated internally, come from a file or what ever. Of course, it could possibly come from a database, but the OP doesn't say.

Jerry Hammond7-Jul-06 4:00

Jerry Hammond

7-Jul-06 4:00

I'm being kind and looking at it as implied.

“Profanity is the attempt of a lazy and feeble mind to express itself forcefully”

PlayByTheRules5-Jul-06 2:51

PlayByTheRules

5-Jul-06 2:51

1. What does this have to do with SQL (the subject of this forum)

2. The people here volunteer their abilities in their spare time, for free, for the benefit of others. Saying your problem is "Very Urgent" is not going to get you a faster response. I would suggest that it does, in fact, turn off some people because they would rather help someone who appears to appreciate the scenario in which the questions are being asked. i.e. a community forum.

If you want urgent help with your problem then I would suggest you call Microsoft Support who will help for a small fee.

how to use now() function in the access?

Jerry Hammond6-Jul-06 18:12

Jerry Hammond

6-Jul-06 18:12

This could arguably be deemed an ADO.NET question as well as an ASP.NET question. Either forum is correct, imo.

Jerry

“Profanity is the attempt of a lazy and feeble mind to express itself forcefully”

KSCsoft5-Jul-06 0:01

KSCsoft

5-Jul-06 0:01

When I update data,I hope the datetime change together.so,I use the Now() function,but throw the Excetption.look at following:

@ID
@name,
@sex,
@age,

"Update mytable set name=@name,sex=@sex,age=@age,DateTime=now() where ID=@ID"

so,how to modify?who can help me?
thank you!

KSCsoft

sathish s5-Jul-06 0:26

sathish s

5-Jul-06 0:26

you can always can have the value of "date" field filled with the current date/time by setting the field's Default value to =Now() in Access table design mode.
What is the exception you get?

KSCsoft5-Jul-06 17:42

KSCsoft

5-Jul-06 17:42

you are right!but it is insert,not the update.

KSCsoft

Eric Dahlvang5-Jul-06 3:32

Eric Dahlvang

5-Jul-06 3:32

KSCsoft wrote:
@ID@name,@sex,@age

MSAccess doesn't use the @ symbol for variable naming. Your problem isn't the 'now()' function.

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

KSCsoft5-Jul-06 17:50

KSCsoft

5-Jul-06 17:50

I use @ symbol for variable naming in the asp.net.
I just wanna know When I update record,the datetime field's value is the current time!

KSCsoft