Database Check

[dfwcomputer]

Could somebody please check over my database, it is finished and just would like some last minute Input.

Click Here

Any Ideas would be greatly appreciated.

Thanks,

 

[Pat Hartman]

Customers form: I would add an unbound combo in the header to find a customer by name
Employees form: same. Also, using one name field rather than separate first and last names is poor practice.
Machine form: Sort the parts list
Parts: What is the purpose of the Part Number? It appears to be unbound.
Sales: Should be form/subform and have search for customer.
Service: add search and sort parts list.
Customer Details: Has report header but no page header. Way too spacey. Header information is repeated for each customer. Report should be reformatted as a simple list.
Customer Phone List: I like it
Relationships: Why is Machine linked to Customer on MachineID--CustomerID? This looks like an error.
Relationships: Service should be related to sales on CustomerID and MachineID rather than to customer and machine separately.
Column names: "Name" is a poor choice (in customers and employees) for a column name since it is the name of a property that is used EVERYWHERE. You will have trouble with this at some point.
Relationships: You have an extra instance of the service table on the diagram. You can delete it.

 

[dfwcomputer]

Thanks for your reply,

Machine form: Sort the parts list

Do you mean sycronized combo's if so what's the best way to go about this?

All the parts are known to us by part number as there really arn't that many.We use MYOB for our main buisness stuff this is mainly a db to have reminder's to allow employee's to start the db every morning and know what services they have to do that day. did you schedule a job and restart db to see the effects.Does this clarify or do i still need to change it.

Sales: Should be form/subform and have search for customer.

Service: add search and sort parts list.

could you clarify these a bit, sorry still learning access.

Customer Details: Has report header but no page header. Way too spacey. Header information is repeated for each customer. Report should be reformatted as a simple list.

Reports are one thing i am having real trouble with i originally wanted a form, where you have to combo's (one for customer's and one for machines)you would then select a customer and that would updates the machines combo to show all the models that customer has in case they have more than one machine. upon selecting the machine it would display a report showing the customer's details and the model of the machine along with the required parts to complete that service. {could be related to relationships as to why i am having trouble with this, or maybe i just have not got a clue what i am doing.)

Customer Phone List: I like it

Got this from northwind, i liked it also when i first saw it. Might be good if i could intigrate this in with the reminder's form for employee's so that when they get a reminder they good click a command button to display this and look up customer's address and phone details to organise the service on there machine. What do you think?

I have included the relationships, is this what you meant or have i gone drastically wrong.


Thanks,

 

[Pat Hartman]

Service should be related to sales on CustomerID and MachineID

If you add a form header section, then add a combo to that section, the wizard will walk you through setting up the combo. One of the options will be "find a record on this form". That's the one you want.

 

[dfwcomputer]

Thanks got it now,

Any comments on the rest?

 

[dfwcomputer]

please, please please help as i am totally stuck

 

[Pat Hartman]

Stuck on what?

 

[dfwcomputer]

i don't kinow everything i seem to do when trying to get my forms setup does not work i think because i don't have the relationships right?

 

[Pat Hartman]

It is really hard to solve non-specified problems. Did you make the structure changes that I suggested? I noticed that you made a new post showing the old structure. That is only going to make it harder to help you.

 

[dfwcomputer]

restarted db as the old was quite a joke!

Ok got it pretty close just need some help relating tblService & tblServiceParts any ideas?

To me it looks pretty good and seems to work pretty good entering data through the tables. all the expected results show up.

 

[Pat Hartman]

If a machine needs to be sold in order to be serviced, I would remove CustomerID and MachineID and replace them with SalesID. I'm assuming that EmployeeID in the service table would be the employee who serviced the machine so it would need to stay. I would remove PartID from tblServiceParts and replace it with MachinePartsID
You would then join
1. tblSales to tblService on SalesID
2. tblService to tblServiceParts on ServiceID
3. tblServiceParts to tblMachineParts on MachinePartsID

 

