Need help writing an update query

I'm not good at this so I need some help.  Here's what I want to do.  I have 
two tables with fields that need linked to add an update.  To show what I 
want to do I have put the table and fields in brackets.  Thanks.  Here it is:

If [Status Table:Status=Sold or No Bid] and [Status Table:Date=1/11/2008] 
then in [List Table:Publication Fee] enter [$30.00]


0
Utf
1/23/2008 1:23:17 AM
access.queries 6343 articles. 1 followers. Follow

3 Replies
558 Views

Similar Articles

[PageSpeed] 11

On Tue, 22 Jan 2008 17:23:17 -0800, Rick <Rick@discussions.microsoft.com>
wrote:

>I'm not good at this so I need some help.  Here's what I want to do.  I have 
>two tables with fields that need linked to add an update.  To show what I 
>want to do I have put the table and fields in brackets.  Thanks.  Here it is:
>
>If [Status Table:Status=Sold or No Bid] and [Status Table:Date=1/11/2008] 
>then in [List Table:Publication Fee] enter [$30.00]
>

How are the Status Table and the List Table linked (if at all)? Do they have a
field in common? Have you defined a Relationship between the tables? If there
are 509 records in the Status Table and 167 records in the List Table, which
records should be searched for status and date, and which records should be
updated - and how can you tell?

Please use standard syntax rather than all this gibberish with colons:

[tablename].[fieldname] = "Sold or No Bid" 

for example.

             John W. Vinson [MVP]
0
John
1/23/2008 5:26:52 AM
Thanks for the response.

I'm sorry I don't know gibberish from syntax.  If I did, I may not be 
posting this question.  I'll try to answer your questions with what I do know.
> How are the Status Table and the List Table linked (if at all)? 
     They are linked by a common field called Cert Number

Do they have a field in common? 
     Yes, the Cert Number.

Have you defined a Relationship between the tables? 
   I don't know what that means

If there are 509 records in the Status Table and 167 records in the List 
Table, which
records should be searched for status and date, and which records should be
updated - and how can you tell?
  Both tables have the same number of fields (Cert Number); they just have 
different sets of information.
> 
> Please use standard syntax rather than all this gibberish with colons:
> 
> [tablename].[fieldname] = "Sold or No Bid" 
> 
> for example.
> 
>              John W. Vinson [MVP]
> 
0
Utf
1/23/2008 6:03:01 AM
On Tue, 22 Jan 2008 17:23:17 -0800, Rick <Rick@discussions.microsoft.com>
wrote:

>I'm not good at this so I need some help.  Here's what I want to do.  I have 
>two tables with fields that need linked to add an update.  To show what I 
>want to do I have put the table and fields in brackets.  Thanks.  Here it is:
>
>If [Status Table:Status=Sold or No Bid] and [Status Table:Date=1/11/2008] 
>then in [List Table:Publication Fee] enter [$30.00]
>

First off, learn about relationships and table normalization. They are
ABSOLUTELY FUNDAMENTAL to any productive use of Access. See some of the
tutorials at:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Next, be sure that [Cert Number] is the Primary Key of the List table
(assuming that table has one record for each Cert Number). Open the
Relationships window - the toolbar button has three little datasheets with
lines connecting them. Add the two tables, and drag Cert Number from List
Table to Status Table. Check the "Enforce Referential Integrity" check box;
this will ensure that you cannot set a status for a nonexistant cert number.

Then, create a new Query in the queries window. Add the Status table and the
List table to the query window. Drag the [Cert Number] field from the "one"
side table to the [Cert Number] field in the related table. You should get a
join line connecting them.

Add the Status, Date, and Publication Fee fields to the query by dragging them
from the table icons into the query grid.

On the first criteria line under Status type "Sold"; on the next line under
that, type "No Bid". Putting the criteria on two lines will use OR logic
returning the record if the status has either one of these values.

On *both* criteria lines under the Date field put

[Enter date:]

This will prompt you for a date (you can type 1/11 or whatever date you want
to see) when you run the query. It needs to be on both lines so that you find
those items with Sold and 1/11, *or* those items with No Bid and 1/11.

