Difficult quiry with 3 tables

  • Thread starter Thread starter Jens Burup
  • Start date Start date
J

Jens Burup

Hi ng
This one have puzzled me for days.
Main table [Equipment] contain itemno descriptions, mType,pType . etc... for
a product range
Column Column Column..........
Itemno mType mType Remarktext
551111 240 C01 Bla1.
bla1.......
551111 242 C01
554500 240 C04
556000 242 C06
558000 240 C05

The remark text is linked to an itemno based upon 2 parametres mType and
pType values and the itemno
To do this, I use on small table called EquipRelation containing 3 coloumns
containing mType, pType and RemarkID
this as a sample
Column Column Column
ptype values mType values RemarkID
C01 240 1
C01 242 2
C04 242 3
C04 240 4

The 3.rd table [Remarks] contaning the remark text is linked to the
EquipRelation table like this

Column Column Column
RemarkID Itemno RemarkText
1 551111 Bla1. bla1.......
2 554500 Bla2. bla2.......
3 556000 Bla3. bla3.......
4 558000 Bla4. bla4.......

As you see, the tricky part is, that the itemno it self is not unike, but
the combination of the itemno & pType & mType identify the row to add the
text.
Only the 1. row[551111] should have a remarktext according to the rules.

I have tried all combination I know of, but I do not get the correct numbers
of records that should show up.
Is it posible to this in onequiry?
Is it posible to use a SELECT statement more than one time in the same
quiry?

Hope this not to difficult to understand
Regards
Jens
 
Jens, your relationships seem a little strange
Equipment seems to relate to EquipRelation on both mType and ptype.
EquipRelation seems to relate to Remarks on RemarkID
However. . .
Remarks links back to Equipment on Itemno, ANDcontains the same field
Remarktext


Can you clarify which of these (if any) are queries, and which are the PK's
FK's?
 
Jens Burup said:
Hi ng
This one have puzzled me for days.
Main table [Equipment] contain itemno descriptions, mType,pType . etc... for
a product range
Column Column Column..........
Itemno mType mType Remarktext
551111 240 C01 Bla1.
bla1.......
551111 242 C01
554500 240 C04
556000 242 C06
558000 240 C05

The remark text is linked to an itemno based upon 2 parametres mType and
pType values and the itemno
To do this, I use on small table called EquipRelation containing 3 coloumns
containing mType, pType and RemarkID
this as a sample
Column Column Column
ptype values mType values RemarkID
C01 240 1
C01 242 2
C04 242 3
C04 240 4

The 3.rd table [Remarks] contaning the remark text is linked to the
EquipRelation table like this

Column Column Column
RemarkID Itemno RemarkText
1 551111 Bla1. bla1.......
2 554500 Bla2. bla2.......
3 556000 Bla3. bla3.......
4 558000 Bla4. bla4.......

As you see, the tricky part is, that the itemno it self is not unike, but
the combination of the itemno & pType & mType identify the row to add the
text.
Only the 1. row[551111] should have a remarktext according to the rules.

I have tried all combination I know of, but I do not get the correct numbers
of records that should show up.
Is it posible to this in onequiry?
Is it posible to use a SELECT statement more than one time in the same
quiry?

Hope this not to difficult to understand
Regards
Jens
Click to expand...
Click to expand...

JohnFol said:
Jens, your relationships seem a little strange
Equipment seems to relate to EquipRelation on both mType and ptype.
EquipRelation seems to relate to Remarks on RemarkID
However. . .
Remarks links back to Equipment on Itemno, ANDcontains the same field
Remarktext


Can you clarify which of these (if any) are queries, and which are the PK's
FK's?
Click to expand...
Hi John
[Equipment] is a join from 2 other tables with a total of 5000 records.
pType and mType + 3 other criteriaes brings down [EQuipment] to about 20-30
rows for my datagrid.
I only want to show a record from the [Remark] in [Equipment] if the
pType and mType from [Remarksrelation] + ItemNo[Remarks] are true
with pType and mType in [Equipment]

