Access XP database

[mellowyellow]

I am creating a database that will produce a sales contract in the
construction biz. It will have two tables: one contains the line items and
the other the customer information and static contractural information. I
need to link the two tables so that I can produce a query from which I will
produce a report. 3 questions: 1). What kind of field would I need to
create to act as primary key, which leads to question 2). What kind of
relationship do I need: one to many, etc and 3). what kind of join would
I need to make this work?.

The table with line items has a "Yes/No" field where I can choose the
apppropriate line items (that is how I will narrow the line items for the
query and the same with the Customer Info table, where I can choose the right
customer info.

Thanks for your help

 

[Ivan Grozney]

So one contract can have several line items and on line item can belong to
more than one contract? If so, then your relationship is many to many.

If you are using Access 2007 there is a data type you can use, which escapes
me at the moment, for this. Or you can create the linking table between the
two tables for your many to many relationship.

Once that is done and you have the proper relationships set up, then you can
make a report off the customer information and a subreport that will show all
the line items.

hth

Vanya

 

[Larry Linson]

I am creating a database that will produce a sales contract in the
construction biz. It will have two tables: one contains the line items
and
the other the customer information and static contractural information. I
need to link the two tables so that I can produce a query from which I
will
produce a report. 3 questions: 1). What kind of field would I need to
create to act as primary key, which leads to question 2). What kind of
relationship do I need: one to many, etc and 3). what kind of join
would
I need to make this work?.

The table with line items has a "Yes/No" field where I can choose the
apppropriate line items (that is how I will narrow the line items for the
query and the same with the Customer Info table, where I can choose the
right
customer info.

Thanks for your help

 

[Larry Linson]

I am creating a database that will produce a sales contract in the
construction biz. It will have two tables: one contains the line items
and
the other the customer information and static contractural information. I
need to link the two tables so that I can produce a query from which I
will
produce a report. 3 questions: 1). What kind of field would I need to
create to act as primary key, which leads to question 2). What kind of
relationship do I need: one to many, etc and 3). what kind of join
would
I need to make this work?.

I suggest, in the long run, you will be happier if you link the contract
information to customer information in a separate table... presumably, you
may have one customer with multiple contracts. There is an excellent
example of using this type of data in the Northwind example database for
Orders and OrderDetails.

(1) There is ongoing debate about "natural" and "surrogate" keys. I tend to
use an Autonumber as a surrogate key for convenience in joining related
data, if the natural key is more than one field... the corresponding foreign
key in the related table is a long integer.

(2) Perhaps I am missing something, but it would seem obvious to me that you
would have one _or more_ line items for each contract so the relationship
would be one (contract) to many (line items) -- which implies that the line
item table has a foreign key to the contract table.

(3) Assuming you are going to select one specific contract and that there
will always be some line items, you'd specify a join property of "only where
the joined fields are equal in both tables" (the SQL will read "INNER
JOIN"). If you want a summary report, and there are some contracts that you
want to appear but which may not have any line items, use the option
"Include ALL records from Contracts and only those from LineItems where the
joined fields are equal" (this SQL will read "Contracts LEFT JOIN

The table with line items has a "Yes/No" field where I can choose the
apppropriate line items (that is how I will narrow the line items for the
query and the same with the Customer Info table, where I can choose the right
customer info.

These fields will apply in the Criteria line in the Query Builder, and in
the WHERE clause of the SQL.

Larry Linson
Microsoft Access MVP