From the beggining: Help organising my information (1 Viewer)

[rm.harper]

Hi,
New to the forum, I've used access in the past but drawing a blank at the first stage. :banghead:

I'm currently trying to build a database to store information of customers, suppliers, products, Jobs (address of job, company who have contracted the works, job ID which has already been generated).

All in all my aim is to be able to manage the jobs get material costs for each job and also keep a detailed summary of materials used, which will also in the future include whether job has been invoiced and returned paid.

Unfortunately I'm struggling; because materials can be supplied by around 4 different suppliers, with each holding their own prices, I'm not sure how to organise all my information onto tables so that when it comes to drawing up forms and queries everything is set up in a way which I don't have to go back and re-organise things should there be problems.

Could anyone help me please?

This is how I have it at the minute (I'm open to suggestions of course, and they are appreciated) bare in mind nothing has been created yet, I am still at the pen and paper drawing stage ha

Tables:
Customers> Company Name, point of contact, billing address, city, postcode, email, website, phone and fax numbers

Suppliers> Company Name, Billing address, city, postcode, email, website, portal link, phone and fax numbers.

Materials> Description, supplier, price (dependant on supplier)

Jobs> Job ID (already generated from a previous system), contractor (person paying us to do job) job address, material delivery ticket numbers

On jobs I would also need to include materials and amounts used as well as generating costing's for each job. (hopefully using my materials and supplier tables) Sometimes we do works on the same job but distinguish between each 'phase' by the date the works were done, which would also need to be included.

Like I said any help will be appreciated, just want to make work a lot easier and I know that doing it on access is the way to go. Its just been a while since I built anything from scratch.


****update 14.50 06/03/14:

So I have been looking around and it seems what I actually want to do is merge a order management database with a contractors database to handle all aspects of the working. Such as holding information on contractors, materials used which i can then create reports for job costing's, material summaries for each job etc. But im still finding it difficult

Thanks in advance
R M Harper

 

[Cronk]

You need another table called, say, tblSupplierPrices. See below.

Firstly, all your tables should have an Autonumber ID field eg SupplierID, MaterialID. Also it will easier down the track if you do not have spaces in field names eg CompanyName

I know you have an externally derived Job ID, but also use a separate Autonumber JobID in your tblJobs. This is because the external number could change and cause complications in your system.

As to the new table, tblSupplierPrices, it should contain the following fields, plus any other field where there is a difference between suppliers
SupplierPriceID
SupplierID (key field in tblSuppliers)
MaterialID (key field in tblMaterials)
Price

If you wanted to record historical prices, you would need a PriceDate field as well.

 

[rm.harper]

Thanks very much for your reply.

I am currently using the access auto number simply for use within access as you said.

With the supplier pricing how should I enter the material with the 3 prices without making duplicate information? you may have already answers this but I don't understand.

Would it not be a better idea to include the supplier pricing within my original materials table as that is the only significant reason for having it there. All materials that are due to be ordered can be from 3-4 suppliers, who evers cheapest nearest and can get it there gets the order so ill have around 15 materials with 3-4 prices in each.

Thanks again for your time, much appreciated!

R M Harper

 

[Cronk]

If you only want to store one material price being that for the current supplier, have the price with the material item. If you want to store all supplier prices for an item of material, use the additional table I suggested, tblSupplierPrices, In that table, you can record multiple prices for the one material item.

If MaterialID is one (screw), this table would have multiple values (prices), one for each supplier of that part.

 

[rm.harper]

If you only want to store one material price being that for the current supplier, have the price with the material item. If you want to store all supplier prices for an item of material, use the additional table I suggested, tblSupplierPrices, In that table, you can record multiple prices for the one material item.

If MaterialID is one (screw), this table would have multiple values (prices), one for each supplier of that part.

I think I understand now.

tblSupplierPrices: For example-

FK-Material(tblMaterials) - FK-Supplier(tblSuppliers) - Price
Rubber ---------------------------Aj Rubber Plant--------------- 0.59
Rubber --------------------------Rubber Industries ------------- 0.54
Rubber ----------------------------Star Rubber ------------------0.60

Is this what you are trying to describe? So the same rubber can be bought from 3 companies so a specific material may have 3 entries within the tblSupplierPrices but they differ from eachother because of the price?

 

[Cronk]

Exactly. Except you would be storing the foreign keys, MaterialID, SupplierID So your table would read
4 8 $0.59
4 9 $0.60
4 43 $0.54

This is scabable. You can add new suppliers and a supplier can provide one or more materials.

To stop double entries, use a composite index on SupplierID and MaterialID in this join table and make it unique.

It now makes it easy to create a query to find the supplier with lowest prices for materials. Try doing it when the prices are in the material or supplier tables.

 

[David R]

Try doing it when the prices are in the material or supplier tables.

Note: Cronk is kidding here. You should start drinking before attempting this. :banghead:

 

[rm.harper]

hey I started drinking when I clicked 'create blank database' ha Thanks again for your replies, you've really helped me past the design phase. The database is almost done and i've started entering values to test it.
Solved; released I can code form fields like excel cells (happy days)
I have a table; tblJobs, which collects the main information for each job or site we work on. using this table I want to make it so when I put the unique 4 digit id number access will auto populate the address and customer fields leaving me, or any other user, to just input the start date and submit that form. Looked it up and I kind of understand but you guys just have a skill for explaining things to half asleep office people like myself ha

Also, in my table tblScheduledWorks it shows what date we are working on a specific site. So information for the site must be visible such as JobNo, Customer and address. These fields are fields in the tblJobs but need to be present in tblSchedules works really as all information relates. How do I do this without duplicating data?

Last one ;
As above (auto-populate fields) ive done it so it works but it seems to be putting the CustomerID in the customer field as appose to the Customer Name. Checked my relationships and it isn't that. But my look column is listing the customer in the CustomerID instead of name....I just don't seem to be able to change it hence why the auto populate will input the CustomerID so it must be my lookup column? help? ha

 

[rm.harper]

As to the new table, tblSupplierPrices, it should contain the following fields, plus any other field where there is a difference between suppliers
SupplierPriceID
SupplierID (key field in tblSuppliers)
MaterialID (key field in tblMaterials)
Price

Finally getting round to testing the pricing function within my database. For reference is there a way i can use the Supplier ID and Material ID but make it so the visible value is the names instead of numbers, simply for my ease of use?

SOLVED

 

[Cronk]

Use combo boxes with the row source based on tblSuppliers and tblMaterials respectively but bind the combo to the ID in your job table. Set the column widths property of the combos to 0 which hides the ID and displays the description only.