Microsoft Access help

[oliverwatson1]

Im making a database using access,

I have one table (called materials) which lists the materials and cost,


another table (called Job) shows the materials used for that Job.

I want to create another field on the Job table which shows the total for the materials entered

How would I go about doing this?

Thanks for any help!

[orange]

Don't store a calculated field.
Create a query that will Total the materials or cost (which ever you intend to use)(could set it up to do Total by Job if needed) and run the query.

People willadvise you to NOT store a calculated value.
You can get the latest value with a query.

[oliverwatson1]

sorry, im still quite confused what to do

On the Job Table the materials are listed on a multi select combo box, I want another field on this table showing the costs of these materials

Please explain in as much detail as possible, im quite new to access

thanks

[orange]

There are several free video tutorials here.
Some are specific to comboboxes.

http://www.datapigtechnologies.com/AccessMain.htm

[ajetrumpet]

There are several free video tutorials here.
Some are specific to comboboxes.

http://www.datapigtechnologies.com/AccessMain.htm

Orange, seeing the two posts that Oliver has already got here, I would guess that he's not going to understand the majority of what's on that site. But then again, it's not a professionally done webpage anyway. Whoever did it, I'd suggest a redo!

But at any rate, Oliver, combo boxes in tables are a no-no. Don't do that. The reason, to beginners, is really irrelevant because you'll probably never see the consequences of it, but it should be changed right now.

The other thing is the fact that you seem to be using tables too much. Tables have one purpose - store data. And that means what it means - just DATA. Nothing else.

I would encourage you to make the move to forms, which is what Access is all about. That's what defines this program, really. Tables are the harmonic language of data storage. It doesn't matter what program is using them. They all function the same way, within virtually all programs. So trying to expand knowledge about how to manipulate data in tables directly is, in all reality, a waste of valuable time, especially if you don't have much of it!

As far as solution is concerned, the job details cannot possibly be all stored in a combo box in a table. That makes no sense. This is what you should have, given your situation here:

TABLE1 (materials)
materialname
materialcost

TABLE2 (jobs)
jobID
jobname (if applicable)

TABLE3 (job details)
jobID (duplicate this as many times as needed, one for each material used)
materialused (material name or id)


That's the proper setup. And from that point, the manipulation of all that data (which seems to be all over the place if you look at it in tables directly) takes place in forms where internally-created functions can do the work for you if you just ask. That's what working with Access is all about. Once the data is in, the tables shouldn't really ever be touched again.

[oliverwatson1]

ajetrumpet:
Thank you so much!
that really did help alot, i was so confused before!

So I've done what you said to do,
I have a JobDetails table which has the MaterialUsed, MaterialUsed 2 etc but I now want to work out the cost of these items, how do I go about doing this?

Hope that makes sense

[ajetrumpet]

what you'll do with something like that is calculate them in forms. Have you got a form yet? If you don't, you should make one. Doing this in tables is impossible (it isn't, but being a beginner, you should just memorize that as a rule so you don't get into trouble).

taking a stab at what I think you've got: If i had a table of materials listed for a job, and that was related to the materials table that had the costs in it, I would write this query to group the jobs and get total costs for them:

select jobtable.job, sum(materialstable.material) as CostOfJob from jobtable 

INNER JOIN materialstable on jobtable.jobnumber = materialstable.jobnumber

group by jobtable.job

do no copy that code verbatim, as it's just an example for you to follow. Assuming you're trying to get the cost of each job that you're doing, that's probably what you're going to need.

and then of course, you display this stuff in a form so it looks decent, and you do that via binding that query object to your form and using form controls to display all of these numbers. That's the big deal with this program. That's why we use it!

[oliverwatson1]

Great, still a little confused though...

That code is really useful but which parts of it do I have to edit?

Just to make things clearer I have uploaded my database onto Mediafire (tried the website uploader but it was just over the limit)
If you wouldnt mind having a look at it?

Thanks and sorry for being such a newbie!

http://www.mediafire.com/?3au47lpfbk5pprz

[ajetrumpet]

you need to learn sql statements, oliver, if you don't know how to use what I gave you.

The Access version of sql is:

select TABLENAME.FIELDNAME

sql is all the same, but the syntax changes. e.g. - the punctuation used. Oracle is simplest, as it uses something like this:

select field1, field2

there is no punctuation.

I don't have time to look at your file. sorry. as I said too, I would assuming your setup when I wrote that. You need to replace tables and fields with your own.