Relationships, Lookups and Access 2007 tables

[netnewbie78]

Hello All,

I don't have a problem (but maybe I will after I explain). I have a
question with regards to something I saw in Access 2007. But first, a
little backstory:

I'm writing a very small stock database. For now, it will simply track
what products come in (Stocks bought by Project) and what products go
out (Stocks sold to by Project) . There are three tables: Products,
Transactions and Projects.

I've used the lookup wizard to 'create' three fields in the
Transaction table: ProjectST, ProjectBF (Project Sold To, Project
Bought For) and Products.

When a user enters an 'IN' transaction (directly via the Transaction
table), they enter, among other things, the date of the transaction,
and the Product which is chosen via the lookup to the Products table.
While this displays the full Product detail, it only stores the ProdID
(which is the PK of the Products table) value in the 'Products' field
of the Transaction table .

(Note: after this design, I looked at the Relationships Manager and
saw a 1:N relation between the Products table and the Transaction
table, i.e., one product can be used in many transactions, or, there
can be many transactions, but each can only have one product).

Besides the date of the trans and the product concerned, users can
also enter, depending on the type of transaction, a ProjectBF and
ProjectST. In the above example, an IN, the user would choose via the
same sort of lookup, a project from the Projects table. This would be
stored in the ProjectBF field (created in the Transaction table via
the aforementioned lookupcolumn). The ProjectST field would remain
empty.

However, in an OUT transaction, the user would choose the project from
ProjectBF first, and then choose the project that it was sold to via a
lookup (again to the projects table, in other words, two lookups to
the Projects table), this would be stored in the ProjectST field of
the Transaction table.

This is necessary because there are cases when stock bought for
Project A is sent to Project B, but a transaction of stock out must
record that it was bought for A and sold to B.

So I looked at the Relationship window again and saw the two (both of
them 1:N) relations from Transactions to the Projects table and now a
new 'table' called Projects_1. ProjBF in the Transaction table goes to
ProjCode (PK) in Projects and ProjST in Transaction goes to ProjCode
(PK) in something new called Projects_1 (which I gather is what Access
created automatically).

Is this normal, i.e., for a second 'table' or 'ghost table' (LOL) to
be created?

Or should I have just created an identical table, i.e., ProjectsBF and
ProjectsST and used lookups in the Transaction table to each
respective Projects table?

Any thoughts/comments?

Rgds,
NN.

P.S, I know there will be some 'fun and games' if and when referential
integrity comes into play, i.e., if someone deletes a project and
integrity and cascading are enabled.

[lyle fairfield]

On Sep 25, 9:38*am, [e-mail] wrote:

Hello All,

I don't have a problem (but maybe I will after I explain). I have a
question with regards to something I saw in Access 2007. But first, a
little backstory:

I'm writing a very small stock database. For now, it will simply track
what products come in (Stocks bought by Project) and what products go
out (Stocks sold to by Project) . There are three tables: Products,
Transactions and Projects.

I've used the lookup wizard to 'create' three fields in the
Transaction table: ProjectST, ProjectBF (Project Sold To, Project
Bought For) and Products.

When a user enters an 'IN' transaction (directly via the Transaction
table), they enter, among other things, the date of the transaction,
and the Product which is chosen via the lookup to the Products table.
While this displays the full Product detail, it only stores the ProdID
(which is the PK of the Products table) value in the 'Products' field
of the Transaction table .

(Note: after this design, I looked at the Relationships Manager and
saw a 1:N relation between the Products table and the Transaction
table, i.e., one product can be used in many transactions, or, there
can be many transactions, but each can only have one product).

Besides the date of the trans and the product concerned, users can
also enter, depending on the type of transaction, a ProjectBF and
ProjectST. In the above example, an IN, the user would choose via the
same sort of lookup, a project from the Projects table. This would be
stored in the ProjectBF field (created in the Transaction table via
the aforementioned lookupcolumn). The ProjectST field would remain
empty.

However, in an OUT transaction, the user would choose the project from
ProjectBF first, and then choose the project that it was sold to via a
lookup (again to the projects table, in other words, two lookups to
the Projects table), this would be stored in the ProjectST field of
the Transaction table.

This is necessary because there are cases when stock bought for
Project A is sent to Project B, but a transaction of stock out must
record that it was bought for A and sold to B.

So I looked at the Relationship window again and saw the two (both of
them 1:N) relations from Transactions to the Projects table and now a
new 'table' called Projects_1. ProjBF in the Transaction table goes to
ProjCode (PK) in Projects and ProjST in Transaction goes to ProjCode
(PK) in something new called Projects_1 (which I gather is what Access
created automatically).

Is this normal, i.e., for a second 'table' or 'ghost table' (LOL) to
be created?

Or should I have just created an identical table, i.e., ProjectsBF and
ProjectsST and used lookups in the Transaction table to each
respective Projects table?

Any thoughts/comments?

Rgds,
NN.

P.S, I know there will be some 'fun and games' if and when referential
integrity comes into play, i.e., if someone deletes a project and
integrity and cascading are enabled.

It's unlikely that there is a new table. Projects_1 is likely to be an
Alias for Projects; the Alias identifies which instance of the table
is being referenced.