[dfwcomputer]

All changes done, Thanks

As you have probably seen i have look ups in the tables via query do these look right or is the an error?

One trouble i keep coming across if i want to look up the details of a customer in the sales table it wants to input it as the customerID number instead of the customer's name, this is why i have used the queries.

Thanks

 

[Pat Hartman]

1. You didn't make the relationship between tblServiceParts and tblMachineParts.
2. I NEVER use lookups in the table definition. This causes insurmountable problems when working with the table or queries based on it in VBA. I create queries that join to the lookup table. That allows me to select the ID and/or the text value from the lookup table.
3. I would never just drag a table onto a form. I would always make a subform.

 

[dfwcomputer]

i have included the new db with the relationships set up now? don't know how i missed that last one!

2. I NEVER use lookups in the table definition. This causes insurmountable problems when working with the table or queries based on it in VBA. I create queries that join to the lookup table. That allows me to select the ID and/or the text value from the lookup table.

there is now no look up's at all if you could show me an example it would be much appreciated.


thanks,
Dean

 

[wazz]

Cascade Delete

i notice you have Cascade Delete Related Records checked for the table relationships. i think if you keep that checked, then if you delete a customer, for example, you will also delete the sales records for that customer. same with employees, i believe: if you delete an employee they also will be deleted from the sales records and/or service records - wherever their name shows up.

perhaps someone could verify this. - w

 

[Pat Hartman]

Cascade Delete is both powerful and dangerous. You need to understand when to use it. The ONLY appropriate place is a relationship between a parent table and a child table. For example, you WOULD specify cascade delete on the relationship between tblOrders and tblOrderDetails because if you delete an order, you would want the order details to also be deleted. They wouldn't make any sense by themselves. You would NOT specify cascade delete on the relationship between tblCustomer and tblOrders. In this case, you would not want to delete all orders if you delete a customer. You would want to control this programatically so you could make sure that there was no active order. You would NOT want to specify cascade delete for the relationship between tblEmployee and tblOrders. Just because an employee left the company doesn't mean that all orders he wrote should also be deleted. Again, your business rules will dictate the actual requirement but you will will want to control it programatically.

there is now no look up's at all if you could show me an example it would be much appreciated.

- an example of what? A query? To build a query that joins a table to a lookup table, open the QBE and add both tables to the grid. Draw a join line from the main table to the lookup table. Change this join type by double-clicking on the center of the join line, to a LEFT join so that a null value in the foreign key field will not prevent a main table record from being selected. You can then select whatever fields you need from each table.

 

[dfwcomputer]

Posted By: Pat Hartman
an example of what? A query?

What I meant was I left all the relationships set up but I also had queries set up e.g tblCustomers you could select StateID drop down and the query inputs the state you select. instead of the StateID number. the database now has no queries and was wondering if you could setup say tblCustomers & tblStates . so I can learn what to do.

(e.g should tblCustomers have a dropdown box for selecting the state or should it be left as is and use forms and queries to set this up.)

Thanks,
Dean

 

[Pat Hartman]

and was wondering if you could setup say tblCustomers & tblStates . so I can learn what to do.

No I can't. Follow my directions to do it yourself.

 

[dfwcomputer]

so if your not here to help what are you here for?

and if you take the time to explain a bit better instead of being so abrupt i would be able to do it my self! but then again if i could do it my self i would not be asking for help.????

And you wonder why i started another post! with a reply like that. Not everything is black and white there is a grey area,, so come on over there is plenty of room!

Becoming a moderator for a specific forum is usually rewarded to users who are particularly helpful

Maybe you should think about this?

 

[wazz]

there is a very fine line between helping and doing. this applies here and everywhere else in life. (look at the world). and i think it may be more difficult than some people realize to be a helper. it is important to step back now and then to let things sink in before doing more; or before asking for more. the answer is not always there in front of you, but, often, it is.