Writing sql code in MS Access

[ed7890]

Hi, I'm creating a simple Access Database, in which i have 4 tables. i have:

1. A table of parts
2. A table of process that can be done to the parts and the cost of each
3. A table that links them together, recording the part id and the process id
4. A table for process type, becuase the processes can fall into 4 different
categories (this table just stores the four categories names and is
linked to the "category type field in the process table)

What i want to do is have a form that would have a drop down menu for the part id. Then four textboxes that will show the total cost of the process for that part, for each category. you know what i mean. i hope i explained it well enough.
i can write sql well enough, i could write this for a webpage no problem but i can't figure out how to write sql for textboxes individual, access always tells me that i'm missing an operator or something

help, please

[Hack]

Are you trying to use that cumbersome query wizard? I've never been able to get that thing to work.

I'm more familiary with VB than Access VBA, but isn't it possible to just write an in-line SQL query, using standard SQL syntax, in Access VBA?

[Klymer]

if you have your select for your form's recordsource written so that you have all your fields, especially the calculated sums, you just need to link your textboxes to this field, but have to ensure you requery your form each time you change your data

if you want to have something like textfield.controlsource = a+b+c then you must add a "=" to the controlsource in the textfields property window (e.g. "=[fld1] + [fld2]")

[ed7890]

ok so i put my sql in textfield.controlsource but can you help me with what the sql code would look like. i cant get it work even when i copy it out of the controlsource for the form. it keeps givin me errors.

Basically i just want something like, SELECT sum(Process_cost) FROM TBL_Process WHERE "process_Type = "Recieving"

[Klymer]

nope, you have to understand how access works.

your form is a representation of your select statement for the forms recordsource. say you have this recordsource:
"SELECT SUM(proces_cost), Process_type, .... <any other things> ,... from ... group by..." - well whatever you need. then you can bind your controls directly to the fields of your recordset. if you want to calculate a sum from something, you could calculate that in your sql statement or switch your textbox (for example) to another controlsource.

you should best be looking into the DOM-Functions (DLookup, DSum, DCount...) which allow you to look for an independent Value.
e.g. if you have your form with: "SELECT * FROM TBL_Process WHERE process_Type = 'receiving' " (check your ""!!! Access won't understand your spelling), then you can always have a textbox which can look at a completely different table or fields, and can use for example as ControlSource:
=DSUM("Process_Cost";"tbl_Process"; "process_type = 'receiving' ")
check out the vba help on DSum and DLookup

[ed7890]

Thanks Klymer, i'll have a look for that.

If i want to include the sum in the recordsource for form how would i do that. i've tried addin sum in the query and that wont work for me either.

[Klymer]

could you post your sql query? would it make easier for me. p.s. i might be offline til monday, so perhaps the answer will be a little late.

[ed7890]

SELECT [TBL_Processes].[Process_Name], [TBL_Processes].[Time_Required], [TBL_Part_Cost].[Part_id] FROM TBL_Processes INNER JOIN TBL_Part_Cost ON [TBL_Processes].[Process_Id]=[TBL_Part_Cost].[Process_id];

Take your time with this, I wont be lookin at it till Tuesday i'd say.

Thanks

[JeffB]

ok so i put my sql in textfield.controlsource but can you help me with what the sql code would look like. i cant get it work even when i copy it out of the controlsource for the form. it keeps givin me errors.

Basically i just want something like, SELECT sum(Process_cost) FROM TBL_Process WHERE "process_Type = "Recieving"

Is this value suppose to change each time the user move from one record to another? If not, then you can run this query by code (like you would do in VB6) and show it in a label or textbox.

JeffB

[ed7890]

But what would that code look like. i cant get any code i write for access to work. could you post an example that i could copy/modify