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
526 Views

Similar Articles

[PageSpeed] 30

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:

windows update 04-15-10
I am running windows vista on a MAC computer with VMWare Fusion. I cannot get the service pack 1 or internet explorer 8 update to run. i get error code 490 and 800704090 Too late now! Support for Vista Gold (no Service Packs) ended on Tuesday, 13 April 2010! Computers running Vista Gold "will no longer receive software updates from Windows Update" until SP1 has been manually installed (which will require several Prerequisite Updates to be installed first). See http://windowsteamblog.com/blogs/windowsvista/archive/2010/04/13/end-of-support-for-windows-vista-with-no...

write Macro or DLL to copy data from worksheet to memory
Hi, I would like to write some functions in Macro or DLL so that I can copy some data from Excel sheet into memory so that I can call back from other Excel sheets or macros. It works as a global variable array, something like that. Thanks If you must use global variables... Option Explicit Dim myArr As Variant Sub loadMyArr() myArr = Range(Range("A1"), Range("A1").End(xlDown)) End Sub The above code puts the contents of the current region of A1 in the active sheet into the variable myArr. myArr will be a 2-D array that you can loop through with code such a...

web address help
whats the web address for outlook so i can sign on? I have it stored on my home computer but dont have it here thanks Outlook doesn't have a web address unless you use an Exchange server. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com <anonymous@discussions.microsoft.com> wrote in message news:64da01c3e61b$58282fc0$a401280a@phx.gbl... > whats the web address for outlook...

help installing exchange 2000 in one of 3 sites
I have a domain cotoso.com installed and configured as AD for 3 companies interlinked together. and + a total of 3 DC in 3 different sites that are connected by VPN. i have exchange installed in each site connected on the same domain contoso.Local(+ E-mail -->contoso.com) + hosting their own companie domain name such as x.com or y.co or z.com for each company. I have a routing link configured + 3 administrative groups in exchange system manger for each company. one DC in one site that also has exchange 2000 installed on it has to be changed because of poor performance. now i'm ...

HELP!! Viewing email images
I have just recently switched to using Outlook rather than Outlook Express as my email program. I now find that any image attachments (.jpg, .bmp etc.) I receive are not visible onscreen as they used to be in Outlook Express. I have to click on each attachment icon to view... It's annoying, especially if I get a series of pics...I have to manually ope each one... Does anyone know how to set Outlook so that it shows me these images onscreen??? Many Thanks, Darren. ...

To write living will do I need special format
I just need to change and update a living trust You should consult someone with the appropriate legal knowledge in the jurisdiction in which you are domiciled. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "belladonna" <belladonna@discussions.microsoft.com> wrote in message news:F25A64CB-355F-44E9-A005-16AA61DA15A7@microsoft.com... > I just need to change and update a living trust ...

Need help getting this code to compile under VS.NET 2003 (compiles under VS6.0).
I'm tasked with converting a LARGE project from VS6.0 to VS.NET 2003. Here is a VERY scaled down version of an error I'm getting. I must be getting rusty, but the way to get around this compiler error is just not coming to me. If you need to compile this, just create a Win32 console app and specify to add support for MFC, then copy this code into the cpp file and press compile. class CLogItem : public CObject { protected: virtual BOOL CheckConflict(CLogItem* pItem) const; }; struct MapEntry; //forward declaration.. typedef CArray<MapEntry, MapEntry&> CMapEntryArray;...

how to query my web site from VBA and return a value to VBA
Hello All, From VBA I would like send a value to my web site, and have it return a value. I've learned how to use FollowHyperlink to send a value to an ASP script, but how can the ASP script send a value back to VBA?? Thanks, Brian Austin, TX You can use xmlhttp to make a request to your web page: '********************************************************* Sub Tester() MsgBox WebResponse("http://www.mydomain.com/myactualpage.asp? info=3Dblah") End Sub Private Function WebResponse(sURL As String) As String Dim XmlHttpRequest As Object Se...