First, open the query datasheet as it stands. Do you see the records that you
want to see?

If so, change the query to an Update query using the Query menu option or the
query type tool in the toolbar. On the Update To line under the Publication
Fee type

30

Run the query by clicking the ! icon.

             John W. Vinson [MVP]
0
John
1/23/2008 5:33:05 PM
Reply:

Similar Artilces:

Help with a formula..
I am trying to create a formula that will take information from a cell on one sheet and combine it with text on another sheet. I know how to get the two together. My problem is that I want the part that is brought in to be bolded type. Here is what I have in the formula. ="we are pleased to submit our quotation for "&(cell reference)&" according to the following specifications." What I want to do is have the cell reference part be bold type. Is there a way to do that? It doesnt work if I bold the cell.. already tried it.. Any suggestions? Thanks! KK You'...

Help needed urgently
Hi, Im trying to check that one row of data satifies a criteria, then if it does it counts an entry in another column adjescent to it. Example: - Column A - Column B Blay - Y Blay - N Blay - Y Chop - N Chop - N High - Y What i would be asking (of a much bigger table) is If Column A = Blaydon and then column B (same row) = Y, then count. In this example I would want 2 returned as the answer. Is this possible? Thanks -- Andy Try one of these... Use cells to hold the criteria: D2 = Blaydon E2 = Y If you're using Excel 2007 or later: =COUNTIFS(A2:A7,...

Trying to create an Update query based on HR data to find upline V
Hi All, looking for some advice. I have an HR table that contains employee information but does not contain management chain info. Basically i am trying to determine who the employees upline VP is. The fields i have to work with are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would be to check the employees' manager and if the manager is a VP (based on job title), return the manager's name to a field called [VP]. If the manager is not a VP then check that manager's manager, so on and so forth until a VP is found. Any ideas would be much appr...

"the wizard is unable to open your query in datasheet mode, possi
"the wizard is unable to open your query in datasheet mode, possibly because another user has a source table open in exclusive mode. your query will be opened in design view" what this message mean ,and how can i solve it Hi - Please provide more details as to what you are doing when you get the error, what your database setup is (i.e. split Fe/BE?, multiuser?, version? etc). Without more information, we can only guess. Thanks - John amr wrote: >"the wizard is unable to open your query in datasheet mode, possibly because >another user has a ...

loan amortisatio chart not updating
Hi, I use MS Monet 2007 premium. I have created a loan amortisation account which breaks up my monthly instslment into pricipal and interest. The loan commencd from 7 October 2005 and is for a period of 5 years. The problem is that the loan account does ot show any loan instalments beyond 7 October 2006 (exactly 1 year after the commencement). Why is this happening. Why is the account not updating with instalments which have been debited to my account after 7 October 2007. Please help. Were you depending on downloaded transaction data for this account? Did the download link br...

help with dynamic tables
This is a bit complicated to explain but I'll try my best. In columns A, B, C I have different drop down lists. Column A has Store1, Store2, Store3, etc. Column B has Dept1, Dept2, Dep3, etc. Column C has ProductA, ProductB, ProductC. As of right now, these lists are not dependent on each other, I can choose anything from any list regardless of the previous category. Also, the length of these lists is undefined, meaning I will constantly be adding to them in sequential rows below. And then columns D and beyond have data such as Sales, Profits, # of items, etc. What I...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

Need code snippet to read offline PST file
Hi friends, I have a PST file in my local hard disk and have requirement to read PST file and parse through all folders and then each message item in all folders and then segregate them to different folders based on subject line. Please kindly send the code for the above requirement. Thanks & Regards Ramesh -- ramserp You're going to have to write your own code. Do you know anything about Outlook programming at all? You can start out by looking at information and code samples at www.outlookcode.com. -- Ken Slovak [MVP - Outlook] http://www.slo...

