recordset = adding fields

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

Hi

I have a recordset that is based on a sum query - because descriptions have
changed I can't include the description in the sum query as it will give me
the following (I assume I have to have GroupBy for the descripton field)

stockcode description qty
abc desc1 100
abc desc2 100

The result I am after is
abc desc2 200

Is it possible to take the recordset and add a field to each record for the
description based on another query eg

SELECT rs.StockCode, InvMaster.Description
FROM InvMaster INNER JOIN rs ON InvMaster.StockCode = rs.StockCode;


If yes how do I do this?
 
Um... you nearly answered your own question. Add your sum on Qty to get
what you want:

SELECT rs.StockCode, InvMaster.Description, Sum(rs.Qty) As SumOfQty
FROM InvMaster INNER JOIN rs ON InvMaster.StockCode = rs.StockCode
GROUP BY StockCode, Description

Why is Description in rs? Sounds like you have a poorly designed database -
the Description should be in only one table.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
I have no control over the database design . . . .

Do I create another recordset if yes, how do I create a new recordset based
on the original one?

Thanks for your time

Al
 
I create an adodb.recordset that is created from a stored procedure in SQL
Server

code . . . .
rs = cmd.execute

This recordset does not include the description field.

I want to either add the description field to this recordset or (and I think
this is what I have to do) create another recordset based on the results of
the first recordset - (I know this isn't going to work) but if a 'normal'
query were possible I think it would look something like

SELECT rs.StockCode, InvMaster.Description
FROM InvMaster INNER JOIN rs ON InvMaster.StockCode = rs.StockCode

Thanks for your help so far

Al
 
Is the Stored Procedure a simple SELECT? If so, you should be able to
create the recordset you want by constructing an SQL statement and opening
or executing it as adCmdText. In your SQL, include the table that has the
description you want. Why are you using the Stored Procedure? Have you
been granted permission to the SP but not the underlying tables? If you
have permission to the tables, you can fetch anything you want by
constructing your own SQL.

What is the structure of the tables that contain the data you want (in
addition to InvMaster)?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
I am using a stored procedure because I thought they were more efficient . .
.. . . ?
The stored procedure is a Select Query (with a GroupBY) -

How do I create another recordset based on the first one?

Thanks for you help

Al
 
Well, yes - a stored procedure tends to be more efficient. If you have the
ability to create new stored procedures, why not create one that solves your
problem?

Failing that, did you try opening a recordset using the SQL suggested
earlier? (Substitute the name of the SP for "rs" everywhere in the SQL.)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
I can't create a group by query with Description in because the description
for the same stockcode can be different - I have a separate table (not in
SQL) for this purpose.

It needs (I think) to be a recordset based on the first one but I don't know
how to do this

Thanks again
Al
 
Table for Receipts (in SQL Server - no control over layout)
GRN
Stockcode
StockDescription - these can be different for the same stockcode
QtyReceived
DateReceived
GRNValue


Table for Description (in Access)
Stockcode
Description

I want a group query by stockcode but on the report I also want to print the
description as shown in the Access table.

At the moment I have a recordset based on the SQL table that gives me
everything I want except the description
 
Ah. And Receipts is a linked table to SQL Server? I wouldn't normally
recommend building a mixed-source query, but in this case it might work
because you'll be asking Access to lookup a unique Stockcode in the local
table. Assuming the linked table name is Receipts, try building a query
like this:

SELECT Receipts.StockCode, InvMaster.Description, Sum(Receipts.Qty) As
SumOfQty
FROM InvMaster INNER JOIN Receipts ON InvMaster.StockCode =
Receipts.StockCode
GROUP BY StockCode, Description

Try running the query from the database window to verify that it runs OK and
is not dog-slow. If that works, then open your local query in your code.

The only other solution is to open a second recordset on your local table in
code and do a find for each record that you process in your original ADO
recordset.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
No - I am trying not to link the SQL tables and do everything through code
hence the recordset for the SQL query

I don't know what you mean with opening a 2nd recordset?

Thanks for all your help
Al
 
Declare a second recordset variable and open the local table.

Dim rs As ADODB.Recordset, rs2 As New ADODB.Recordset
....
' Your original:
Set rs = cmd.Execute
' Also open the local table
rs2.Open "Select StockCode, Description FROM Description, _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

As you process each record in rs, find the description in rs2:

rs2.Find "StockCode = '" & rs.StockCode & "'"

Now use the matching description you found from rs2.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Thanks but how do I report on this?

Do I have a 3rd recordset for a table3 on which the report will be based and
then process each record of rs, each time adding it to table 3 with the
appropriate description?

If yes, I am not sure how to get these into table3

Al
 
Ah. You didn't say how you planned to use the recordset. Now it appears
that you want to use it as input to a report. What is it you're really
trying to do? Where is this code running now? What does the rest of the
code look like?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Back
Top