Table Help

[dfwcomputer]

I thought i was finished but seem to be running into some trouble just wondering if yous could have a look at the tables and relationships and see if they look alright. As I am starting to get confused.

This is access 97 Version db 97 version

 

[wazz]

made a few changes. hope they help reduce confusion.
w

 

[dfwcomputer]

Wazz are you saying to delete my tables and keep the ones you created?

Thanks
Dean

 

[wazz]

in a word, yes. i left the originals if you're not convinced. i moved quite quickly but i think the changes work. how does it look to you? there are more changes that could be made. let me know if the changes make sense and i'll explain if necessary.
w

 

[dfwcomputer]

ok i think i have got it, so which tables of mine should i delete just machine,machine parts and parts?

also the main reason for this db is to schedule services on machines and be able to use a reminder for the day the service is scheduled will this table layout be able to adapt this.

thanks again,
Dean

 

[wazz]

tables to delete: Machine; Machine Details; and Parts.

In the Service Table:
- Change the field "Date" to "DateIn"
- Add field "DateOut"
- Remove fields "Previous Service" and "Next Service" -- these can be looked up (PreviousService) or calculated (NextService) and so do not need to be stored at all (Previous Service is already stored as DateIn). to send reminders about next service you would calculate using the DateOut field (+ xDays) or similar
- possible field addition: DateOutRequest or DateOutEstimate -- if the customer needs it by a certain date (if you take requests) or if you give time estimates -- you could insert this field between DateIn and DateOut; might help to prioritize your job list and you could also use it to see if you've been getting your work done in the time promised.
- you might want a new table tblServiceLine. tblService would relate to tblServiceLine as 1:Many. Each Service could require many Jobs.
- you might then want a table - tblJobs - that lists each type of job (Part Replacement; Consultation; Cleaning; Lubrication; etc.) and put a Foreign Key from this table into tblServiceLine so you can select a job (or jobs) from a list. let me know if you would like an example.
- if this project is not too far along then start renaming your objects with prefixes such as tbl for tables, frm for forms, qry for queries, etc.
- sorry for delay responding again

w

 

[dfwcomputer]

I have dine a lot of work on this since, I think it is finnished have a look and let me know what ya think.

password is "landwaste"

dbl click dates for calendar, also try scheduling a service and restarting database for pop up.

db1 2002 version

Thanks
Dean

 

[wazz]

it's looking really good.
suggestions:
Sales table:
- Serial Number and Engine number: do these refer to the Machine? if so, move these fields to the Machine table. since you have machine ID in the sales table you can look up the serial number and engine any time using the key. those fields are machine-specific, though (it seems), so they belong in the machine table.
- add an EmployeeID (unless you always know who is doing the selling)
- you should probably have a new table SalesLine (like ServiceLine) if a Sale can include more than one item (Sale:SalesLine is 1:N):
Sales
SalesID (PK)
CustomerID
Date
WorkOrder
EmployeeID
SalesLine
SalesLineID (PK)
SalesID (FK)
LineNum
MachineID
Quantity
etc

ServiceLine table
- add this table as described above, link with Service table

Jobs table
- ExpectedCompletionDate: move to the Service table
- Complete chkbox: remove (possible) - if you add a DateOut field or DateCompleted field you can see if a job is finished by checking to see if this field is null
- possible: add field Description

Other simple suggestions (maybe you already thought of):
- on your forms, for each ID field, change the Tab Stop property to No (on the Other Properties tab)
- Machine Details subform: hide the ID field (Visible: No); add a description to the dropdown combo (PartNumber and PartDescription) or even just the description (and ID)
- create a query for every form and combo box and use those as the row/record sources

- i just did what you suggested (add a job close/open) - very nice touch! you could use the Service table instead if you include a DateOut field and check for nulls; that way you don't have to enter the job twice (against normal forms). but perhaps you are simply creating a reminder for yourself, unrelated to the other business jobs, in which case, nevermind. maybe rename the table to Reminders.
((i guess the reason for using a DateOut field is to check just how late a job was, or, to check how long it actually took to finish a job. you can adjust your estimates accordingly. if you use the DateOut field you don't need the check box. either way.))

i think it's looking great.
w

 

[dfwcomputer]

Sales table:
- Serial Number and Engine number: do these refer to the Machine? if so, move these fields to the Machine table.

This was a hard one, as we have many machines of the same model but all have different serial numbers, engine numbers e.t.c. (would this create a whole heap of records of the same model?)

add an EmployeeID (unless you always know who is doing the selling)

Good idea Forgot that one.


These machines are very big mainly for chipping trees and stump grinding so all of our customers only have one machine a part from select customers who bought 2 machines over a 12 month period if this helps?

Thanks
Dean

 

[wazz]

This was a hard one, as we have many machines of the same model but all have different serial numbers, engine numbers e.t.c. (would this create a whole heap of records of the same model?)

yes it would. but that's OK. in the same way that Parts can belong to a Category of parts, Machines can be of a certain Model. Ergo: you need a table "Model". The primary key of tblModel will be the foreign key in tblMachine. for every Machine you can look up the model in a combo box. Each Model can have characteristics of its own that you store in tblModel. the serial number i would presume will be a characteristic of the Machine. i'm not sure about the engine number, though. does every Model use the same engine? if so, you would store the engine number (engine type? - another table?) in tblModel, too because it's an attribute of the Model. i'm not quite sure how the Machine breaks down, but you probably get the picture.

it looks like people won't buy more than one machine at a time so you won't need the tblSalesLine.

 

[dfwcomputer]

Added the query to all the forms Makes it a lot quicker Thanks

Still having a little trouble understanding machine layout though;

If this helps we have a lot of machine (wood chippers) 30 different models to be exact, we might have 5 of the same model machine on the floor to sell, but each machine of them 5 has a unique;
serial number

work order number (this is the sheet that is supplied from the factory that tell you about the crank, pulley sizes etc This is filed away and can be looked up via the work order number in sales form.)

Engine number (Possibly all the same engine but all have different engine numbers all most like a unique serial number for each engine that is built, This is also listed on the work order sheet from above.)

rego number (as they are all on trailers).

What do you think about Relationships Have I got them looking alright?

I have included the latest version with most of the changes Sorry about the size (200kb) but it's all most in it's complete state.

Latest Version

Sorry to be a bother but your help is extremely appreciated.

Dean

 

[wazz]

If this helps we have a lot of machine (wood chippers) 30 different models to be exact, we might have 5 of the same model machine on the floor to sell, but each machine of them 5 has a unique;
serial number

do you only sell wood chippers? in any case i think you might need this approach:
tblManufacturer (or tblMake)
MakeID
MakeName
tblModel
ModelID (PK)
MakeID (FK)
ModelName
ModelCode
tblMachine
MachineID (PK) -- could use the serial number (remove from Sales table)
ModelID (FK)

work order number (this is the sheet that is supplied from the factory that tell you about the crank, pulley sizes etc This is filed away and can be looked up via the work order number in sales form.)

this information is about the Machine, not the Sale. if the Work Order is the same for each model then store this in tblModel. if it's always a bit different for each Machine (even for the same model of machine) store it in tblMachine.

Engine number (Possibly all the same engine but all have different engine numbers all most [almost ] like a unique serial number for each engine that is built, This is also listed on the work order sheet from above.)

this is a Machine Part (Machine Details)

w

 

[dfwcomputer]

Sorry one thing i have also forgotten to mention is that we use MYOB for all our transactions, this DB's main use it for employees to see what services need doing via reminders that i will set (so every morning they open database and see reminders), that is why the info in the database is limited.

Sorry if this caused confusion?

Thanks
Dean