Hi guys. VERY new to access, so please excuse my ignorance

OK: here is my (proposed) database:

I want to create a quoting system (as the subject suggests) and im not really sure about the relationships that need to be in place. Here is my current table structure:

Table: Product
Product Code <-- (pr k)
Description
Cost Price (ex tax)
Quantity
Sell Price (inc tax)

Table: Status
Status ID <--(pr k)
Status < -- this is the "status" of the quote ie: 'in progress', 'accepted', 'not accepted' etc..

Table: Quote
Quote ID <-- (pr k)
Created < --date created, auto generated ("=now()"??) will this work?
Expires < -- quote expiry date ("=now()+14"??) will this work?
Total (ex)
Total (inc)
TAX
Profit (ex)

Table: Salesman
SalesmanID <-- (pr k)
Salesman

Table: Company
Company ID <-- (pr k)
Company Name
Contact
phone number
fax number
street address
suburb
post code

OK, first some basic questions

1. Is this table structure ok? SHould I add/remove any data from it?

2. Where exactly should my relationships be? im totally lost with this part

3. ANY other suggestions with this would be appreciated

Thanks in advance!

Trav

The tables look fine to me. But I might be wrong without the knowledge of your business.

The relationships completely depend on your business. Maybe think about it in another way - Can all your data be represented with all these tables?

For example, does a quote have any connections with companies? Say you could give a company a quote, then the table "quote" is related with "company". Then you may need to know if a quote is dependant on a company, if yes, you may need to insert a company id in the "quote" table to represent the relationship. But if quotes are independent of companies, in other words, a quote can be used on many companies, then you need a relationship table which consists of quote id and company id. Probably it involves a salesman and need SalesmanID too in this table.

For a beginner, you came up with a pretty good schema!

I would do this somewhat differently (assumes taxes are always applied at a constant rate, and that there are no quantity discounts):

Table: Product - your basic product catalog
ProductID <-- (pr k)
Description
CostPrice (ex tax)
SellPrice (inc tax)
<Quantity removed>

Table: Status
StatusID <--(pr k)
Status < -- this is the "status" of the quote ie: 'in progress', 'accepted', 'not accepted' etc..

Table: Quote
QuoteID <-- (pr k)
Created < --date created, auto generated ("=now()"??) will this work?
Expires < -- quote expiry date ("=now()+14"??) will this work?
Status
SalesmanID
CompanyID
<removed totals and profit fields--these should be calced on demand in queries>

Table: QuoteDetails
QuoteDetailID (PK)
QuoteID
ProductID
Quantity

Table: Salesman
SalesmanID <-- (pr k)
Salesman

Table: Company <question: will you ever need >1 contact at a company?>
CompanyID <-- (pr k)
Company Name
Contact
phonenumber
faxnumber
streetaddress
suburb
postcode

Your relationships will fall anyplace where a field on TableX appears as the primary key on another table.

Also, you will be happier in the long run if you make sure not to use spaces in table and field names.