Update query-multiple updates different criteria in different fields

[doug c]

Hi all,


New member.
I almost don't know how to ask this but here goes I have tried a search with no result.

I have a table (curmonthsale) with fields:

pnum
report date
sales amount.

I have linked this table with another table (sale) with fields:

pnum (the linked field)
jan
feb
mar
apr
may
jun
jul
aug
sep
oct
nov
dec

I need to do an update query that updates the fields in (sale) according to multiple criteria. Like:

when (curmonthsale) linked pnum matching pnum in (sale) and report date = "2016/01" updates the jan field to sales amount from the (curmonthsale)

I can do this query for one update at a time or in an IIF statement (7 limit), but i would like to do all the months in one update query.

In other words do all the updates in one query. if report date (curmonthsale)= 2016/01 than update Jan (Sale) with sales amount plus if report date (curmonthsale)= 2016/02 than update Feb (Sale) with sales amount, plus all the other months.

I don't know VBA at all, but can do some limited SQL. I do most my work in design view but have enough (barely) knowledge to do and edit the SQL view.

Hopefully I have worded this question properly and not too dumb.

Thank you for your help,
Doug

[orange]

I think your table design(s) violate most rules.
I recommend you review Normalization. and wikipedia
Getting your tables and relationships designed to meet your requirements and follow Normalization rules will simplify your database activity.

You might try watching some free video tutorials by Dr.Daniel Soper that will help you with the concepts.

Intro to Database
The Relational Model
Data Modelling and the ER Model

[doug c]

Thank you Orange,
I get these tables from someone else (state agency) and I don't think they are going to change them any time soon. They want the data back in the sale table in those fields. I am doing this for a friend as a favor who found out I knew a little about databases from some time I spent on DBASE IV about a hundred years ago.
Doug

I think your table design(s) violate most rules.
I recommend you review Normalization. and wikipedia
Getting your tables and relationships designed to meet your requirements and follow Normalization rules will simplify your database activity.

You might try watching some free video tutorials by Dr.Daniel Soper that will help you with the concepts.

Intro to Database
The Relational Model
Data Modelling and the ER Model

[ssanfu]

I would think you need an append query instead of an update query.

Really need to see the dB, but since you say you cannot change the table structures, I see 3 options:
1) Maybe you could create a crosstab query on table "curmonthsale", and use it in an append query and append(update?) table "Sales".
2) Use VBA to loop through table "curmonthsale" and append/update table "Sales". (my preference)
3) Manually make the changes to table "Sales".

[doug c]

Thanks Steve!
I will try your ideas and get back to you.

[MatthewGrace]

I admit I don't understand the structure, and need to see the DB. I'm especially foggy on the Table CurMonthSale - what benefit is there in the linked pnum field (which can only yield January, February, etc.)? Couldn't you get that exact same month extracted from the adjacent field called Report Date?

As I read it, it looks like you just want to see all the sales, grouped by each month, on a nice neat report. Best of luck to you.