You might want to glance at
www.mvps.org/access/tencommandments.htm
before you continue with "Lookup" tables.

[netnewbie78]

On Sep 25, 10:42*am, lyle fairfield <[e-mail]:

On Sep 25, 9:38*am, [e-mail] wrote:

Hello All,

I don't have a problem (but maybe I will after I explain). I have a
question with regards to something I saw in Access 2007. But first, a
little backstory:

I'm writing a very small stock database. For now, it will simply track
what products come in (Stocks bought by Project) and what products go
out (Stocks sold to by Project) . There are three tables: Products,
Transactions and Projects.

I've used the lookup wizard to 'create' three fields in the
Transaction table: ProjectST, ProjectBF (Project Sold To, Project
Bought For) and Products.

When a user enters an 'IN' transaction (directly via the Transaction
table), they enter, among other things, the date of the transaction,
and the Product which is chosen via the lookup to the Products table.
While this displays the full Product detail, it only stores the ProdID
(which is the PK of the Products table) value in the 'Products' field
of the Transaction table .

(Note: after this design, I looked at the Relationships Manager and
saw a 1:N relation between the Products table and the Transaction
table, i.e., one product can be used in many transactions, or, there
can be many transactions, but each can only have one product).

Besides the date of the trans and the product concerned, users can
also enter, depending on the type of transaction, a ProjectBF and
ProjectST. In the above example, an IN, the user would choose via the
same sort of lookup, a project from the Projects table. This would be
stored in the ProjectBF field (created in the Transaction table via
the aforementioned lookupcolumn). The ProjectST field would remain
empty.

However, in an OUT transaction, the user would choose the project from
ProjectBF first, and then choose the project that it was sold to via a
lookup (again to the projects table, in other words, two lookups to
the Projects table), this would be stored in the ProjectST field of
the Transaction table.

This is necessary because there are cases when stock bought for
Project A is sent to Project B, but a transaction of stock out must
record that it was bought for A and sold to B.

So I looked at the Relationship window again and saw the two (both of
them 1:N) relations from Transactions to the Projects table and now a
new 'table' called Projects_1. ProjBF in the Transaction table goes to
ProjCode (PK) in Projects and ProjST in Transaction goes to ProjCode
(PK) in something new called Projects_1 (which I gather is what Access
created automatically).

Is this normal, i.e., for a second 'table' or 'ghost table' (LOL) to
be created?

Or should I have just created an identical table, i.e., ProjectsBF and
ProjectsST and used lookups in the Transaction table to each
respective Projects table?

Any thoughts/comments?

Rgds,
NN.

P.S, I know there will be some 'fun and games' if and when referential
integrity comes into play, i.e., if someone deletes a project and
integrity and cascading are enabled.

It's unlikely that there is a new table. Projects_1 is likely to be an
Alias for Projects; the Alias identifies which instance of the table
is being referenced.

You might want to glance atwww.mvps.org/access/tencommandments.htm
before you continue with "Lookup" tables.- Hide quoted text -

- Show quoted text -

Hi,

Thanks for your reply/explanation, I was a bit puzzled. Re: the 10
commandments/lookup bit, I've read about them before. However, based
on my scenario above, do you (or anyone else out there), have any
suggestions/recommendations to get around it?

Rgds,
NN.

[lyle fairfield]

Hi,

>
Thanks for your reply/explanation, I was a bit puzzled. Re: the 10
commandments/lookup bit, I've read about them before. However, based
on my scenario above, do you (or anyone else out there), have any
suggestions/recommendations to get around it?

Of course we "normalize". We use candidate tables. We establish
relationships. We enforce referential integrity. But these things are
extraneous to the intrinsic organization of any table. We use our
relationships in forms and subforms and reports. We are in control. MS-
Access does not do what it "thinks" should be done. It does what we
tell it to do.

[netnewbie78]

On Sep 25, 1:55*pm, lyle fairfield <[e-mail]:

Hi,

Thanks for your reply/explanation, I was a bit puzzled. Re: the 10
commandments/lookup bit, I've read about them before. However, based
on my scenario above, do you (or anyone else out there), have any
suggestions/recommendations to get around it?

Of course we "normalize". We use candidate tables. We establish
relationships. We enforce referential integrity. But these things are
extraneous to the intrinsic organization of any table. We use our
relationships in forms and subforms and reports. We are in control. MS-
Access does not do what it "thinks" should be done. It does what we
tell it to do.

Hi again,

1. What is meant by candidate tables and what do you mean when you say
you use the relationships in forms? Relatations are created between
tables, no? Tables are at the 'lowest level' so to speak, i.e., forms
'sit on top' of tables (of course the hierarchy is files, records,
fields, or something like that if I recall correctly).

2. Are you saying that I can have a form for my transaction table
(whichi is displayed in datasheet view as it is quicker to tab across
for data entry purposes), which has a control (a lookup/combo) to my
Products table which will let the user choose a product? (without the
use of lookup columns/fields which the 10 commandments warn against).
Is this not the same, forgive me, then as using the Lookup Wiz and
letting access create a field in the Transaction table which looks up
the Product table directly (minus the need for the form for the
Transaction table)?

Or have I missed the boat all together?

Thanks,
NN.