help!! smtp protocol error occurred
exchange 2000 srv/windows 2k srv smtp protocol error occurred when trying to send mails. receives fine. Still waiting for some relavent information so we can help you solve your problem. >-----Original Message----- >exchange 2000 srv/windows 2k srv >smtp protocol error occurred when trying to send mails. >receives fine. > > >. > I had that problem, I telneted into the servers that the error occurred and found I was on a blacklist. Had to sumit to an open relay test, and bam, no more problems. ...

Help !!!!
I was migration my Exchange 2000 to 2003 I do my forest prep and domain prep But i can't create mailboxes ??? Why??? some documentation please... What actually happens? What error do you get? -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexchangeblog.com "A.dian´┐Ż" <Adian@discussions.microsoft.com> wrote in message news:31CB593F-F4DD-4CAB-9F4E-C2131BF7CF95@microsoft.com... > I was migration my Exchange 2000 to 2003 I do my forest prep and domain prep > But i can't create mailboxes ??? > Why??? > some documentation...

Is this a correct way of doing an optimistic update
Imagine two different users executing this code at the same time. I can not use the CRecordset Update Delete methods for this. // Check to see if version number has been incremented since this view read in // the client data CClientRecordSet rs(mConn); rs.m_strFilter = "ID = "; rs.m_strFilter += client->GetKey(); rs.m_strFilter +=" AND "; rs.m_strFilter += "Version = "; rs.m_strFilter += client->GetVersion(); // Assuming here, this is acting as a lock on tables referenced mConn->BeginTrans(); rs.Open(CRecordset::snapshot, NULL, CRecordset::re...

Help ! formatting data to text
I am creating data in an Excel spreadsheet. I then want to get that data into a simple text email. I have some problems and questions... 1) how do I get the columns of data to line up evenly when I copy the data to email text ? Keep in mind I need to be in simple text format, not HTML or rich text. Every time I do this, all columns become chaos and are unreadable. 2) Is there a simple way to automate the creation of an email from an excel file ? this is less important to me. Thanks in advance WxMachine #1. I think it may have to do with what email client you use, too. I copy and ...

form and query problem. please help.
All tables are linked with weak entities. However, when i enter data on the form I can't get it to let me enter more than one partipicant without access generating a new invoice id. however i need one invoice to many participants. It wont work and i have no idea what to do at this point. in addition the workshop will not let me add workshop to invoice. this is a small mdb and i'd like to email it to anyone who can assist me with the relationships as I think this is the problem but I don't know what to do. please help me. INVOICE invoiceNO - autonumber invoice prices WORKSHOP wo...

suggestion needed !!!!!!!!!!
In my company the want to calculate phone calls done by each sales person at the end of the day. Please give me suggestion how to implement this. Mahain, A quick solution would be 1) Create an advance find view based on the criteria you are looking for ie. Activity of the type phone call, Status = completed 2) Export the list to Excel (Dynamic pivot table/worksheet) 3) If pivot : Drag and drop relavent fields 3) If worksheet: filter by date and use sum. The advantage of dynamic excel report is it updates itself when ever you open the sheet. Hope this is helpful Venkat http://venkatbendalam...

