wrong type of join?

[Geoff Cox]

Hello

I have returned to my attempt to get values from another table in a
query!

I have simplifed matters and made a little progress.

Problem - the query only returns 1 row! I get the row where the name
is school, 1118 is selected, type ="a", and the Renewal_cost = 100.

Is it the wrong type of join?

The SQL view for the query is

SELECT
[test-simple].name, [test-simple].[1118], [test-simple].[1116],
[test-simple].type,
IIf([1118],
Switch(
[type]="a",[test-simple-costs].school_1118,
[type]="c",[test-simple-costs].personal_1118,
[type]="d",[test-simple-costs].fe_1118),
IIf([1116],
Switch(
[type]="a",[test-simple-costs].school_1116,
[type]="c",[test-simple-costs].personal_1116,
[type]="d",[test-simple-costs].fe_1118),0))
AS Renewal_cost
FROM [test-simple] INNER JOIN [test-simple-costs]
ON [test-simple].ID = [test-simple-costs].ID;

If more info needed please say so!

Cheers

Geoff

 

[Douglas J. Steele]

As soon as one condition is found that matches (in this case [1118] being
true), none of the rest of the conditions are evaluated.

Perhaps now you understand why I was saying that your design wasn't a
particularly good one!

 

[Geoff Cox]

As soon as one condition is found that matches (in this case [1118] being
true), none of the rest of the conditions are evaluated.

Perhaps now you understand why I was saying that your design wasn't a
particularly good one!

OK! But isn't is possible to have an "else if"? Whch tells me I'm not
sure what IIF is - is not the same as If?

Cheers

Geoff

 

[Geoff Cox]

As soon as one condition is found that matches (in this case [1118] being
true), none of the rest of the conditions are evaluated.

Perhaps now you understand why I was saying that your design wasn't a
particularly good one!

Douglas,

It occurs to me that I'm not sure what to do re improving the design
of the table.

Remember I have created a simple table, called test-simple.

It only has the following fields

name (text)
1118 (yes/no)
1116 (yes/no)
type (text)

The value of type can be a, b or c. a=school, b=personal, c=fe
college.

The other table test-simple-costs has the costs in it, e.g. if 1118 is
selected and the type=c, this is for an 1118 personal subscription, if
1118 and type=d, this is for an FE college 1118 subscription.

The create field code in the simple-test-query is

RenewalCost: iif(
[1118],Switch(
[type]="a",[test-simple-costs].school_1118
,[type]="c",[test-simple-costs].personal_1118,
[type]="d",[test-simple-costs].fe_1118,
[1116],Switch(
[type]="a",[test-simple-costs].school_1116,
[type]="c",[test-simple-costs].personal_1116,
[type]="d",[test-simple-costs].fe_1116,
)))

How can I improve this setup?

Cheers

Geoff

 

[Douglas J. Steele]

The problem is that you've got fields named things like school_1118,
personal_1118 and fe_1118. What that does is hide information as the name of
the field!

Rather than a single row with field names like that, 1118 and "school"
should both be fields in the table, and you should have multiple rows:

SubscriptionNumber SubscriptionType SubscriptionFee
1116 school 85
1116 personal 50
1116 fe 100
1118 school 100
1118 personal 60
1118 fe 130
-
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

As soon as one condition is found that matches (in this case [1118] being
true), none of the rest of the conditions are evaluated.

Perhaps now you understand why I was saying that your design wasn't a
particularly good one!

Douglas,

It occurs to me that I'm not sure what to do re improving the design
of the table.

Remember I have created a simple table, called test-simple.

It only has the following fields

name (text)
1118 (yes/no)
1116 (yes/no)
type (text)

The value of type can be a, b or c. a=school, b=personal, c=fe
college.

The other table test-simple-costs has the costs in it, e.g. if 1118 is
selected and the type=c, this is for an 1118 personal subscription, if
1118 and type=d, this is for an FE college 1118 subscription.

The create field code in the simple-test-query is

