self join query in access

[Shahzad]

dear respected gurus,

I would like to knew how to apply append,insert query for a self table
where no primary keys issues.

i do have problem say there are 5 rows of single record, this is data
of motor under one policy. say policy is john2004, so john is owner of
5 cars. now on next year the card has got renew with same
conditions(same amounts all), so i need to insert new recocrd via
query in new policy is john2005, with same condition no change

as the policy renew at masters level but on child table does not
renew...

i have been trying since 3 days and last i decided to consult with my
GURUS

i did try a lots but does not works. like

=>insert into tblMotor values (Select * from tblMotor where
policyno="john2004" from tblmotor);

=>insert into tblMotor (Select * from tblMotor where
policyno="john2004");
NOTE: above two are wrong, as renew policy is coming from other system
and there are records in master but child is empty
=>insert into tblMotor (policyno,plateno,suminsured,premiuum)
values(policyno,plateno,suminsured,premiuum);

any hints or tips for self join queries will be appreciated
rgds
shahzad

 

[John Vinson]

dear respected gurus,

I would like to knew how to apply append,insert query for a self table
where no primary keys issues.

i do have problem say there are 5 rows of single record, this is data
of motor under one policy. say policy is john2004, so john is owner of
5 cars. now on next year the card has got renew with same
conditions(same amounts all), so i need to insert new recocrd via
query in new policy is john2005, with same condition no change

What's the Primary Key? You say "five rows of single record" - surely
you have two tables, Policy related one to many to tblMotor?

as the policy renew at masters level but on child table does not
renew...

i have been trying since 3 days and last i decided to consult with my
GURUS

i did try a lots but does not works. like

=>insert into tblMotor values (Select * from tblMotor where
policyno="john2004" from tblmotor);

Well, you have an extra clause "from tblmotor" there which is
incorrect syntax...

=>insert into tblMotor (Select * from tblMotor where
policyno="john2004");

and this would certainly fail since by selecting * (all fields) you're
going to be trying to add a duplicate primary key.

NOTE: above two are wrong, as renew policy is coming from other system
and there are records in master but child is empty
=>insert into tblMotor (policyno,plateno,suminsured,premiuum)
values(policyno,plateno,suminsured,premiuum);

But Plateno surely isn't in tblPolicy is it?

I'm trying to understand what fields exist in your two tables, and
where the data for the new record should come from. It would also help
to know what is the Primary Key of tblMotor.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[tombsy]

In my world, self join tables must have a primary key on the child for
parent/child relationships to exist.


p.s I'm not a guru, I'm an Applications Developer.

 

[Shahzad]

dear Gurus,

yes there is master/details is there, plateno is part of details,
where is policyno is primary key in master tables and relation is
1-to-Many

now the question is this this is a self join query, we issue a policy
in master, policyno=A0604J0205,where there are no details available,
whoever i have cascade update also between this two tables

now i need to run such query which will collect(select all records
from details of A0406J0204), and insert/append AS A0604J0205

this is VBA query, if any one send any kind of normal query will also
sufficent/fruitfull
+++++++++++++++++++++++++++++++++++++
Public Function Motor_Insert2()

strSQL = "INSERT INTO tblDetails (POLICYNO, VEHICLES, MODEL, PlateNo )
" & _
" SELECT " & _
" tblDetails.POLICYNO, tblDetails.VEHICLES, tblDetails.MODEL,
tblDetails.PlateNo " & _
" FROM tblDetails " & _
" WHERE (((tblDetails.POLICYNO)= '" & A0604J0205 & "')); "

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
End Function
++++++++++++++++++++++++++++++++++++++

 

[John Vinson]

dear Gurus,

yes there is master/details is there, plateno is part of details,
where is policyno is primary key in master tables and relation is
1-to-Many

now the question is this this is a self join query, we issue a policy
in master, policyno=A0604J0205,where there are no details available,
whoever i have cascade update also between this two tables

I do not understand. A "Self Join Query" is one in which you create a
Query by adding a table to the query grid TWICE, joining one instance
of the table to the other instance of the table. This is sometimes
useful in a heirarchy or other special circumstances; but I do not see
how a self join is useful or relevant in this case.

now i need to run such query which will collect(select all records
from details of A0406J0204), and insert/append AS A0604J0205

This would just be a simple Append query. You can append from a table
into the same table; this is *NOT* considered a "self join" query.
Maybe that's where the confusion is coming from!

this is VBA query, if any one send any kind of normal query will also
sufficent/fruitfull
+++++++++++++++++++++++++++++++++++++
Public Function Motor_Insert2()

strSQL = "INSERT INTO tblDetails (POLICYNO, VEHICLES, MODEL, PlateNo )
" & _
" SELECT " & _
" tblDetails.POLICYNO, tblDetails.VEHICLES, tblDetails.MODEL,
tblDetails.PlateNo " & _
" FROM tblDetails " & _
" WHERE (((tblDetails.POLICYNO)= '" & A0604J0205 & "')); "

This will find all of the existing records in tblDetails where
PolicyNo is A0604J0205 and append them into tblDetails. Of course
there are two problems here: 1) there aren't any records with that
policy number in tblDetails (yet); and 2) if there were, you couldn't
append them because they'd violate the Primary Key constraint!

Try:

strSQL = "INSERT INTO tblDetails " & _
" (POLICYNO, VEHICLES, MODEL, PlateNo )" & _
" SELECT 'A0604J0205' AS PolicyNo, " & _
" tblDetails.VEHICLES, tblDetails.MODEL,tblDetails.PlateNo " & _
" FROM tblDetails " & _
" WHERE (((tblDetails.POLICYNO)= '" & A0604J0204 & "')); "

This will *search* for the data from policy A0604J0204, and construct
a new record with a new policy number, A0604J0205.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps