Multiple Queries in one or Recordsets or...? 2

[campagnolo1]

Greetings,

complete Access newbie here with a slight problem:
I'm trying to create a table in Access that then I want to run a Crystal Report on. I have linked my Access database to some FoxPro tables and then created 3 queries. The first query uses the linked tables and then creates a table. The second query uses the table created by the first query and creates a second table. The third query uses the second table and creates a third table. That 3rd table is then used by the report. So far all of this works just fine, I even wrote a macro to run all 3 queries in order for easy updating of the data.

Still with me?

Good!

Unfortunately having to create 3 tables (there are other projects where I would have to create 6 to 8 tables) makes the database quite large in size.

My question now: can I either create one large query that accomplishes what I need, or can I create temporary tables (in FoxPro I use Cursors), or is there something I don't know about that can help me accomplish my task?

Just as an example I will post the code for 2 of the queries.

Query 1 uses the linked tables:

SELECT slheader.Docid, slheader.Ordrstat, slheader.Billto, slheader.Coname, slheader.Doctype, slheader.Salesman, slheader.Donedate, slheader.Terms, slheader.Comment, sllines.Descrip, sllines.Needby, sllines.Lineseq, sllines.Orderqty, sllines.Shipqty, sllines.Item, sllines.Shipfrom, sllines.Delmark, sllines.Linestat, sllines.Picknote, sllines.Linetype, slheader.Custpono, slheader.Billlocn, Left([sllines]![doctype],1)="S" AS Expr1 INTO W1
FROM sllines LEFT JOIN slheader ON (sllines.Docid = slheader.Docid) AND (sllines.Doctype = slheader.Doctype)
WHERE (((slheader.Ordrstat)="A" Or (slheader.Ordrstat)="P") AND ((sllines.Linestat)<>"C")) AND Left(sllines.doctype,1)="S";

Query 2 uses the table created in query 1 and linked tables:

SELECT w1.*, slcust.Code1, slcust.Crstatus INTO W2
FROM w1 LEFT JOIN slcust ON w1.Billto = slcust.coid AND W1.Billlocn = slcust.Locid;

Any help is greatly appreciated!

Cheers,

Chris

 

[Golom]

Just a simple substitution

SELECT w1.*, C.Code1, C.Crstatus INTO W2

FROM 

(SELECT H.Docid, H.Ordrstat, H.Billto, H.Coname, H.Doctype, H.Salesman, 
       H.Donedate, H.Terms, H.Comment, L.Descrip, L.Needby, L.Lineseq, 
       L.Orderqty, L.Shipqty, L.Item, L.Shipfrom, L.Delmark, L.Linestat, 
       L.Picknote, L.Linetype, H.Custpono, H.Billlocn, 
       Left([sllines]![doctype],1)="S" AS Expr1 

FROM sllines As L LEFT JOIN slheader As H 
     ON (L.Docid = H.Docid) AND (L.Doctype = H.Doctype)

WHERE H.Ordrstat IN ("A","P") AND L.Linestat)<>"C" AND Left$(L.Doctype,1)="S") As W1

LEFT JOIN slcust As C ON w1.Billto = C.coid AND W1.Billlocn = C.Locid

 

[dhookom]

You haven't posted anything that suggests why you need to create tables rather than combining tables and queries. If this is a Crystal Reports issue then you might have better luck asking in a CR forum.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What is the SQL code of the 3rd query ?
Anyway, for the first two:

SELECT H.Docid, H.Ordrstat, H.Billto, H.Coname, H.Doctype, H.Salesman, H.Donedate, H.Terms, H.Comment
, L.Descrip, L.Needby, L.Lineseq, L.Orderqty, L.Shipqty, L.Item, L.Shipfrom, L.Delmark, L.Linestat
, L.Picknote, L.Linetype, H.Custpono, H.Billlocn
FROM (sllines L
INNER JOIN slheader H ON L.Docid = H.Docid AND L.Doctype = H.Doctype)
LEFT JOIN slcust C ON H.Billto = C.coid AND H.Billlocn = C.Locid
WHERE H.Ordrstat IN ('A','P') AND L.Linestat<>'C' AND Left(L.doctype,1)='S'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[campagnolo1]

Well, this is all good stuff guys, thank you for the quick responses!
@PHV and Golom:
Thank you for the code, I think that is pretty much what I'm after.
The third query would be as followed. The results of that query than should get saved as the actual result table which is used by the Crystal report.

SELECT w2.*, imlocn.Locid, imlocn.Onhand, imlocn.Woalloc INTO W3
FROM w2 LEFT JOIN imlocn ON w2.Item=imlocn.Item
WHERE imlocn.Locid = "MAIN";

@dhookom:
The reson I don't want each query to get written to a table is because it inflates the file size of the access database, which I'm trying to keep to a minimum. This particular project is not that big, but I have tried a different one before that created a database of 800MB, even after compression.

Cheers,

Chris

 

[PHV]

What about this ?

SELECT H.Docid, H.Ordrstat, H.Billto, H.Coname, H.Doctype, H.Salesman, H.Donedate, H.Terms, H.Comment
, L.Descrip, L.Needby, L.Lineseq, L.Orderqty, L.Shipqty, L.Item, L.Shipfrom, L.Delmark, L.Linestat
, L.Picknote, L.Linetype, H.Custpono, H.Billlocn, C.Code1, C.Crstatus, I.Locid, I.Onhand, I.Woalloc
FROM ((sllines L
INNER JOIN slheader H ON L.Docid = H.Docid AND L.Doctype = H.Doctype)
INNER JOIN slcust C ON H.Billto = C.coid AND H.Billlocn = C.Locid)
INNER JOIN imlocn I ON L.Item=I.Item
WHERE H.Ordrstat IN ('A','P') AND L.Linestat<>'C' AND Left(L.doctype,1)='S' AND I.Locid='MAIN'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[campagnolo1]

PHV,

it seems to work, but since I'm on my way out I won't have time to compare the reports to make sure the results match.

Thanks you all so far for the help and I think I have gotten the concept of how to do it. I'm sure I will post more on Monday if I get stcuk.

Happy Friday!

Chris

 

[Golom]

You may need to critically appraise this but my experience with CR is that it can use a query from Access just as it would use a table. You should be able to base your report on a query that produces the result that you sent to table W3 and then the whole MAKE TABLE scenario ceases to be.

 

[campagnolo1]

Golom,

I (think) I will need the query in a table since the report will be run from multiple people. The way we have it set up right now is in a VFP project that runs as an exe. But some employees still try to run the report from CR and then wonder why they get errors (because they haven't run the exe which runs the query). So I want to set up this query in Access and then have it run maybe every 15 minutes on a schedule to refresh the table. That way the employees only have to run the report from CR and not have to worry about the table being up-to-date.

I will now compare the report results and let you know how it turned out.

Chris

 

[Golom]

I guess my point was that a query-based report is always current because it extracts data from the base tables at the time the report runs. There's no such thing as its being out-of-date and no need to schedule background processing to refresh the static tables that you are building.

 

[campagnolo1]

I'm still fairly new to Access, so how would I use the Access query with CR? Or is that something I should ask in a different forum?

 

[Golom]

I have a fairly old release of CR (8.something) and I had to build an ODBC data source to use an Access query. Later releases can (I gather) handle tables and queries without messing with ODBC. Try forum766 as a starting point.

 

[campagnolo1]

Golom,

I'm already testing with it and so far it looks pretty good. I'm getting one database error, but I think that's more a CR issue.
I think I can say that my question has been answered and want to thank you very much for your help. I have a feeling that I will be back in this forum more often now.

Cheers,

Chris