Report with mulitiple data sources

[itlotl]

I'm having problem figuring out how to display some data I need on a report. I have tried two different methods:

1)I wrote a series of queries to feed into a subreport. When I ran the queries on a small sample it worked just fine, but when I ran it on the real data(more than 80 fields, more than 700 rows), the 3rd query took way, way too long which made linking it to my subreport impossible, and it uses most if not all of the computing power(my computer is not super powerful, but it is fairly decent). I've made examples of the first 3 queries to give you an idea:

Query1:

SELECT Orders.OrderID AS OrI, Orders.OrderName AS OrN FROM Orders;

(This is in order to compare OrderID against itself in the next query.)

Query2:

SELECT DISTINCT Orders.OrderID, Query1.OrI+' '+Query1.OrN AS NewOrderName
FROM Orders INNER JOIN Query1 ON (Query1.OrI) Like (Orders.OrderID+'*')
WHERE LEN(Orders.OrderID)>0 And INSTR(LEN(Orders.OrderID)+2,(Query1.OrI),"a certain subtring")=0;

Query3:

SELECT Orders.OrderID, Orders.OtherField AS NewOtherField FROM Orders WHERE Orders.OrderID NOT IN (Select [Query2].OrderID FROM [Query2]);

2) I have tried using both subreports and subforms to feed the data in. Access allows you to do this, but unlike the forms I have developed using vba in the past, I can't figure out how to get Access/vba to let me communicate between the report and the subreport/subform in either direction. I'm also having problems communicating directly with the subform/report (from the vba code). I've tried everything I found in the books I have, Access/vba help, and the internet, and I'm stuck.

Any help? Ideas on either method would be greatly appreciated.

Thanks,
Jenni

 

[PCLewis]

The number of full table scans you will be causing is probably the cause of your speed dilema in the report. If you gave some more information like the data types and maybe some sample data, I am sure that a more efficient query coul dbe constructed.

The two things in the query that are going to make it slow, and negate any indexing you have, are the INSTR in the where clause and the LIKE in the inner join.

As far as "communicating" with a subreport goes, maybe all you are having problems with is addressing the subreport. A trick I use when far too lazy to work it out for myself, is to start the report (no data necessary) then in design mode in any field that lets you use the expression builder, navigate to your subform using the gui, and then you will have the proper syntax to address your subreport. Whether you need to address it from outside the subreport or not is another story, for which there is not enough information to comment on.

Some assumptions I made from the post:
OrderID is not an autonumber field. It is not a mandatory or unique field and probably is not numeric. More than likely it is alphanumeric and contains data like: "A1000", "A1000-1", "A1000-2" and hence th elike in the join was to try and associate all the "A1000" orders together. I think some more info would go a long way.

I don't understand the WHERE clause in Query2, since OrderID is the same data as Ori so doing an INSTR with a start position of Len(OrderID)+2 on Ori will return 0 every time. I presume you got that wrong when typing the question but it looks different in your real world example.

 

[itlotl]

You are right. OrderID is a datatype that contains data like, "A1000", "A1000-1", "A1000-2" where "A1000-1" and "A1000-2" are children of "A1000." The purpose of the first two queries is to find all of the OrderIDs that have children and list them with their children. That's why I have the OrderID and Ori. It's the only way I could figure out to get Access/Sql to let me compare OrderID to itself in order to identify an OrderID that has children. The WHERE clause has two functions: 1) Some of the OrderIDs have not been defined yet. So, I don't want to include the blank OrderIDs in this query. 2)OrderIDs can not only have children, but they can also have grandchildren. The INSTR statement is to make sure that I'm only getting the children and not the grandchildren.
The final query statement is to find all of the OrderIDs that do not have children (and therefore are not in the previous query).
Does that answer your questions?