Prev: Rounding Time
Next: Crunch numbers with MS Access: is this possible?
From: KenSheridan via AccessMonster.com on 24 May 2010 08:58
The combo box needs to be bound to the Supplier Number column in the
subform's underlying table or query. But if you want a text box then you can
either do as I originally advised and as the subform's RecordSource property
use a query which joins the Suppliers table to the Customers table and bind
the text box to the Supplier Name column, or use an unbound text box whose
ControlSource is an expression which calls the DLookup function:

=DLookup("[Supplier Name]", "[Suppliers]", "[Supplier Number] = " & [Supplier
Number 1])

or if Supplier nuber is a text data type rather than a number;

=DLookup("[Supplier Name]", "[Suppliers]", "[Supplier Number] = """ &
[Supplier Number 1] & """")

I've called the column in the subform's underlying table Supplier Number 1 in
this instance as you said you have three columns each referencing the
Suppliers table, so you'd do the same with each. This also means that if you
do use a query to pull in the supplier names the joins would probably have to
be LEFT OUTER JOINS to allow for a Null in any of the three columns.

The fact that you have three columns referencing Suppliers does suggest
however that the logical model is flawed. What you seem to be doing here is
representing different attributes of the relationship type (the type of
service provided in your case) between Customers and Suppliers as column
headings in Customers. Attributes are data, and data should only be stored
as explicit values at column positions in rows in tables. This was Codd's
Rule 1 (the Information Rule) when he first proposed the database relational
model back in 1970. The way to model this relationship would be as a table
with foreign key columns referencing the primary keys of Customers and
Suppliers, and a third column representing the nature of the realtionship.
This third column would also be a foreign key, in this case referencing a
table of the different types of relationship (i.e. the services) between
customers suppliers. The relationship type is therefore a ternary one
between the three entity types. So a customer with all three types of
relationship to suppliers would be represented as three rows in this table,
each with the same customer number, different values in the service type
column and either the same or different values in the supplier depending on
whether the services to that customer are provided by the same or different
suppliers. If a customer cannot receive the same service from more than one
supplier at any one point in time this should be enforced by means of a
unique index on the Supplier number and service type columns, in combination,
not individually.

Ken Sheridan
Stafford, England

--
Message posted via http://www.accessmonster.com

From: Ray C on 24 May 2010 12:16

Yipppppeeee !!! Great. Smashin. Fabulous. Magic our Mourice. Tha Reet Up our
Street kid.

Seriously Ken, Thank you so much, I would never have got there without your
invaluable help. The Text Box with the DLookUp has done the trick and I will
spent the next few days reading the whole content of your post and trying to
work out why they did not work for me.

I don't know what we are going to do now that Microsoft look as though they
are closing this forum.

Many thanks anyway.

Regards Ray C


"KenSheridan via AccessMonster.com" wrote:

> The combo box needs to be bound to the Supplier Number column in the
> subform's underlying table or query. But if you want a text box then you can
> either do as I originally advised and as the subform's RecordSource property
> use a query which joins the Suppliers table to the Customers table and bind
> the text box to the Supplier Name column, or use an unbound text box whose
> ControlSource is an expression which calls the DLookup function:
>
> =DLookup("[Supplier Name]", "[Suppliers]", "[Supplier Number] = " & [Supplier
> Number 1])
>
> or if Supplier nuber is a text data type rather than a number;
>
> =DLookup("[Supplier Name]", "[Suppliers]", "[Supplier Number] = """ &
> [Supplier Number 1] & """")
>
> I've called the column in the subform's underlying table Supplier Number 1 in
> this instance as you said you have three columns each referencing the
> Suppliers table, so you'd do the same with each. This also means that if you
> do use a query to pull in the supplier names the joins would probably have to
> be LEFT OUTER JOINS to allow for a Null in any of the three columns.
>
> The fact that you have three columns referencing Suppliers does suggest
> however that the logical model is flawed. What you seem to be doing here is
> representing different attributes of the relationship type (the type of
> service provided in your case) between Customers and Suppliers as column
> headings in Customers. Attributes are data, and data should only be stored
> as explicit values at column positions in rows in tables. This was Codd's
> Rule 1 (the Information Rule) when he first proposed the database relational
> model back in 1970. The way to model this relationship would be as a table
> with foreign key columns referencing the primary keys of Customers and
> Suppliers, and a third column representing the nature of the realtionship.
> This third column would also be a foreign key, in this case referencing a
> table of the different types of relationship (i.e. the services) between
> customers suppliers. The relationship type is therefore a ternary one
> between the three entity types. So a customer with all three types of
> relationship to suppliers would be represented as three rows in this table,
> each with the same customer number, different values in the service type
> column and either the same or different values in the supplier depending on
> whether the services to that customer are provided by the same or different
> suppliers. If a customer cannot receive the same service from more than one
> supplier at any one point in time this should be enforced by means of a
> unique index on the Supplier number and service type columns, in combination,
> not individually.
>
> Ken Sheridan
> Stafford, England
>
> --
> Message posted via http://www.accessmonster.com
>
> .
>
From: Larry Linson on 24 May 2010 15:36
"Ray C" <RayC(a)discussions.microsoft.com> wrote

> I don't know what we are going to do now
> that Microsoft look as though they
> are closing this forum.

Actually, Microsoft has officially announced that this newsgroup will close
as of 6/1/2010, so it's more than "look as though". If you have a news
server, or are willing to sign up for one (there are many, both fee and
free), the USENET newsgroup comp.databases.ms-access still exists and many
MVPs and others who provide excellent answers have indicated they will be
returning there.

Microsoft is providing some online forums that include some "answerers" who
are paid and also accept answers from others (including MVPs). So far, there
has been no groundswell of acceptance for those, though some find them
usable. Access is lumped in with some other software in one of the sections
of social.answers.microsoft.com -- nothing like the number of newsgroups has
been discussed, AFAIK.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


From: Aria via AccessMonster.com on 24 May 2010 16:37
Well, maybe I shouldn't jump in here but I've been concerned ever since I
read the posts regarding the termination of these newsgroups. I'm really sad
that they feel the need to do this. When I think back to how much help I've
received, I know the creation of our dbs would not have happened otherwise. I
guess timing is everything.

I've been to the forum they suggest and hope they change it. I'm not so sure
grouping everything together is the best idea. I'm still trying to learn
Access and I like having the different sections. When one posts in the New
User section, the expectation is that you are a novice or completely new to
Access. I don't know anything about Usenet or how to use it so I don't quite
know what I will do after this is gone.


Larry Linson wrote:
> > I don't know what we are going to do now
> > that Microsoft look as though they
> > are closing this forum.
>
>Actually, Microsoft has officially announced that this newsgroup will close
>as of 6/1/2010, so it's more than "look as though". If you have a news
>server, or are willing to sign up for one (there are many, both fee and
>free), the USENET newsgroup comp.databases.ms-access still exists and many
>MVPs and others who provide excellent answers have indicated they will be
>returning there.
>
>Microsoft is providing some online forums that include some "answerers" who
>are paid and also accept answers from others (including MVPs). So far, there
>has been no groundswell of acceptance for those, though some find them
>usable. Access is lumped in with some other software in one of the sections
>of social.answers.microsoft.com -- nothing like the number of newsgroups has
>been discussed, AFAIK.
>

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1

From: KenSheridan via AccessMonster.com on 24 May 2010 19:37
Aria:

You don't need to use a newsreader if you prefer a web-based interface as
there are a number of the latter which will enable you to access the comp.
databases.ms-access newsgroup to which Larry referred. I've only just
realised that this is what the 'General 1' section does in AccessMonster,
which I'm using at the moment to access this group. Its other sections
access this newsgroup. I'm assuming that it will continue to operate after
this newsgroup is consigned to the dustbin of history. You can find it at:

http://www.accessmonster.com

Its free to use and setting up an account is only a few minutes work.

Google Groups is another web-based option which seems to be quite popular,
though I'm not too enamoured of it myself. You'll find the comp.databases.ms-
access group at:

http://groups.google.com/group/comp.databases.ms-access/topics

I'm sure you'll find most of the names you've become familiar with here at
comp.databases.ms-access in the near future if not already, and there will be
no diminution in the level of help you'll receive. From the evidence to date
the new MS forums don't look promising.

Ken Sheridan
Stafford, England

Aria wrote:
>Well, maybe I shouldn't jump in here but I've been concerned ever since I
>read the posts regarding the termination of these newsgroups. I'm really sad
>that they feel the need to do this. When I think back to how much help I've
>received, I know the creation of our dbs would not have happened otherwise. I
>guess timing is everything.
>
>I've been to the forum they suggest and hope they change it. I'm not so sure
>grouping everything together is the best idea. I'm still trying to learn
>Access and I like having the different sections. When one posts in the New
>User section, the expectation is that you are a novice or completely new to
>Access. I don't know anything about Usenet or how to use it so I don't quite
>know what I will do after this is gone.
>
>> > I don't know what we are going to do now
>> > that Microsoft look as though they
>[quoted text clipped - 13 lines]
>>of social.answers.microsoft.com -- nothing like the number of newsgroups has
>>been discussed, AFAIK.

--
Message posted via http://www.accessmonster.com

First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Rounding Time
Next: Crunch numbers with MS Access: is this possible?