As 551111 exist with more combinations of pType and mType values in the
maintable, I need a table [EquipRelation] to check if the combination is
valid ,
if true I get an ID to select the correct remark in[Remarks] if itemno
[Equipment] is the same in [Remarks]

Please explain what PK or FK means
jens
 
It sounds like there is a join missing between EquipRelation.RemarkID and
Remarks.RemarkID



Jens Burup said:
Jens Burup said:
Hi ng
This one have puzzled me for days.
Main table [Equipment] contain itemno descriptions, mType,pType .
Click to expand...
etc...
for
a product range
Column Column Column..........
Itemno mType mType Remarktext
551111 240 C01 Bla1.
bla1.......
551111 242 C01
554500 240 C04
556000 242 C06
558000 240 C05

The remark text is linked to an itemno based upon 2 parametres mType and
pType values and the itemno
To do this, I use on small table called EquipRelation containing 3 coloumns
containing mType, pType and RemarkID
this as a sample
Column Column Column
ptype values mType values RemarkID
C01 240 1
C01 242 2
C04 242 3
C04 240 4

The 3.rd table [Remarks] contaning the remark text is linked to the
EquipRelation table like this

Column Column Column
RemarkID Itemno RemarkText
1 551111 Bla1. bla1.......
2 554500 Bla2. bla2.......
3 556000 Bla3. bla3.......
4 558000 Bla4. bla4.......

As you see, the tricky part is, that the itemno it self is not unike, but
the combination of the itemno & pType & mType identify the row to add the
text.
Only the 1. row[551111] should have a remarktext according to the rules.

I have tried all combination I know of, but I do not get the correct numbers
of records that should show up.
Is it posible to this in onequiry?
Is it posible to use a SELECT statement more than one time in the same
quiry?

Hope this not to difficult to understand
Regards
Jens
Click to expand...
Click to expand...

JohnFol said:
Jens, your relationships seem a little strange
Equipment seems to relate to EquipRelation on both mType and ptype.
EquipRelation seems to relate to Remarks on RemarkID
However. . .
Remarks links back to Equipment on Itemno, ANDcontains the same field
Remarktext


Can you clarify which of these (if any) are queries, and which are the PK's
FK's?
Click to expand...
Hi John
[Equipment] is a join from 2 other tables with a total of 5000 records.
pType and mType + 3 other criteriaes brings down [EQuipment] to about 20-30
rows for my datagrid.
I only want to show a record from the [Remark] in [Equipment] if the
pType and mType from [Remarksrelation] + ItemNo[Remarks] are true
with pType and mType in [Equipment]

As 551111 exist with more combinations of pType and mType values in the
maintable, I need a table [EquipRelation] to check if the combination is
valid ,
if true I get an ID to select the correct remark in[Remarks] if itemno
[Equipment] is the same in [Remarks]

Please explain what PK or FK means
jens
Click to expand...
 
Hi John
[Equipment] is a join from 2 other tables with a total of 5000 records.
pType and mType + 3 other criteriaes brings down [EQuipment] to about 20-30
rows for my datagrid.
I only want to show a record from the [Remark] in [Equipment] if the
pType and mType from [Remarksrelation] + ItemNo[Remarks] are true
with pType and mType in [Equipment]

As 551111 exist with more combinations of pType and mType values in the
maintable, I need a table [EquipRelation] to check if the combination is
valid ,
if true I get an ID to select the correct remark in[Remarks] if itemno
[Equipment] is the same in [Remarks]

Please explain what PK or FK means
jens
Click to expand...
Click to expand...

JohnFol said:
It sounds like there is a join missing between EquipRelation.RemarkID and
Remarks.RemarkID
Click to expand...

There is a realation id between the two tables, but it is not shown in the
above sample.
Anyway I solved the issue with a not very sophisticated sololusion, but it
works.
I added Itemno and Remarks to [EquipRelation](650 rows) to, but keeping
[Remarks] to administrate changes in the original remarks cell.
When some changes takes place, then I manually run an update query between
[Remarks](122 rows) and [EquipRelation].

jens
 
Back
Top