Subform question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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.


Jeff Boyce said:
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 said:
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.
Click to expand...
Click to expand...
 
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

Nancy said:
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.


Jeff Boyce said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top