Help! Problem Referencing Excel 2003 in VB.NET
I am using Visual Studio 2008 Express Edition to develop some VB.NET code that manipulates Excel files. I am developing on a Vista/Excel 2007 machine, but I need the code to work on an XP/2003 machine as well. I initially set a reference to the Microsoft Excel 12.0 Object Library and imported the Microsoft.Office.Interop.Excel namespace. This works great on the VIsta/Excel 2007 machine, but it does not work on 2003, which needs the 11.0 library. I get an error that says "Unhandled Exception . . . Could not file or assembly 'Microsoft.Office.Interop.Excel, Version 12.0.0....

Querying multiple records in two tables
Hi, in my database I have tables for users (UserID, UserName), projects (ProjectID, Project Name), and qualifications (QualID, QualName). I have join tables for users_qualifications (UserID, QualID), and projects_qualifications. (ProjectID, QualID). What I need to do is run a query for a project to show which users have the exact matching qualificiations. Users can have many qualifications, projects can require many qualifications, users may only work on a project if the qualifications required/held match exactly. Please help. Assuming that ProjID, and QualID are numeric, the following sho...

Time update as a limited user not working
I added time update permisssion to my limited user acct. but it does not work. When I try, the time synchonization is greyed out. How can I get it to work. Thanks. On Apr 4, 12:33=A0pm, Mint <chocolatemint77...@yahoo.com> wrote: > I added time update permisssion to my limited user acct. but it does > not work. > When I try, the time synchonization is greyed out. > > How can I get it to work. > > Thanks. Is this Windows MCE SP2? What method did you use to add time update permission to your limited user account? Does your unlimited user accou...

Need Help with drop down list with conditions...
I need help. I'm having a very difficult time creating a sequence of list which change based on the item selected on the main list, lets call it the index: The Index list, the first list, would define vendors. In this case Hotel Companies (e.g Hilton, Marriott, Sheraton). The following list, the 2nd list, would define properties, but the properties would vary based on which hotel company is selected (e.g. If Hilton, then "The Plaza, the Waldorf Astoria. If Marriott, then Marriott Marquis, Marriott Convention Center etc etc...) on list #1. And Last but not least, the 3rd list would...

Excel Running Wild After Update
I ran the Office update via Auto Update. Word and Powerpoint seem fine but Excel attemtps to open every compatible file (and a few that aren't) when I start it up. After it overdoses on files it fails and closes. Any ideas on what to do other than reverting to an older version, pre-update? Wild guess: Look in Applications:Microsoft Office 2004:Office:Startup:Excel: . Are there a bunch of random files in there? I think Excel would try to open anything in there on launch. On 3/23/06 9:04 AM, "Jeff Fread" wrote: > I ran the Office update via Auto Update. Word and Powe...

Can I do this query in one step?
Suppose I have a talble like this, each record has a unique ID. All"A"s or "B", "C"s should have only x or y or z property attached. Iwant to find A, B or C which has more than one properties.1 A x ...2 A x ...3 A x ...4 A y ...5 B x6 B y7 C z....resutls would look like:A xA y....I think I can do this in two steps or with a subquery. Then I thoughtof self-join but didn't figure out how to use it in this case.Thanks a lot! SELECT DISTINCT and do not include the record ID perhaps? Or is the record ID vital?"muster" <muster@gmail.com> wrote in me...

Macro Help #18
Well I think this is a macro. I have searched my excel book and i can not seem to figure out what to do. I want to write something that if this statement is true, then the font color changes. But here is the potential problem. THe location of the text will change. So i need it to search for this text and then change the color. Does that make sense? Is this possible? If this can be done it will really help me. Thanks in advance. This is exactly what i am trying to do. You type in a persons name into cell a1. you also type a name into cell a2. These names will transfer over ...

Run series of query -- I don't want any pop-up
I have generated 4 queries (1. empty all, 2. import data, 3. generate information, 4. Make a new table) I've created a button to run these 4 quries sequencially. ------------------------------------------------------------------------------------ Problem: MS ACCESS pops up "confirmation dialog" to ask me whether to delete/modify the table. ------------------------------------------------------------------------------------ Question: I don't want any question to be asked. I just want those queries to be executed once i press my button. -------------------------------...

Newbie needs help with new Exchange 2003 installation
I am TOTALLY new to Exchange. We just installed Exchange Server 2003 on a Windows 2003 domain. I have one (test) account set up and it appears to work OK but I keep getting an error message that says "Task "Microsoft Exchange Server" reported error (0x8004010F): 'The operation failed. An object could not be found.' I know, I know...How could I possibly not understand THIS message? I appear to be getting mail but I' can't be sure. How can I check to see what's causing this (and more important...what object it's looking for?) Thanks for any help! -Fran- ...

I Need an answer for this Formula
I am using excell 2007 & this formula works {=IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} When i upload this workbook to a 2003 version this formula does not work I get {=_xinfl.IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} or somthing close to this Then in the cell with this formula has a NAME error WHY & HOW could i fix The IFERROR function can only be used in Excel 2007. Try this array formula** : =LOOKUP(1E100,CHOOSE({1,2},0,AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5)))...

