0

Is it at all possible to make the determination of which columns to update in an update statement conditional?

I am trying to run an update query to update one of 30 columns per record, but only if the previous field in the record is already filled in. Basically I have a table with projects and a table with donors. Each project has 1 to 4 donors, but each donor has funded between 1 and 25 projects. The projects page is filled in with donors, and so I am trying to automatically fill in the donors with projects they have funded. So far my attempt is as follows. (The query from which I am updating is a query that selects fields when the donors from the donor table equal donor 1 or donor 2 or donor 3 or donor 4 from the project table) :

UPDATE [Appropriate Query] 
SET (iif([Project Funded 1 title] is null OR [project funded 1 title] <>   
[Project title], [project funded 1 title], iif([project funded 2 title]
is null OR [project funded 1 title] <> [Project title], [project funded 2 title])) 
= Iif([Project title] is null, [cc number], [project title])

(i have this nested loop first SET part looped for 30 columns. the second part of my code is because some of the "project title" fields are empty but I need some identifier in the field)

So far I have only gotten error messages. Is it at all possible to make the determination of which columns to update conditional on their contents as illustrated above? I know the iif statement is ugly, but JET SQL (the language of access) doesn't allow case statements, from what I understand.

Thank you so much, and forgive my ignorance.

Benjamin
  • 1
  • 1

1 Answers1

0

You can't, except by building the SQL dynamically with VBA.

BUT you shouldn't do that (at least not here), instead you should correct your table design.

You have a many-to-many relationship between projects and donors, so you should have a Junction table between them, instead of multiple columns in the base tables.

Edit

Sure, with a series of INSERT queries, e.g. if you have filled the projects table:

INSERT INTO junction (project_id, donor_id) 
SELECT id, donor1 FROM project WHERE donor1 IS NOT NULL;

INSERT INTO junction (project_id, donor_id) 
SELECT id, donor2 FROM project WHERE donor2 IS NOT NULL;

etc.

Andre
  • 26,751
  • 7
  • 36
  • 80
  • Thank you very much, that's exactly the information I have been looking for. – Benjamin Apr 20 '16 at 15:23
  • A follow up question... I have already populated many of the fields with data. Is there a way I can populate the junction table automatically with the ID number values? – Benjamin Apr 20 '16 at 16:17