View Full Version : Access Database Design Help

11-02-2005, 04:26 AM
Hi there, just wondering if there is anyone that can suggest what to do here.

I have created a web site using DREAMWEAVER, ASP and a database in ACCESS.

I am realy having problems with the database design.

So far I have created the following tables. I have left a few fields out as they are not neccisary in this email.

* tblSpecies - idSpecies & Fruit Type

* tblFinishes - idFinish & Finish Type (Meaning Smooth or Waxed).

* tblLocation - idTown & Town Location

* tblMoisture - idMoisture & Moisture Content

* tblSuppCategory - idSuppCategory & Supplier Type

* tblSuppliers - idSupplier & Name & Address & Phone & Fax & Email & Website & Description

From there I have created a search, results & detail.asp pages.

I have been told that in ACCESS I need to create another table that will put all of these together.

I would like to login to the search page and search the following. For these I have created search field for species and drop down menues for the rest.

(APPLE) (WAXED) (NSW) (2 deg) (FRUIT)

With this search i would like to see the results of all the suppliers that have APPLES in stock which have been WAXED and are located in NSW with a temp of 2 deg and are categorised as FRUIT.

The part that has puzzled me for so far is that 1 supplier has 50 differednt SPECIES. Another supplier may also have the same 50 SPECIES.

Would appreciate any help here. Have been lost for a while now. The more I think about it the more i get lost.



Creative Insanity
11-02-2005, 04:47 AM
Is your web hoster running a winbox as there webserver?
If not then save your time as ASP will not run on a Linux server.

11-09-2005, 03:37 PM
You need to relationship your tables...

each table needs a common field that links them all together like an id field. You can set up different types of relationship within access but i think that is only useful when building within the access gui

when you use asp and you are querying access tables then you will have to define your table relationships within SQL

sorry.. got that wrong I forgot that you can relationship your tables as a query within access and call that named query within Dreamweaver (i think)

example of coded sql join..

SELECT * FROM pfm_jobs left join pfm_work on pfm_work_job=pfm_jobs_job where pfm_work.pfm_work_group like '%s'

(note the join in the sql)

statement broken down..
select * from table pfm_jobs left join pfm_work on pfm_work_job=pfm_jobs_job

left join ( joins another table)

on pfm_work_job = pfm_jobs_job (sets the two relative fields from the declared tables to join on (values must be relative))


data-customerID 1
data-customerID 2
data-customerID 3
data-customerID 4
By linking the above tables you can see data-customerID 1 has 4 orders. If you linked these two tables data-customerID 1 would repeat 4 times against each order (one to many relationship)

there is no short cut to learning this so your going to have to do some homework