insert text to different pages and update
Is there a way to insert a text cell from one page put into another page and when the first page cell is updated the second page cell will also be updated. Using the = sign just displays the formula, not the text. :confused: Thanks Frank -- fwburkey ------------------------------------------------------------------------ fwburkey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30314 View this thread: http://www.excelforum.com/showthread.php?threadid=505331 Not clear what you are doing wrong. the formula should look lik =Sheet1!A1 Make sure there is noth...

Help
Hi... I tried to work on Word today for first time on newish Mac and realized none of my Office applications work. When I double click on Word I can see something happening as I get the circle of colours twirling, but half an hour later it still hadn't opened. I just have to do this as quickly as possible as I have a deadline for what I have to do Monday at 12. I've tried reinstalling the software, but that didn't do anything for me. Mind you I know absolutely nothing about installing anything on Mac computers so I could be doing it all wrong. Do any of you have any idea what c...

February updates on EPM (WSS+PServer+MSProject)
Hi, February updates Links are available on http://epm2007.wordpress.com You can comment and poll to give feed back on post installation. Regards, Xavier HOVASSE www.ixila.com Hi Sam, If you have sufficient permissions: In ProjProf: File -> Open ... -> Retrieve the list of all projects ... Right click on project name and select Rename. In PWA: Project Center -> mark project in any column except Idicator and Project Name. Click on Edit Project Properties. Change name and click on Save and Publish. Regards Barbara Am 01.03.2010 08:58, schrieb Xavier HOVASSE...

Macro help with saving a spreadsheet with date and time in it
Can someone help me with some code that would save a file name as "schedule-mm-dd-yyyy-hh:mm"? Thanks, Alan Alan, how about something like this Sub Save_As() ActiveWorkbook.SaveAs Filename:="Schedule " & Format(Now, "mm-dd-yyyy-hh-mm") & ".xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Fee...

Help!
We went live with 4 decimals on our functional currency and we now want to reduce them to 2 decimals. All of our transactions so far are in 4 decimals. Can anyone guide me into doing this? thank you Use the Change Decimal Places window to change the number of decimal places used for keeping quantity or currency amounts for items. You might need to complete this procedure if an item was set up improperly, or if you’re changing the units of measurement for an item. NOTE: If an item is on an unposted transaction, you can’t change its decimal place settings. Ensure you have a complete ba...

Need to show file properties dialog box
Hello I need to show the shell's file properties dialog box. I tried converting old vb6 code into vb.net but it gives me error. Here's my code: Private Structure SHELLEXECUTEINFO Dim cbSize As Long Dim fMask As Long Dim hwnd As Long Dim lpVerb As String Dim lpFile As String Dim lpParameters As String Dim lpDirectory As String Dim nShow As Long Dim hInstApp As Long Dim lpIDList As Long Dim lpClass As String Dim hkeyClass As Long Dim dwHotKey As Long D...

updates 05-29-10
I keep getting configuering updates when I turn on anff my computer.Also cant search for updates and there is no record of any updates being downloaded gerry wrote: > I keep getting configuering updates when I turn on anff my > computer.Also cant search for updates and there is no record of any > updates being downloaded When was the last time you freshly installed Windows ME? -- Shenan Stanley MS-MVP -- How To Ask Questions The Smart Way http://www.catb.org/~esr/faqs/smart-questions.html ...