Run consecutive co-dependent queries 5

[cutestuff]

hi,

Is there a more efficient way to do the following:

I have 6 queries that are dependent on each other. What I mean is: i need to run the first query to feed the second query and so on. I can run four queries ok but as soon as i get to the fifth one, it says "not enough disk space" and it takes a while.

Can someone please tell me how to get around it? Or is there a more efficient way to do it?

The data tables change daily as it is fed from excel.

thanks in advance!

 

[dhookom]

Can you describe what you mean by "dependent on each other" and explain why they need to be run separately? Are they action queries?

Duane
Hook'D on Access
MS Access MVP

 

[cutestuff]

Perhaps if I can tell you what I am trying to do. Maybe having multiple queries is not the way to go.
I have a spreadsheet with 2 columns.
The first column contains a list of items.
The second column also contains a list of items.
An item on column1 can point to an item on column 2 and vice-versa.
So for instance (the data looks like this):
Column1 Column2
A -> B
B -> C
C -> D
D -> E
E -> F
I am trying to achieve:
A -> B -> C -> D -> E -> F

(In the example above, A,B,C,D,E,F are sample data)

So from 2 columns I would end up with 6 columns all related to each other. But to get C (A->B->C) I would have to use B as A.
Does that make sense? (Sorry if it's a little confusing).

Thanks so much,
j

 

[MajP]

Do all have exactly six relations? Is it really textual data like "A" "B"? Could you provide some real data?

 

[dhookom]

Is there any SQL views you can share? It seems there are no action queries so you shouldn't have to "open" more than a single query.

Duane
Hook'D on Access
MS Access MVP

 

[cutestuff]

the data is in text format - it's a combination of letters and numbers.
i can't post the exact data since it's company policy but i will try and make up some dummy data.

i will post the sql view when i get to work tomorrow.
thanks,
j

 

[MajP]

You can do it in one, I did it in two for ease

qryColOne

SELECT 
 A.fldOne AS ColumnOne, 
 B.fldTwo
FROM 
 tblOne AS A LEFT JOIN tblOne AS B ON A.fldOne = B.fldTwo
WHERE 
 B.fldTwo Is Null
ORDER BY A.fldOne;

SELECT 
 qryColOne.ColumnOne, 
 B.fldTwo AS columnTwo, 
 C.fldTwo AS ColumnThree, 
 D.fldTwo AS ColumnFour, 
 E.fldTwo AS ColumnFive
FROM 
  (((qryColOne LEFT JOIN tblOne AS B ON qryColOne.ColumnOne = B.fldOne) LEFT JOIN tblOne AS C ON B.fldTwo = C.fldOne) LEFT JOIN tblOne AS D ON C.fldTwo = D.fldOne) LEFT JOIN tblOne AS E ON D.fldTwo = E.fldOne
ORDER BY 
 qryColOne.ColumnOne;

 

[cutestuff]

I will try it and let you know.
thanks in advance.

 

[MajP]

here is my data

ID	fldOne	FldTwo
1	a	b
2	b	c
3	c	d
4	d	e
5	e	f
6	f	g
7	A1	A2
8	A2	A3
9	A3	A4
10	A4	A5
11	A5	A6
12	RS	RT
13	RT	RU
14	RU	RV
15	123	124
16	124	125
17	125	126
18	126	127
19	127	128
20	128	129

output

ColOne	colTwo	ColThree ColFour ColFive ColSix
RS	RT	RU	RV		
123	124	125	126	127	128
a	b	c	d	e	f
A1	A2	A3	A4	A5	A6

 

[cutestuff]

Sorry - what did you mean by:
tblOne As A LEFT JOIN tblOne as B
What does A and B refer to here? (trying to understand your code)

 

[cutestuff]

Never mind... I think I get it now... Am trying it right now.

 

[kjv1611]

just in case you didn't get it..

That's just using Aliases. You could rewrite it a little different to be more easily read (my opinion):

SELECT
 A.fldOne AS ColumnOne,
 B.fldTwo
FROM[highlight]
 tblOne A
    LEFT JOIN
 tblOne B
    ON A.fldOne = B.fldTwo[/highlight]
WHERE
 B.fldTwo Is Null
ORDER BY A.fldOne;

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

At least that's the way I have started doing all queries - in Access or in SQL Server or whatever. If it's written by me, currently, that's the format I use. I just personally thought it easier to read that way... kind of like when you have loops and conditional statements in VB/VBA and other languages.. Easy to read is important to me.

--

"If to err is human, then I must be some kind of human!" -Me

 

[cutestuff]

thanks kjv for the info .

I am getting the Column One and Two values ok, but i get blank for all the other columns. ? Not sure why...

SELECT qryColOne.ColumnOne, B.PNR AS ColumnTwo, C.PNR AS ColumnThree, D.PNR AS ColumnFour, E.PNR AS ColumnFive
FROM (((qryColOne LEFT JOIN qryFirstLink AS B ON qryColOne.ColumnOne = B.INST) LEFT JOIN qryFirstLink AS C ON  B.PNR= C.INST) LEFT JOIN qryFirstLink AS D ON C.PNR= D.INST) LEFT JOIN qryFirstLink AS E ON D.PNR= E.INST

 

[MajP]

http://tinypic.com/r/2r4jsck/6

you can do this easily in the query builder

 

[cutestuff]

weird thing is i do have exactly those relationships / connections. i can't figure out why it shows blanks on the 3rd, 4th and so on. <sigh>

 

[MajP]

My guess is that the two fields data are not exactly the same, especially coming from excel. In fldOne you probably have values like
A,B,C,D,E
And in fld two you have some spaces
A ,B , C , D, E

The will not link because "A" does not equal "A

 

[lespaul]

you could trim each one to test MajP's theory....

Leslie

 

[MajP]

If the culprit is spaces, the best is to clean up the data. However, you can also use the trim function;

LEFT JOIN tblOne AS C
ON
trim(B.fldTwo)=trim(C.fldOne))
LEFT JOIN tblOne AS D
ON
trim(C.fldTwo)=trim(D.fldOne))
LEFT JOIN tblOne AS E
ON
trim(D.fldTwo)=trim(E.fldOne))
LEFT JOIN tblOne AS F
ON
trim(E.FldTwo)=trim(F.fldOne);

 

[Malekish]

If you're going to do this on a regular basis and not just a one-off, you might look into how that data is getting put into the spreadsheet to eliminate inconsistencies like leading/trailing spaces. Are people manually entering data into the sheet (human error) or are they copy / pasting. If it's an export, how is that data being entered/generated in the original system?