Spoiled

I might have been spoiled from using sql server for a few years, but is
something like this possible with access?

declare @id int
select @id = id from <table> where <condition>

Any help or alternative methods would be great, thanks.

EvilGuyWhoEatsBrains wrote:[color=blue]
> I might have been spoiled from using sql server for a few years, but is
> something like this possible with access?
>
> declare @id int
> select @id = id from <table> where <condition>
>
> Any help or alternative methods would be great, thanks.[/color]

It depends on what you are trying to accomplish.

the @id int etc in sql i still use within an access project to allow a
form in access project for example to search for a specific field
within a view or procedure within in sql.

If you are working with MS Access.mdb you would use the combo and
search feature within in the form for a search.

I could list many different ways to replace the @ id int etc in access
depending on what you are trying to accomplish. If you would like me
to get specific however could you narrow down you need just a little.
lol

Thanks for the reply. :)

What I'm trying to accomplish is taking a value from a table, and
assigning that value to a variable. Then using that variable in a
different select statement. All within the same query.

I guess what I was asking for was just a baisic example on how to do
that in access, I can figure out the rest from there.

With sql server I would accomplish the task like this... (just making
this up as I go, don't worry if the structure doesn't make sense lol)

-------------------------------
declare @id int
declare @price money

select @id = id, @price = price from products where productName like
'shoes'

select firstname, lastname, numberBought * @price as total from
shoppingcart where id = @id
-------------------------------

"EvilGuyWhoEatsBrains" <nokittymypotpie@gmail.com> wrote in message
news:1150466103.836175.206620@g10g2000cwb.googlegr oups.com...[color=blue]
> Thanks for the reply. :)
>
> What I'm trying to accomplish is taking a value from a table, and
> assigning that value to a variable. Then using that variable in a
> different select statement. All within the same query.
>
> I guess what I was asking for was just a baisic example on how to do
> that in access, I can figure out the rest from there.
>
> With sql server I would accomplish the task like this... (just making
> this up as I go, don't worry if the structure doesn't make sense lol)
>
> -------------------------------
> declare @id int
> declare @price money
>
> select @id = id, @price = price from products where productName like
> 'shoes'
>
> select firstname, lastname, numberBought * @price as total from
> shoppingcart where id = @id
> -------------------------------[/color]

If your Access DB was a client to SQL Server, as it could be, you could use
the stored procedure you describe. If you are using the default Jet database
engine that installs with Access, it does not support stored procedures. In
Access itself, however, you could have code that used variables to construct
an SQL statement that you execute, though it is unusual to just execute
select statements in VBA code -- usually they are used to open a Recordset,
or to serve as the Record Source for a bound Form or Report.

Would you be so kind as to clarify what you are trying to accomplish,
instead of just giving the stored procedure code? (I think I see what you
are trying to do, except I don't know what you want to do with the
information returned from the second T-SQL select statement.

If so, I'll bet there will be someone who can give you an Access approach to
accomplish that purpose.

Larry Linson
Microsoft Access MVP

EvilGuyWhoEatsBrains wrote:[color=blue]
> Thanks for the reply. :)
>
> What I'm trying to accomplish is taking a value from a table, and
> assigning that value to a variable. Then using that variable in a
> different select statement. All within the same query.
>
> I guess what I was asking for was just a baisic example on how to do
> that in access, I can figure out the rest from there.
>
> With sql server I would accomplish the task like this... (just making
> this up as I go, don't worry if the structure doesn't make sense lol)
>
> -------------------------------
> declare @id int
> declare @price money
>
> select @id = id, @price = price from products where productName like
> 'shoes'
>
> select firstname, lastname, numberBought * @price as total from
> shoppingcart where id = @id
> -------------------------------[/color]

Hi,

Just did a quick sql for you in Access to accomplish what you stated
above:

SELECT shoppingcart.firstname, shoppingcart.lastname,
shoppingcart.numberBought, Products.Price, [numberBought]*[Price] AS
Total
FROM Products INNER JOIN shoppingcart ON Products.ID =
shoppingcart.IDFK
WHERE (((Products.ProductName)="Shoes"));

Hope this helps.

Cilla