|
| |
|
|
jorge
Posts: 31
Joined: 9/8/2003
Status: offline
 |
Whats wrong with my query - 9/14/2003 16:18:50
Hi all
This is a query created in access:
SELECT Duiven.Rin, Vader.Rin, Moeder.Rin, VaderVader.Rin, MoederVader.Rin, VaderMoeder.Rin, MoederMoeder.Rin
FROM (((((Duiven LEFT JOIN Duiven AS Vader ON Duiven.Vad=Vader.Rin) LEFT JOIN Duiven AS Moeder ON Duiven.Moe=Moeder.Rin) LEFT JOIN Duiven AS VaderVader ON Vader.Vad=VaderVader.Rin) LEFT JOIN Duiven AS MoederVader ON Vader.Moe=MoederVader.Rin) LEFT JOIN Duiven AS VaderMoeder ON Moeder.Vad=VaderMoeder.Rin) LEFT JOIN Duiven AS MoederMoeder ON Moeder.Moe=MoederMoeder.Rin
WHERE (((Duiven.Rin)="2326169"));
Now when i copy paste this into the custom query field from the frontpage DRW
it gives me a blank page
Anyone knows the reason for this?
|
|
|
|
jorge
Posts: 31
Joined: 9/8/2003
Status: offline
|
RE: Whats wrong with my query - 9/14/2003 16:20:21
Oh and in access the query works just fine
| |
|
|
|
jorge
Posts: 31
Joined: 9/8/2003
Status: offline
|
RE: Whats wrong with my query - 9/14/2003 17:49:12
i tried to use it like this:
SELECT Duiven.Rin, Vader.Rin, Moeder.Rin, VaderVader.Rin, MoederVader.Rin, VaderMoeder.Rin, MoederMoeder.Rin
FROM (((((Duiven LEFT JOIN Duiven AS Vader ON Duiven.Vad=Vader.Rin) LEFT JOIN Duiven AS Moeder ON Duiven.Moe=Moeder.Rin) LEFT JOIN Duiven AS VaderVader ON Vader.Vad=VaderVader.Rin) LEFT JOIN Duiven AS MoederVader ON Vader.Moe=MoederVader.Rin) LEFT JOIN Duiven AS VaderMoeder ON Moeder.Vad=VaderMoeder.Rin) LEFT JOIN Duiven AS MoederMoeder ON Moeder.Moe=MoederMoeder.Rin
WHERE (((Duiven.Rin)= '::Rin::'
))
Rin is a value it gets from another result page, via a hyperlink:
pedigree.asp?Rin=<%=FP_FieldURL(fp_rs,"Rin")%>
still it doesn't work
it gives me an empty table,
| |
|
|
|
jorge
Posts: 31
Joined: 9/8/2003
Status: offline
|
RE: Whats wrong with my query - 9/15/2003 18:06:10
Hi Spooky,
I personaly think this is an incopatibility between FP2002 DRW and Access.
take a look at http://users.bigpond.net.au/abrowne1/ser-06.html
if you have a couple of minutes just try to use the query that's explained over there, just try it out with 3 generations and you'll see what i mean. Just create that sipmle query in access and try to use it in DRW, the result is not the same at all.
ok, i'm verry newbie, but i don't think i'm doing something wrong
if you could confirm me that already would be something
| |
|
|
|
jorge
Posts: 31
Joined: 9/8/2003
Status: offline
|
RE: Whats wrong with my query - 9/16/2003 6:11:22
I've set up a page to help me solve this problem
http://pigeons.no-ip.info/
I would be happy if you can tell me what i'm doing wrong for not getting the same result on DRW as in access.
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: Whats wrong with my query - 9/16/2003 12:23:29
Hi Jorge,
If you get a blank page it means that the query did not find any record according the condition in it. If you don't get an error it means that the query could not retrieve data. So you have to look what is wrong Logically not syntactically. I am sure it is one of the AND.
I have not faced any incompatibility between Access and Frontpage 2002.
It would be helpful if you tell us what are you trying to find in order to see what is wrong with the code. But since you say that the code works in Access and it gives data, why don't you save that query in Access and make it the data source for your Frontpage DRW, do not copy the SQL from Access to Frontpage, treat the query as a table and go to Frontpage DRW and select it from there, you would see it as a View not as a query.
After you select the query as the data source, go to options and select the condition.
Try that, it should work.
| |
|
|
|
jorge
Posts: 31
Joined: 9/8/2003
Status: offline
|
RE: Whats wrong with my query - 9/16/2003 15:18:52
Hhammash,
I really tried that. I used the query(that worked in access) as the data source for my Frontpage DRW(VIEW), then i added criteria in the options menu,
But it doesnt work
Again, this is the sql of my query in access:
SELECT Duiven.Rin, Duiven_1.Rin, Duiven_2.Rin, Duiven_1.Vad, Duiven_1.Moe, Duiven_2.Vad, Duiven_2.Moe
FROM (Duiven LEFT JOIN Duiven AS Duiven_2 ON Duiven.Moe = Duiven_2.Rin) LEFT JOIN Duiven AS Duiven_1 ON Duiven.Vad = Duiven_1.Rin;
What i'm trying to find is a pedigree.
I have only 1 table called Duiven, that table has 3 fields called:
Rin
Vad
Moe
So I want the query to generate a pedigree(Pigeons in this case)
Rin is the ringnumber of a pigeon
Vad is his fathers ringnumber
Moe is his mothers ringnumber
If these 3 things are known its possible to generate a pedigree from all the pigeons that exist in that table.
| |
|
|
|
jorge
Posts: 31
Joined: 9/8/2003
Status: offline
|
RE: Whats wrong with my query - 9/16/2003 15:59:09
http://pigeons.no-ip.info/queryproblem.asp
maybe this url will work
or maybe i was just updating that page when u try to visit it
| |
|
|
|
jorge
Posts: 31
Joined: 9/8/2003
Status: offline
|
RE: Whats wrong with my query - 9/16/2003 18:41:28
I have found the problem.
In the query i created I used 3 times the same table, i just gave it another name(in access) but the field names remained the same. That is the problem . It think that frontpage is confused beceause of the fieldnames that are the same in the 3 tables.
So what i did now is I copied the same table 3 times, and i changed the field names to something different in each table.
Now my query works in frontpage, but i dont like this solution
beceause now each time I add a new record I have to duplicate it into the other 2 tables.
Is someone aware of this problem? and isnt there a way to create this query with 1 and the same table?
| |
|
|
|
jorge
Posts: 31
Joined: 9/8/2003
Status: offline
|
RE: Whats wrong with my query - 9/16/2003 20:02:45
Is there a way to address field names in a join query? Beceause if you use tables with the same fieldnames, the fields cannot be identified
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Whats wrong with my query - 9/17/2003 10:37:16
Just a shot in the semi-dark, but have you tried bracketing the field names, i.e, [Duiven_1.Rin]?
| |
|
|
|
jorge
Posts: 31
Joined: 9/8/2003
Status: offline
|
RE: Whats wrong with my query - 9/17/2003 12:44:20
I've tried it like this but with no result:
SELECT [Duiven.Rin], [Duiven.Vad], [Duiven.Moe], [Duiven_1.Vad], [Duiven_1.Moe], [Duiven_2.Vad], [Duiven_2.Moe]
FROM (Duiven LEFT JOIN Duiven AS Duiven_1 ON Duiven.Vad = Duiven_1.Rin) LEFT JOIN Duiven AS Duiven_2 ON Duiven.Moe = Duiven_2.Rin;
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Whats wrong with my query - 9/17/2003 13:43:02
quote:
In the query i created I used 3 times the same table, i just gave it another name(in access) but the field names remained the same. That is the problem . It think that frontpage is confused beceause of the fieldnames that are the same in the 3 tables.
I am confused by this statement. The query in your first post references 6 tables. SELECT Duiven.Rin, Vader.Rin, Moeder.Rin, VaderVader.Rin, MoederVader.Rin, VaderMoeder.Rin, MoederMoeder.Rin. Each word that appears before the "." is a table (or query) name. I wonder, is the query you created in Access based on other queries? In other words are some of the references in your SELECT statement to other queries? I ask because the DRW can build a results region based on a query, but has a real hard time building results on a query of a query. Does that make sense?
How many tables, not queries, are you really trying to pull from?
| |
|
|
|
jorge
Posts: 31
Joined: 9/8/2003
Status: offline
|
RE: Whats wrong with my query - 9/17/2003 18:20:09
Actually the query uses only 1 table(Duiven), look:
Don't forget that i created this query in access
SELECT Duiven.Rin, Duiven.Vad, Duiven.Moe, Duiven_1.Vad, Duiven_1.Moe, Duiven_2.Vad, Duiven_2.Moe
FROM (Duiven LEFT JOIN Duiven AS Duiven_1 ON Duiven.Vad=Duiven_1.Rin) LEFT JOIN Duiven AS Duiven_2 ON Duiven.Moe=Duiven_2.Rin;
I used 3 copy's of the same table(Duiven), access automaticly calls them Duiven, Duiven_1, Duiven_2.
I posted this question on some other forums, and now i think i know the cause of the problem:
******************************************************************************************************
******************************************************************************************************
...it has to do with just standard SQL coding and ADO.
You *MUST* use aliases for the duplicated field names. Because outside of Access, only the field name is visible, *not* the table name:
SELECT Duiven_0.Rin AS D0Rin, Duiven_1.Rin AS D1Rin, Duiven_2.Rin AS D2Rin,
Duiven_1.Vad AS D1Vad, Duiven_1.Moe AS D1Moe,
Duiven_2.Vad AS D2Vad, Duiven_2.Moe AS D2Moe
FROM (Duiven AS Duiven_0 LEFT JOIN Duiven AS Duiven_2 ON Duiven.Moe = Duiven_2.Rin)
LEFT JOIN Duiven AS Duiven_1 ON Duiven.Vad = Duiven_1.Rin;
And then you use the field names D0Rin, D1Rin, etc., to get the data from those fields.
******************************************************************************************************
******************************************************************************************************
You see, whats important here is =====> Because outside of Access, only the field name is visible, *not* the table name.
I'm gonna try this out now, i have a good feeling about this.
| |
|
|
|
jorge
Posts: 31
Joined: 9/8/2003
Status: offline
|
RE: Whats wrong with my query - 9/17/2003 18:44:17
this did it,
SELECT Duiven.Rin AS DRin, Duiven_1.Rin AS D1Rin, Duiven_2.Rin AS D2Rin, Duiven_1.Vad AS D1Vad, Duiven_1.Moe AS D1Moe, Duiven_2.Vad AS D2Vad, Duiven_2.Moe AS D2Moe
FROM (Duiven LEFT JOIN Duiven AS Duiven_2 ON Duiven.Moe = Duiven_2.Rin) LEFT JOIN Duiven AS Duiven_1 ON Duiven.Vad = Duiven_1.Rin;
thanks to everyone who helped me
jorge
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 
