Subform question

[Nancy]

I have a field PO from the PO_TBL. An example of the PO field is 123456.
I have a field PO_ITEM from the ENV_TBL. An example of the PO_ITEM field is
123456 001 (1st 6 is PO number, last 3 are the item number).

In the main part of the form, the user will select the PO from a drop down.

The customer wants a subform where table=ENV_TBL and the 1st 6 characters of
the PO_ITEM field = the PO from the PO_TBL.

I attempted a query but am having problems setting up the relationship. Any
and all help on this is appricated.

Thanks in advance.

 

[Jeff Boyce]

Nancy

One of the tenants of good data design is "one fact, one field" Your
[PO_ITEM] field holds two facts. The first points to a primary key in
another table ... this is called a "foreign key". The second is a
sequence/item number. It needs its own field.

You can always put them together for display purposes in a query.

You will find that Access has several built-in features that expect data to
be organized as I described. One of those is that you can use the
Relationships window to show that your tables are related one-to-many ...
but first you need to use "one fact, one field"!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Nancy]

Jeff -

I truely believe in the on fact, on field, however this is not something
designed but inherited. My customer does not want me to change any of their
data/fields, hence my delima. I was looking to see if there was anyway I
could work around this flaw in their design to get them through.

Nancy

One of the tenants of good data design is "one fact, one field" Your
[PO_ITEM] field holds two facts. The first points to a primary key in
another table ... this is called a "foreign key". The second is a
sequence/item number. It needs its own field.

You can always put them together for display purposes in a query.

You will find that Access has several built-in features that expect data to
be organized as I described. One of those is that you can use the
Relationships window to show that your tables are related one-to-many ...
but first you need to use "one fact, one field"!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a field PO from the PO_TBL. An example of the PO field is 123456.
I have a field PO_ITEM from the ENV_TBL. An example of the PO_ITEM field
is
123456 001 (1st 6 is PO number, last 3 are the item number).

In the main part of the form, the user will select the PO from a drop
down.

The customer wants a subform where table=ENV_TBL and the 1st 6 characters
of
the PO_ITEM field = the PO from the PO_TBL.

I attempted a query but am having problems setting up the relationship.
Any
and all help on this is appricated.

Thanks in advance.

 

[Jeff Boyce]

Nancy

Create a query in which you use Left() and Mid() functions to "grab" the
pieces. This gives you a way to work with the separate facts.

Point out to your customer that main form/subform design in Access relies on
well-normalized data ... and 12345 001 is NOT!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff -

I truely believe in the on fact, on field, however this is not something
designed but inherited. My customer does not want me to change any of
their
data/fields, hence my delima. I was looking to see if there was anyway I
could work around this flaw in their design to get them through.

Nancy

One of the tenants of good data design is "one fact, one field" Your
[PO_ITEM] field holds two facts. The first points to a primary key in
another table ... this is called a "foreign key". The second is a
sequence/item number. It needs its own field.

You can always put them together for display purposes in a query.

You will find that Access has several built-in features that expect data
to
be organized as I described. One of those is that you can use the
Relationships window to show that your tables are related one-to-many ...
but first you need to use "one fact, one field"!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a field PO from the PO_TBL. An example of the PO field is
123456.
I have a field PO_ITEM from the ENV_TBL. An example of the PO_ITEM
field
is
123456 001 (1st 6 is PO number, last 3 are the item number).

In the main part of the form, the user will select the PO from a drop
down.

The customer wants a subform where table=ENV_TBL and the 1st 6
characters
of
the PO_ITEM field = the PO from the PO_TBL.

I attempted a query but am having problems setting up the relationship.
Any
and all help on this is appricated.

Thanks in advance.