creating new db

[stephendeloach via AccessMonster.com]

i have created a new db with a tblMain and tblDescription. Here is how it is
layed out..
tblMain

ID
OP
LN
Rig
Parish
Vend
SD
TD

tblDescription

ID
IN
Cate
Qty
UP
Desc
Total

What i am wanting to do is enter info into tblMain and then line items in
tblDescription that are related to the info in tblMain. I guess my question
would be What would my relationship be with the 2 tables? Thanks

 

[Guest]

Assuming tblMain has a one-to-many relationship with tblDescription, your
best bet is to create form based on tblMain, and insert into that form a
subform based on tblDescription. You can then join the forms on the common
field (ID?) and you will then be able to enter multiple lines for each line
in tblMain.

 

[John W. Vinson]

i have created a new db with a tblMain and tblDescription. Here is how it is
layed out..
tblMain

ID
OP
LN
Rig
Parish
Vend
SD
TD

Could you give us some clue as to what these cryptic names might mean? Perhaps
it would help.

tblDescription

ID
IN
Cate
Qty
UP
Desc
Total

What i am wanting to do is enter info into tblMain and then line items in
tblDescription that are related to the info in tblMain. I guess my question
would be What would my relationship be with the 2 tables? Thanks

What is the "description" describing? What real-life Entity is represented by
tblMain? by tblDescription? Does one record in tblMain correspond to (zero or
one) tblDescription records, or to (zero, one or more) tblDescription records?
Does every tblDescription record relate to one and only one tblMain record, or
might it apply to many?

John W. Vinson [MVP]

 

[Guest]

That depends on how your table are designed. It's difficult to tell from your
field descriptions exactly what data they contain.

In a typical one-to-many relationship, you would have a master record with a
unique ID, and then in the detail table, several items that relate to that
id.

You would create a subform based on the detail table, and when you drag it
into your master form, use the Data tab on the subform to link Master/Child
fields. You'd pick the common field in both tables, probably ID in your
description.

Then the subform will display detail items for whichever master record is
displayed or highlighted on the master form.

 

[stephendeloach via AccessMonster.com]

Thanks for the replies. Here is what the fields represent..

tblMain
ID - ID (primary key)
Op - Operator
LN - LeaseName
Rig - RigName
Parish - Parish
Vend - Vendor
SD - SpudDate (when the job starts)
TD - TDate (when the job is finished)

tblDescription

ID - ID primary key
IN - Invoice#
Cate - Category
Qty - Quantity
UP - UnitPrice
Desc - Description
Total - Total for that invoice

What we have are invoices coming into the office that will need to be put
into this db. We have multiple invoices that have the same information
(tblMain) and every line item on these invoice will need to go into
tblDescription. So i would need to have the tables linkd by ID - ID? Hope I
answered some of the questions you guys asked.. Thanks

i have created a new db with a tblMain and tblDescription. Here is how it is
layed out..

[quoted text clipped - 8 lines]

SD
TD

Could you give us some clue as to what these cryptic names might mean? Perhaps
it would help.

tblDescription

[quoted text clipped - 9 lines]

tblDescription that are related to the info in tblMain. I guess my question
would be What would my relationship be with the 2 tables? Thanks

What is the "description" describing? What real-life Entity is represented by
tblMain? by tblDescription? Does one record in tblMain correspond to (zero or
one) tblDescription records, or to (zero, one or more) tblDescription records?
Does every tblDescription record relate to one and only one tblMain record, or
might it apply to many?

John W. Vinson [MVP]

 

[Guest]

Based on what you're describing, it sounds like tblMain should hold the
master invoice data. You might want to rename it tblInvoiceHeader or
something similar. This would hold al the header information for each invoice.

tblDecription (which you might want to rename tblInvoiceDetail) would hold
all the line items for each individual invoice stored in tblInvoiceHeader.
Invoice # should be in tblInvoiceHeader, and the items in tblINvoiceDeatail
should refer to the INvoice# to which they apply.

Also, I'd recommend against using a Total field in the detail table. The
total for each invoice should be a calculated field based on the sum of Qty x
Price for each line item for each invoice. If you store it in a Total field
you have to remember to update that field each time a quantity or unit price
changes. A better way is to have a query calculate the Total value
automatically so it is always up to date.

I would also recommend renaming your columns to more descriptive and
readable names.

Thanks for the replies. Here is what the fields represent..

tblMain
ID - ID (primary key)
Op - Operator
LN - LeaseName
Rig - RigName
Parish - Parish
Vend - Vendor
SD - SpudDate (when the job starts)
TD - TDate (when the job is finished)

tblDescription

ID - ID primary key
IN - Invoice#
Cate - Category
Qty - Quantity
UP - UnitPrice
Desc - Description
Total - Total for that invoice

What we have are invoices coming into the office that will need to be put
into this db. We have multiple invoices that have the same information
(tblMain) and every line item on these invoice will need to go into
tblDescription. So i would need to have the tables linkd by ID - ID? Hope I
answered some of the questions you guys asked.. Thanks

i have created a new db with a tblMain and tblDescription. Here is how it is
layed out..

[quoted text clipped - 8 lines]

SD
TD

Could you give us some clue as to what these cryptic names might mean? Perhaps
it would help.

tblDescription

[quoted text clipped - 9 lines]

tblDescription that are related to the info in tblMain. I guess my question
would be What would my relationship be with the 2 tables? Thanks

What is the "description" describing? What real-life Entity is represented by
tblMain? by tblDescription? Does one record in tblMain correspond to (zero or
one) tblDescription records, or to (zero, one or more) tblDescription records?
Does every tblDescription record relate to one and only one tblMain record, or
might it apply to many?

John W. Vinson [MVP]

 

[John W. Vinson]

What we have are invoices coming into the office that will need to be put
into this db. We have multiple invoices that have the same information
(tblMain) and every line item on these invoice will need to go into
tblDescription. So i would need to have the tables linkd by ID - ID? Hope I
answered some of the questions you guys asked.. Thanks

I'd agree with everything mray suggested.

BUT... I'd also have *two* fields rather than just ID. The Primary Key of
tblMain should be ID (or perhaps InvoiceID for clarity; fieldnames should be
reasonably short but not so short as to be obscure!). The Description table
should have its own primary key (DescriptionID perhaps), and *ALSO* an
InvoiceID field of the same datatype as tblMain.ID - Long Integer if that
field is an Autonumber. It on this "foreign key" field that you want to make
the link; don't link the primary keys to each other.

John W. Vinson [MVP]