Update queries have always thrown me....

[QMDirk]

Can someone help? This is real easy, I just can't seem to get it. I have a table (named "Reviews Needed" with 2 fields: "MaxOfFINISHED PRODUCT NUMBER" , and "LAST REVIEWED". A second table named "tblBOM" has the same 2 fields and more. I want to write an update query so that when I change the value in table "Reviews Needed", the "Last Reviewed" field in "tblBOM" will be updated. Anyone?

 

[plog]

That's not how databases are to work. You don't store calculated values nor duplicate data in multiple places. Instead, you calculate it when you need it.

You should build a regular SELECT query to determine the [LAST REVIEWED] value. Then reference that query instead of saving it and having to hack together a system to store it.

 

[QMDirk]

That's not how databases are to work. You don't store calculated values nor duplicate data in multiple places. Instead, you calculate it when you need it.

You should build a regular SELECT query to determine the [LAST REVIEWED] value. Then reference that query instead of saving it and having to hack together a system to store it.

The first table is the result of a Make Table Query. (There's really only 1 table, since the 2nd one is going to be overwritten each time it is used). I want to be able to select one of the records on the form and run an update query so that the value updates in the real (permanent table).

 

[jdraw]

Perhaps you could describe/overview the issue you are trying to resolve with this proposed database using simple, plain English. There may be some options. Update queries can be so permanent and have caused havoc to many databases by not testing the WHERE clause in advance.
Good luck.

 

[plog]

UPDATE and MAKE TABLE queries are huge red flags that you're not efficiently using a database as its intended. I'm with jdraw--its time for a big picture of your database and a big picture of the issue. So without using a ton of database jargon, please explain 2 things:


1. What it is your organization does.

2. What your database for your organization to that aim.

 

[QMDirk]

Let's re-phrase the question. I have a table of records, each record has a "Last Reviewed" date. I wrote a query to show all records where the "Last Reviewed" date is older than "Today()-365". That returns the right records. Now I have a form base on that query and I made it a 'split-form' so I can view the records below and edit them at the top. Does this sound more conventional? I also put an unbound textbox on the form, but I may not need it?...

 

[plog]

No, more confusing. Your initial post was about data, now you're bringing forms into it. And you didn't rephrase the question, you only asked if the 3 sentences prior to the question made sense.

 

[QMDirk]

UPDATE and MAKE TABLE queries are huge red flags that you're not efficiently using a database as its intended. I'm with jdraw--its time for a big picture of your database and a big picture of the issue. So without using a ton of database jargon, please explain 2 things:


1. What it is your organization does.
we make cooking spray. for that I have a table or records, each with a separate review date.
2. What your database for your organization to that aim.
the records have to be reviewed yearly.

 

[QMDirk]

No, more confusing. Your initial post was about data, now you're bringing forms into it. And you didn't rephrase the question, you only asked if the 3 sentences prior to the question made sense.

and you're being a jack ass. the question is simple. And of course there were forms involve.

 

[Minty]

@QMDirk And now you're being rude to someone trying help you. Guess who's likely to gain the most from that...

So I'll ask a similar question. You appear to have some data, which we know nothing about.

You have a date field that you want to update, but that normally indicates a poor design so we are asking you what it's purpose is, and how you got to the position of needing to update it, as normally you would calculate a future or past date from a fixed (i.e. shouldn't be changed) data point. Does that help?

 

[plog]

Out of all the comments in your prior post his one stuck out the most:

And of course there were forms involve.

That's something a person who thinks Access is just "Excel With Forms" would say. And you're making other common mistakes they make in moving to Access--making tables out of queries, trying to update fields from other queries, creating temporary tables/deleting them/recreating them, etc.

Access is a database and it handles differently than a spreadsheet. If you get this "simple question" answered to your satisfaction, you're just going to return over and over with more "simple questions" because you've started down the wrong path for how databases are to work and built a poor foundation for your database.

Stick with this thread and someone might come along and help you solve this in the manner you want. But it won't be the manner in which it should be solved. I'll quit being a jackass now and drop out.