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