RenewalCost: iif(
[1118],Switch(
[type]="a",[test-simple-costs].school_1118
,[type]="c",[test-simple-costs].personal_1118,
[type]="d",[test-simple-costs].fe_1118,
[1116],Switch(
[type]="a",[test-simple-costs].school_1116,
[type]="c",[test-simple-costs].personal_1116,
[type]="d",[test-simple-costs].fe_1116,
)))

How can I improve this setup?

Cheers

Geoff

 

[Geoff Cox]

The problem is that you've got fields named things like school_1118,
personal_1118 and fe_1118. What that does is hide information as the name of
the field!

Rather than a single row with field names like that, 1118 and "school"
should both be fields in the table, and you should have multiple rows:

SubscriptionNumber SubscriptionType SubscriptionFee
1116 school 85
1116 personal 50
1116 fe 100
1118 school 100
1118 personal 60

more food for thought!

thanks

Geoff

1118 fe 130
-
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

As soon as one condition is found that matches (in this case [1118] being
true), none of the rest of the conditions are evaluated.

Perhaps now you understand why I was saying that your design wasn't a
particularly good one!

Douglas,

It occurs to me that I'm not sure what to do re improving the design
of the table.

Remember I have created a simple table, called test-simple.

It only has the following fields

name (text)
1118 (yes/no)
1116 (yes/no)
type (text)

The value of type can be a, b or c. a=school, b=personal, c=fe
college.

The other table test-simple-costs has the costs in it, e.g. if 1118 is
selected and the type=c, this is for an 1118 personal subscription, if
1118 and type=d, this is for an FE college 1118 subscription.

The create field code in the simple-test-query is

RenewalCost: iif(
[1118],Switch(
[type]="a",[test-simple-costs].school_1118
,[type]="c",[test-simple-costs].personal_1118,
[type]="d",[test-simple-costs].fe_1118,
[1116],Switch(
[type]="a",[test-simple-costs].school_1116,
[type]="c",[test-simple-costs].personal_1116,
[type]="d",[test-simple-costs].fe_1116,
)))

How can I improve this setup?

Cheers

Geoff

 

[Geoff Cox]

The problem is that you've got fields named things like school_1118,
personal_1118 and fe_1118. What that does is hide information as the name of
the field!

Rather than a single row with field names like that, 1118 and "school"
should both be fields in the table, and you should have multiple rows:

SubscriptionNumber SubscriptionType SubscriptionFee
1116 school 85
1116 personal 50
1116 fe 100
1118 school 100
1118 personal 60
1118 fe 130

Douglas,

How do I address the SubscriptionFee where SubscriptionNumber is 1116
and the SubscriptionType is school?

I cannot use [SubscriptionFee] where [SubscriptionNumber] and
[SubscriptionType] - can I? What is to be used?

Cheers

Geoff

-
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

As soon as one condition is found that matches (in this case [1118] being
true), none of the rest of the conditions are evaluated.

Perhaps now you understand why I was saying that your design wasn't a
particularly good one!

Douglas,

It occurs to me that I'm not sure what to do re improving the design
of the table.

Remember I have created a simple table, called test-simple.

It only has the following fields

name (text)
1118 (yes/no)
1116 (yes/no)
type (text)

The value of type can be a, b or c. a=school, b=personal, c=fe
college.

The other table test-simple-costs has the costs in it, e.g. if 1118 is
selected and the type=c, this is for an 1118 personal subscription, if
1118 and type=d, this is for an FE college 1118 subscription.

The create field code in the simple-test-query is

RenewalCost: iif(
[1118],Switch(
[type]="a",[test-simple-costs].school_1118
,[type]="c",[test-simple-costs].personal_1118,
[type]="d",[test-simple-costs].fe_1118,
[1116],Switch(
[type]="a",[test-simple-costs].school_1116,
[type]="c",[test-simple-costs].personal_1116,
[type]="d",[test-simple-costs].fe_1116,
)))

How can I improve this setup?

Cheers

Geoff

 

[John W. Vinson]

Rather than a single row with field names like that, 1118 and "school"
should both be fields in the table, and you should have multiple rows:

SubscriptionNumber SubscriptionType SubscriptionFee
1116 school 85
1116 personal 50
1116 fe 100
1118 school 100
1118 personal 60
1118 fe 130