Pulling counts out of query results
I have a query that has one field Type which is set to Count The query results are used in a report. The report has the fields in the detail section as TYPE and CountOfType (1 line only in the detail section) The report looks like this when displayed AS 28 AV 17 OR 5 I need to be able to get the individual AS No (28) and add it to another number elswhere in the report. How can I do this? I have tried using a textbox with an if function (if [type]="AS",CountOfType,0 but that did not work. Any help appreciated. Ray I think you will need to do it in the query. But try addi...

host unreachable after recent Windows Update
Exchange 5.5 on Windows 2000 Server, fully patched and running real swell until Wednesday morning. (Tuesday evening: applied the GDI+ patch: MS04-028.) Didn't find out until later today: internal e-mail is fine, inbound Internet e-mail is fine, but nothing goes out over the IMC. Each outbound e-mail generates Event ID 3010 warning message in the App log: host unreachable. But NSLOOKUP on that very server successfully resolve those same domain names, and ping gets replies. The only other software on this server: GroupShield 5.0 all up-to-date, and VirusScan 8.0.0 patch 1 push...

Integration Manager need a registration key for test company?
Hi all, Can anyone tell if I need the registration key for IM if I am doing some testings on Test company (World Online)? Also, can I specify the company database in IM for the data to import to? Please advise. Thanks You do not need a reg key for the Two Company, and if you are using the standard adapter the data will integrate into the Company that is open in Great Plains. You can Specify which database you want to integrate into by using the SQL Optimizer. You do not need great Plains open for this adapter. "Terry" wrote: > Hi all, > > Can anyone tell if I nee...

Updating Related Entities Via Workflow
I'm a complete newbie to workflows, so your help is much appreciated. In general my question is, can a parent entity be updated via workflow rules when some event occurs on a related child entity, or does this need to happen via assembly or callout? Specifically... I have a situation where leads come into our system from various places. Because I cannot add leads to a queue, I use a workflow to create a phone call for each lead and add it to a queue. Now, is it possible (through workflow) that when a user accepts the phone call activity from the queue, that the parent lead own...

Newbe help with a lookup
Hi, I&#8217;m trying to make some modifications to a salary matrix and I&#8217;m having trouble with a lookup. In column A I have a series of numbers from 0 to 30 representing pay grade steps where 0 represents a new employee and 30 represents an employee having completed 30 years of service. In column B I have an annual salary for each of the corresponding pay grade steps. I want to take a new salary and find the closest salary in column B and return the corresponding step number. For example if the new salary is $73,415.13 and the matrix shows step 15 is 73,205 and step 16 is 73...

HELP
We have an application that is used by over 8000 people worldwide. One of our users is just starting to have the following problem: Run-time error '-2147467259(80004005)': Method 'Add' of object 'CommandBarControls' failed I have read that this problem can be caused by trying to open the file in Internet Explorer. The user has told me that she has tried opening MS Excel and then the .xls file and she still gets the same problem when the file is trying to open. She is the only one that has had this problem. Could there be something wrong with her Excel settings or som...

Pay code update with Integration Manager
I am trying to do a Pay Code update for all employees using Integration Manager using the General Payroll Update. I am able to get the standard Pay Codes to update, but cannot seem to get the "Based On" Pay Codes to update. When I log into GP and view the Employee Maintenance and Go To Pay Codes, I see the standard update. However the Based On Pay Codes remain the old rates unless I select select the Pay Code and simply click Ok. Then the update automatically is brought in. How can I get these updates to take without having to visit each of the Based On Pay Codes and sele...

error message in Outlook after running windows and office updates
Hello, I am hoping someone can help me with this. When ever a few staff tries to send a new e-mail, a message "Do you want to save changes" come up and on the back of this message I see the spell checker box come up. The problem is that if you click on "no" on the error message, it comes up with another message something like "running out of memory". I just ran all the windows and office updates on all the machines in the company and so far two employees are having this problem. Is this something to do with the updates? Any suggestions? did you try ...

Do money updates install when you're not admin?
I noticed when I first start using money it told me to log on as administrator so that the updates can take effect. Is this true of all money updates? When I see the message "Money update recieved" does that mean it has also been installed or do I have to log in as admin to complete the process? In microsoft.public.money, Brian H wrote: >I noticed when I first start using money it told me to log on as >administrator so that the updates can take effect. Is this true of all money >updates? When I see the message "Money update recieved" does that mean it >has a...

Help with keeping inventory.
Hello Everyone. I was wondering if someone might be able to help me. I'm trying t come up with a way to keep inventory of product. Basically what I need is to keep track of the current inventory of items... and what I'd like is for different users to be able to ope the worksheet and enter the NUMBER OF PRODUCT they are removing at tha time.... and for the total inventory of each item to be adjuste accordingly. The only catch is - I'd like to be able to use the SAM CELL always -- that is - I want only one cell to be the one that i used to enter the amount of inventory being o...

Help with Outlook Automation
I launch Outlook form MS Access 2003 using the code under the double line. When I send the first email it works fine. But if I send another email everything freezes up. I have to use the task manager to Kill OUTLOOK. And then it is immediatly replace by another OUTLOOK session and the email window comes up. I was wondering if my code could be improved to prevent that from happening. Any suggestions please! RBollinger ======================================================= Private Sub Command11_Click() On Error GoTo Err_Command11_Click Dim stDocName, mTO, mCC, mSu...