Hi

[June7]

You have not designed db? Have you reviewed the referenced tutorial site? Have you looked at other threads on topic of manufacturing db as suggested earlier?

Identify data entities and relationships, build tables or at least a diagram of the structure. This is called an Entity Relationship Diagram. An ERD is a basic tool for planning db.

[rizzy]

june7 yes the company builds wood product. i will start by giving you a bit of background about the flat file database. first field you have schedule(it is used by the company to schedule the work to achieve weekly targets in money and now you must have seen that each schedule can have many work orders to achieve that target depending on the total value of each work order) next you have workorders now this is the unique key apparently as everything is linked with the workorder(w/o).then you have cutomers with multiple workorders and multiple addresses etc. then we move to the products (start with product type gives you what the product is the columns on the right of the product type is all the parts different shapes, quantities to make that product)now i think this is still ok but the next three parts are first delivery details this section gives you all the delivery details as i mention in the file with the arrow shape (now it is obvious that to track the delivery you need the workorder and customerid at least) and one important thing is that we start from schedule non stop tothe end of the database each record is linked as i said above that you start from workorder, customer, product type, delivery detail, productstages tracking, machine tracking of the product. these last three sections have formulae as well in total valuein the delivery , product stage tracking and machine tracking , i hope this makes sense . i have done some tables as i am myself not expert and haven't created database to this extent and without any help.i only have worked with my sql , excel , access but small database this is quiet complicated. what do you think about the database

[June7]

You have not yet provided a relational db for analysis. As stated, manufacturing is a difficult db to design. I have no new recommendations.

[rizzy]

i have read your reply i ahve done research about database design thats where i have started to create an understanding and model the database and how its working. i have knowledge in ERdiagrams, Normalisation as i have done all this and did create data in SQL even thought the concept is same but ACCess is quiet different in operations which i am learning. i have done tables and know about relationships but the problem is i need some expert advice to check if these table are fine and i can move onto the relationships

[rizzy]

do you want me to send you the tables/entities for the above flat file. do you want in the shape of er diagram or just tables witha all the attributes

[June7]

As I said, post db design for analysis.

[rizzy]

i have table cutomers
customerid(PK)
customerName

next table

CUSTOMERDETIALS

CustomerID(FK)
CusotmerAddress
CustomerCity
CustomerPostcode
CustomerPhone

[rizzy]

table name
SCHEDULE
ScheduleID(PK)
ScheduleNO

the customer and customerdetails table has one to many relationship as one customer and has many addresses

[June7]

Need AddressID field?

Not much there. What about Products, Employees, Materials, ProductMaterials, etc.

[rizzy]

Table name
ORDERSTABLE
WorkorderID(PK)
CustomerID(FK)
ProductID(FK)
StatusID(FK)
DeliveryID(FK)
OrderDate
Totalvalue

[rizzy]

sorry i will send you the relational database(ACCESS) over the weekend. i will send you the database i have created till now thankyou for your help really appreciate

[June7]

Can each order have more than one product? Consider an OrderDetails table.

[rizzy]

how can i send you the database confidentially as all the details were in june7

[ssanfu]

Curious.... you posted the Excel flat file with data. Now you want to send the dB just to June ?

Not a problem... good luck. I'm out!

[June7]

Attach db or any file to post in forum, same as spreadsheet.