Douglas,

How do I address the SubscriptionFee where SubscriptionNumber is 1116
and the SubscriptionType is school?

I cannot use [SubscriptionFee] where [SubscriptionNumber] and
[SubscriptionType] - can I? What is to be used?

Of course you can. If you're assuming that a query can reference only one
field, you are mistaken!

You can join on up to TEN fields. Just join your table's SubscriptionNumber to
SubscriptionNumber, and Type to Type.

John W. Vinson [MVP]

 

[Geoff Cox]

Rather than a single row with field names like that, 1118 and "school"
should both be fields in the table, and you should have multiple rows:

SubscriptionNumber SubscriptionType SubscriptionFee
1116 school 85
1116 personal 50
1116 fe 100
1118 school 100
1118 personal 60
1118 fe 130

Douglas,

How do I address the SubscriptionFee where SubscriptionNumber is 1116
and the SubscriptionType is school?

I cannot use [SubscriptionFee] where [SubscriptionNumber] and
[SubscriptionType] - can I? What is to be used?

Of course you can. If you're assuming that a query can reference only one
field, you are mistaken!

You can join on up to TEN fields. Just join your table's SubscriptionNumber to
SubscriptionNumber, and Type to Type.

John W. Vinson [MVP]

Thanks John. I have done that, except that the field names are now
type (not SubscriptionNumber) and user (not SubscriptionType). In
user a="school", c="personal" and d="fe". Sorry to change horses
mid-stream but I am now trying to stick with the real text.

I have the following but I only get the "a" part for each IIF - I am
assuming that by joining the 2 fields the associated
[test-simple-costs].cost value is found? It is but only for the first
of each IIF.

Still missing something!

Cheers

Geoff

RenewalCost:
IIf([test-simple].type="1118",Switch(
[test-simple].user="a",[test-simple-costs].cost,
[test-simple].user="c",[test-simple-costs].cost,
[test-simple].user="d",[test-simple-costs].cost),
IIf([test-simple].type="1116",Switch(
[test-simple].user="a",[test-simple-costs].cost,
[test-simple].user="c",[test-simple-costs].cost,
[test-simple].user="d",[test-simple-costs].cost),
IIf([test-simple].type="1618",Switch(
[test-simple].user="a",[test-simple-costs].cost,
[test-simple].user="c",[test-simple-costs].cost,
[test-simple].user="d",[test-simple-costs].cost),
IIf([test-simple].type="1118plus",Switch(
[test-simple].user="a",[test-simple-costs].cost,
[test-simple].user="c",[test-simple-costs].cost,
[test-simple].user="d",[test-simple-costs].cost),
IIf([test-simple].type="1116plus",Switch(
[test-simple].user="a",[test-simple-costs].cost,
[test-simple].user="c",[test-simple-costs].cost,
[test-simple].user="d",[test-simple-costs].cost),0)))))

 

[Geoff Cox]

You can join on up to TEN fields. Just join your table's SubscriptionNumber to
SubscriptionNumber, and Type to Type.

John W. Vinson [MVP]

John (and Douglas),

Solved it!

Again I was putting too much info into a name. I had school1118,
personal1118 and fe1118 as entriesunder type in the test-simple-costs
table, should just have had 1118 with user=a, 1118 with user=c, and
1118 with user=d.

Many thanks for your help!

Cheers

Geoff

 

[Geoff Cox]

You can join on up to TEN fields. Just join your table's SubscriptionNumber to
SubscriptionNumber, and Type to Type.

John W. Vinson [MVP]

Of course I spoke too soon!

I have been able to get the create field to work on a simplified table
but it requires that thier is an exact correspondence between the type
and user field values in the 2 tables.

My "real" table has several hundreds of type entries and I don't want
to have to copy all these into the costs table.

is it possible to get this to work without doing this?

I need to be able to select, for example, a record where type=1118 and
user=a, and then get the related cost from the costs table.

Cheers

Geoff

 

[John W. Vinson]

I have been able to get the create field to work on a simplified table
but it requires that thier is an exact correspondence between the type
and user field values in the 2 tables.

Well... yes. That's how joins work. They join a value in one table to a
corresponding value in another table.

My "real" table has several hundreds of type entries and I don't want
to have to copy all these into the costs table.

is it possible to get this to work without doing this?

Not without SOME way to determine from the value of a "type entry" which
record in the costs table you mean.

I need to be able to select, for example, a record where type=1118 and
user=a, and then get the related cost from the costs table.

If the main table (you've never said what it is or what it means), then
joining on Type and on User will match a record in the main table with 1118
and a in those two fields to a record in the costs table with 1118 and a in
the costs record. Since I cannot see your database and you haven't said what's
in it, I was sort of assuming that was what you wanted. Evidently it isn't.

What IS in your main table? Fieldnames and sample contents please.

What IS in your costs table? ditto.

How would you determine which record in the Costs table pertains to an
arbitrary record in the main table? Please give a real example.

John W. Vinson [MVP]

 

[Geoff Cox]

How would you determine which record in the Costs table pertains to an
arbitrary record in the main table? Please give a real example.

John W. Vinson [MVP]

John,

The aim is to be able to create a query so that the subscription
renewal reminder with the cost of renewal can be sent to the people
concerned, using some mailshot software. Trying to make my life
easier!

This isn't a "real" example but it does show the essence of what I
would like to do.

Let's say that the main table, called test-table is

ID name type user date_start email_address
1 fred 1118 a 01/11/2006 (e-mail address removed)
2 jane 1118 b 05/11/2006 (e-mail address removed)
3 jim 1118 c 10/11/2006 (e-mail address removed)
4 jill 1116 d 15/11/2006 (e-mail address removed)

The related costs table, test-table-costs is

ID type user cost
1 1118 a 100
2 1118 b 400
3 1118 c 85
4 1118 d 130

The query would extract the name and cost associated with the
type/user combination in table-test and send this subscription
reminder (those where 12 months has elapsed since the start_date) to
the associated email address.

I now know how to do this by having both tables in the query and by
linking type and user fields, but would like to do it in a different
way so that I do not have to copy all several hundred records from the
real table to the real costs table.

Do hope this is clear!

Cheers

Gefoff

 

[John W. Vinson]

The aim is to be able to create a query so that the subscription
renewal reminder with the cost of renewal can be sent to the people
concerned, using some mailshot software. Trying to make my life
easier!

This isn't a "real" example but it does show the essence of what I
would like to do.

Let's say that the main table, called test-table is

ID name type user date_start email_address
1 fred 1118 a 01/11/2006 (e-mail address removed)
2 jane 1118 b 05/11/2006 (e-mail address removed)
3 jim 1118 c 10/11/2006 (e-mail address removed)
4 jill 1116 d 15/11/2006 (e-mail address removed)

The related costs table, test-table-costs is

ID type user cost
1 1118 a 100
2 1118 b 400
3 1118 c 85
4 1118 d 130

The query would extract the name and cost associated with the
type/user combination in table-test and send this subscription
reminder (those where 12 months has elapsed since the start_date) to
the associated email address.

I now know how to do this by having both tables in the query and by
linking type and user fields, but would like to do it in a different
way so that I do not have to copy all several hundred records from the
real table to the real costs table.

Do hope this is clear!

No. It's not.

Why would you need to copy ANY records to the costs table?

A query

SELECT [test-table].ID, [test-table].[name], [test-table].[type],
[test-table].[user], [test-table].[date_start],[test-table].[email_address],
[test-table-costs].[cost]
FROM [test-table] INNER JOIN [test-table-costs]
ON [test-table].[type] = [test-table-costs].[type]
AND [test-table].[user] = [test-table-costs].[user];

will join each record in test-table to the cost for that record in
test-table-costs.

If you mean that there is no record in [test-table-costs] for the combination
of (say) [type] = 1121 and user "d", then... how can Access possibly determine
what the cost for that combination should be? You need to be able to find the
cost *somewhere*. Maybe I'm misunderstanding the problem!

John W. Vinson [MVP]

 

[Geoff Cox]

The aim is to be able to create a query so that the subscription
renewal reminder with the cost of renewal can be sent to the people
concerned, using some mailshot software. Trying to make my life
easier!

This isn't a "real" example but it does show the essence of what I
would like to do.

Let's say that the main table, called test-table is

ID name type user date_start email_address
1 fred 1118 a 01/11/2006 (e-mail address removed)
2 jane 1118 b 05/11/2006 (e-mail address removed)
3 jim 1118 c 10/11/2006 (e-mail address removed)
4 jill 1116 d 15/11/2006 (e-mail address removed)

The related costs table, test-table-costs is

ID type user cost
1 1118 a 100
2 1118 b 400
3 1118 c 85
4 1118 d 130

The query would extract the name and cost associated with the
type/user combination in table-test and send this subscription
reminder (those where 12 months has elapsed since the start_date) to
the associated email address.

I now know how to do this by having both tables in the query and by
linking type and user fields, but would like to do it in a different
way so that I do not have to copy all several hundred records from the
real table to the real costs table.

Do hope this is clear!

No. It's not.

Why would you need to copy ANY records to the costs table?

A query

SELECT [test-table].ID, [test-table].[name], [test-table].[type],
[test-table].[user], [test-table].[date_start],[test-table].[email_address],
[test-table-costs].[cost]
FROM [test-table] INNER JOIN [test-table-costs]
ON [test-table].[type] = [test-table-costs].[type]
AND [test-table].[user] = [test-table-costs].[user];

will join each record in test-table to the cost for that record in
test-table-costs.

If you mean that there is no record in [test-table-costs] for the combination
of (say) [type] = 1121 and user "d", then... how can Access possibly determine
what the cost for that combination should be? You need to be able to find the
cost *somewhere*. Maybe I'm misunderstanding the problem!

John W. Vinson [MVP]

John,

I may just be starting to get an idea of what's going on! I put in you
SELECT code into the query and then looked at the design view and
realised that you had added the cost field from the test-table-costs
table and made the 2 inner joins.

I have been trying to create a field and have the 2 inner joins too!!

Talk about going off at a tangent!

Many thanks!

Cheers

Geoff

 

[John W. Vinson]

John,

I may just be starting to get an idea of what's going on! I put in you
SELECT code into the query and then looked at the design view and
realised that you had added the cost field from the test-table-costs
table and made the 2 inner joins.

Good! Should have suggested going back to the design grid: the SQL is the
"real" query, and the grid a tool to create SQL; but it's a familiar and very
useful tool, so it helps to go back and forth.

You can use up to *TEN* join lines, though I'd rarely use more than two or
three. I'm remembering a case where I did use five.

I have been trying to create a field and have the 2 inner joins too!!

Just note that there should NOT be a Cost field in your "test" table, at all.
Just use a query to look up the cost when it's needed.

A couple of other suggestions:

Name, Date, Time, and some other words are reserved words in Access. It's best
NOT to use them as fieldnames.

I've had occasional trouble when using hyphens in object names. It's not
strictly necessary, but I'd suggest limiting field and tablenames to the
characters A-Z, 0-9, and underscore (capitalization can be freely mixed and
I'll use camel case names such as TestData or TestCosts).

John W. Vinson [MVP]

 

[Geoff Cox]

Good! Should have suggested going back to the design grid: the SQL is the
"real" query, and the grid a tool to create SQL; but it's a familiar and very
useful tool, so it helps to go back and forth.

You can use up to *TEN* join lines, though I'd rarely use more than two or
three. I'm remembering a case where I did use five.


Just note that there should NOT be a Cost field in your "test" table, at all.
Just use a query to look up the cost when it's needed.

A couple of other suggestions:

Name, Date, Time, and some other words are reserved words in Access. It's best
NOT to use them as fieldnames.

I've had occasional trouble when using hyphens in object names. It's not
strictly necessary, but I'd suggest limiting field and tablenames to the

John,

The mailing software which I mentioned is called Gammadyne and seems
to work really wel and it was this that objected to a name like
address-1, so I have removed - and used _ .

Cheers

Geoff