docmd.runcmd SQL functions

I have a table with fields a,b,c,d. If I have a row (x) where x.d is
blank, I want to search the table and see if there is a row (y) such
that x.c=y.c and x.b=y.b and if y.d is not blank, then I want x.d to
be filled in with y.d.

I can't seem to figure out how to start this.

Thanks

Carl
0
Carl29464
12/19/2007 5:18:45 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
720 Views

Similar Articles

[PageSpeed] 20

Frankly, you don't want to do this.  You have an un-normalized data model 
and you are storing redundant data.  If b and c determine d, then that 
should be stored *once* a record in its own table.

-- 
--Roger Carlson
  MS Access MVP
  Access Database Samples: www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:
  http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


"Carl29464" <carlvh@bellsouth.net> wrote in message 
news:6946ac87-1039-4e00-9565-328f744c5ff6@s8g2000prg.googlegroups.com...
>I have a table with fields a,b,c,d. If I have a row (x) where x.d is
> blank, I want to search the table and see if there is a row (y) such
> that x.c=y.c and x.b=y.b and if y.d is not blank, then I want x.d to
> be filled in with y.d.
>
> I can't seem to figure out how to start this.
>
> Thanks
>
> Carl 


0
Roger
12/19/2007 5:57:55 PM
I agree with you however I am trying to massage a table I was given...
Sometimes flat files work fine for quick and dirty work.

Carl
0
Carl29464
12/19/2007 6:04:39 PM
True, but what if you have records z and y that have identical values of b 
and c, but different values for d?  Which record wins?  Not only do flat 
file store data redundantly, but you have real data integrity concerns.

Nevertheless, you could use a correlated update query.  Something like:

Update Table1 as T1 Set d = (Select d From Table1 as T2 Where T1.b = T2.b 
And T1.c = T2.c)
Where T1.d Is Null;

Disclaimer: This was written off the top of my head.  You also left off a 
lot of details, so it may not apply or be complete.  For instance, the above 
will fill ANY d that is null.  If you wanted a specific row filled, you 
would add those values to the Where clause:

Where T1.d Is Null And T1.b = "zzz" and T1.c = "yyy";

But it should give you a starting point.

-- 
--Roger Carlson
  MS Access MVP
  Access Database Samples: www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:
  http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

"Carl29464" <carlvh@bellsouth.net> wrote in message 
news:3973cf13-82e1-49ac-b386-0e28d8aabec9@e10g2000prf.googlegroups.com...
>I agree with you however I am trying to massage a table I was given...
> Sometimes flat files work fine for quick and dirty work.
>
> Carl 


0
Roger
12/19/2007 6:38:12 PM
I like the disclaimer, can I have your permission to use part of it in my 
signature block?  ;-)

-- 

email address is invalid
Please reply to newsgroup only.



"Roger Carlson" wrote:

> True, but what if you have records z and y that have identical values of b 
> and c, but different values for d?  Which record wins?  Not only do flat 
> file store data redundantly, but you have real data integrity concerns.
> 
> Nevertheless, you could use a correlated update query.  Something like:
> 
> Update Table1 as T1 Set d = (Select d From Table1 as T2 Where T1.b = T2.b 
> And T1.c = T2.c)
> Where T1.d Is Null;
> 
> Disclaimer: This was written off the top of my head.  You also left off a 
> lot of details, so it may not apply or be complete.  For instance, the above 
> will fill ANY d that is null.  If you wanted a specific row filled, you 
> would add those values to the Where clause:
> 
> Where T1.d Is Null And T1.b = "zzz" and T1.c = "yyy";
> 
> But it should give you a starting point.
> 
> -- 
> --Roger Carlson
>   MS Access MVP
>   Access Database Samples: www.rogersaccesslibrary.com
>   Want answers to your Access questions in your Email?
>   Free subscription:
>   http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
> 
> "Carl29464" <carlvh@bellsouth.net> wrote in message 
> news:3973cf13-82e1-49ac-b386-0e28d8aabec9@e10g2000prf.googlegroups.com...
> >I agree with you however I am trying to massage a table I was given...
> > Sometimes flat files work fine for quick and dirty work.
> >
> > Carl 
> 
> 
> 
0
Utf
12/19/2007 7:55:01 PM
Reply:

Similar Artilces:

How convert Docmd.Domenuitem
I'm converting a 97 db to 2000. A form has a button that executes a Docmd.Domenuitem command. This is supposed to invoke Find and Replace. It does, but the subject is grayed out. I need the find to be based on the control where the cursor was when the button was pressed. Any ideas? Access help in 2000 does not even mention this command neither does my Access book. Maybe you need to do Screen.PreviousControl.SetFocus DoCmd.RunCommand acCmdFind or DoCmd.FindRecord On Thu, 29 Sep 2005 13:43:03 -0700, "mscertified" <rupert@tigerlily.com> wrote: >I'm converti...

SQL to find FileNum in one table not in another
I am now ready to start validating the data in my database. It's extracted from several Excel Tables. One problem I'm encountering is that I need a One-to-Many relationship between "EmpData" (One) and "CurrentData" (Many). But I've got more records in "EmpData" than in "CurrentData" There needs to be at least one record in "CurrentData" (715) for every record in "EmpData" (734). I've already run SELECT COUNT (*) FROM eachtable and SELECT COUNT (DISTINCT *) FROM eachtable to verify that there are no duplicates....

Hardware Requirements for SQL and Application Server
I am wanting to purchase two new servers, one as an application server that will house Great Plains and Citrix, and the other will be for SQL. I read the requirements from Microsoft, and I am not clear as to what is the ideal configuration for the hardware. Does anyone have a similar setup and could tell me what I would need for the following: 40 Users 1000-2000 transactions Financials Field Service - Returns Mgmt. Manufacturing Distribution Any suggestions on how to make this configuration work for now and be upgradable are cool as well. Thanks for your support! ...

sql view
Does anyone know why the SQL view may not be available to use for a table of form? I have read that you can get the SQL view by clicking on the view button drop down list, but it isn't there for some reason. The only view options showing are Design View, Datasheet View, Pivot Chart View, and Pivot Table View. I don't see the SQL View option. Can you tell me how to find it? Thanks! Yes, Just bring up the property sheet for the form (while in design mode). Then select the data tab. Now, click on the Record Source property field. Then, you can view the sql by clicking on the [...] b...

SQL Server 2008 Extended Events
I am using SQL Server 2008 Std SP1 on a Windows 2008 std 64-bit server. I am trying to understand and use Extended Events. I have created a session that is to monitor tempdb usage when ever there is a physical page write. I want to capture the query that is causing this action. Below is my session defintion: CREATE EVENT SESSION Monitor_tempdb_usage ON SERVER ADD EVENT sqlserver.physical_page_write ( ACTION (sqlserver.nt_username, sqlserver.sql_text, sqlserver.client_hostname, sqlos.task_time, sqlserver.username, sqlserver.session_id) WHERE sqlserver.database_id ...

laying out iscsi drives for SQL
Is there a best practice for laying out a disk structure on a Windows Target server for an SQL server initiator's drives? Point being, when configuring the drives on the iSCSI Host, you format them as the VHD, then should you format the drives the same way as on the initiator, in this case an 2008 SQL server? We have formatted the drives on the iSCSI host with default settings, and then on the initiator, our SQL server, we format the drives with a 64K allocation size. We are trying to determine the optimal size of formatting the disks. Should both servers’ drives be formatt...

How to call Web service function using MFC
Hi to all http://localhost/cadpublisher/Publisher.asmx is the web service http://localhost/cadpublisher/Publisher.asmx?op=Publish is the Publish function. I want to call a web service function Publish( CString szcadfile, CString sz3Dfile). I dont want to use soap sdk. Does any one help me to solve the problem? Thanks in advance Vaibhav ...

Conversion from Jet to SQL
I have a question on the conversion from the normal Jet engine Access to SQL. Our DBA is defining all of our current production tables to SQL and all of the table names are beginning with 'dbo_'. I was wondering if we can assign an alias to the table so we do not have to change all the queries to the new table name. Or if there is a better/different way to accomplish the conversion, I would love to hear that as well. Thanks for all your assistance. Just rename the tables in the Access front-end. The links will still work. "Bunky" <Bunky@discussions.microsoft....

kind of character based macro functionality
Hello, is there a way to realize macros that replace function-specific lines on character-base, so the macro is inserted and compilation is done afterwards? exp: UINT f() { // ... // part to be replaced by a macro, lets call it "ExitTh" pMyDlg->SetDlgItemText(IDC_TAG,"..."); EnableControls(param); return(0xFFFFFFFF); // ... } The quesiton is incoherent. The example seems to be unrelated to the question. Try again. joe On Fri, 20 Feb 2004 15:24:55 +0100, ZZT <a@b.c> wrote: >Hello, > >is there a way to realize macros that replace function...

Is it true about DoCmd.SendObject
That DoCmd.SendObject will find your default Mail client? -- Thanks in advance for any help with this......Bob MS Access 2007 accdb Windows XP Home Edition Ver 5.1 Service Pack 3 "Bob Vance" <rjvance@ihug.co.nz> wrote in message news:u91TwlG3KHA.4964@TK2MSFTNGP05.phx.gbl... > That DoCmd.SendObject will find your default Mail client? > > -- > Thanks in advance for any help with this......Bob > MS Access 2007 accdb > Windows XP Home Edition Ver 5.1 Service Pack 3 > > It's true if your default Mail client is Outlook :~) ...

Getting around limitations with MIN function
I have a range of calculated values and want to display the smallest value in an adjacent cell. However, some of the cells (correctly) return #DIV/0! errors and, as the help file explains, the MIN function subsequently returns this same error. Can anyone think of a way of getting around this problem? I imagine there must be a way to only count valid numbers or something similar. Thanks in advance! Paul It isn't correct to return errors! You can avoid the #DIV/0 error (which comes about by trying to divide by zero) by adjusting your formulae like this: =IF(divisor=0,"error",y...

Adding/Updating a record in an SQL Server table using ODBC
Hi, This is a beginner's question. I tried to write an MFC aplication using SQL Server database as the back-end. I'm able to retrieve data from the required table but i'm unable to add/update records in the table as i'm getting a "Recordset read-only" error. What should I do? Is it required to add additional code to make it work? Please reply asap as this is part of my final year college project and time is running out. Thanks, Satish. check you have a primary key on the table. "Satish Chandrasekar" <yourdisplayname@discussions.microsoft.com&g...

SQL 2000 SP 4 and GP 8.0
Hello: Is it true that there is a problem with installing and "using" SQL Server service pack 4 and Great Plains 8.0? If so, is MBS going to release a new GP service pack in order to alleviate that concern? And, again if so, what would happen if you installed service pack 4 in a GP 8.0 environment specifically? What problems would occur? Thanks! childofthe1980s I have not seen any problems with SQL Server 2000 sp4 and GP 8. -- Charles Allen, MVP "childothe1980s" wrote: > Hello: > > Is it true that there is a problem with installing and "usin...

Help with "Form DoCmd.Open and DoCmd.Close
The following two lines of code is not operating correctly. Private Sub cmdChangeVendor1_Click() DoCmd.Close acForm, "Invoice by Market Reviewer" DoCmd.OpenForm "Selections Vendor and Reviewer", acNormal End Sub When I click on the cmdChangeVendor1 button, I expect the form "Invoice by Market Reviewer" to close and the form "Selections Vendor and Reviewer" to open. But that is not the result I get. The form "Invoice by Market Reviewer" closes but that is about it. Nothing else opens. I have two forms that behaves this way. I have ...

SQL and object model
Hello, is it possible to access from a VBA Macro directly on to the object-model? And which is the better way to work with the object-model or with SQL. Like for example you parse an email and you want that data in MS CRM, is it to access SQL and put the data in or to work with the object-model? Thanks a lot Vin Rule of thumb when it comes to MS CRM is that if you are going to be updating data, always try to use the SDK API's. Updating the database directly can cause issues with the data security and should be a last resort for certain updates that you have already verified will ...

Forcing Security Service Startup after SQL Server
Does anyone off the top of their head know the command line command to get the CRM Security Service to not start up until after SQL Server has already finished starting up? Alan Here you go: From command line at the specific path ("...\Microsoft CRM\Server\bin"): CrmSecurityService.exe -r -s Frank Lee Workopia, Inc. >> Other Microsoft CRM Online Forum Resources: http://www.workopia.com/Links.htm >-----Original Message----- >Does anyone off the top of their head know the command line command to get >the CRM Security Service to not start up until after SQL S...

SQL Statment for Reorder Points/Restock Level
Does anyone have, or know what SQL statement will work to copy Reorder Points/Restock Levels from an existing store into another store in HQ Admin? I have a store that I want to have the same reorder and restock points as another store and I would like to just run one/two SQL statements to copy all the values. Thanks, Chris Select ItemID,SnapshotReorderPoint,SnapshotRestockLevel from itemdynamic Into #MyTemp Where StoreID = X Update ItemDynamic Set ItemDynamic.ReorderPoint=#MyTemp.SnapShotReorderPoint ,RestockLevel=#MyTemp.SnapShotRestockLevel from ItemDynamic,#MyTemp Where Item...

When I close a form with DoCmd.Close I get a parameter popup
This is a repost. I will try to explain the problem a little better than I did the last time. When I execute DoCmd.Close on one of my forms I get one of those little unknown parameter popups, the kind you get when there's something wrong with your query. But all I'm trying to do is close the form, not read any data. This popup appears to be gernerated by the row source query in a list box on the form. This row source query references another text box on the form and this appears to be what the popup is asking for. But there's a value in this text box. And why should th...

Rebuild Index on SQL 2005
The drive that holds data files is about 136gb. Originally, all data files took up under 40gb of space. Mistakenly, we ran index rebuild job with fill factor of 10%. The data files grew to 126gb. If I rebuild the indexes using 90% fill factor, do I risk running out of space? In other words, are indexes rebuilt in sequence or in parallel? Does rebuild index release space held by the old one and then recreates or after each index rebuild? Thanks in advance The new index is created first, and only after that is the old removed. Note that this is per index, so if you use s...

If Function Function
okay. So I am trying to workout a formula to compute a commision earning. Basically, if X amount of new business is made, then X*Y (corresponding Commision rate percentage), but if new business =<X, then X*Y (Commision rate percentage that is true). Problem is, it's super confusing...anyone have any suggestions? See if this helps: http://mcgimpsey.com/excel/variablerate.html -- Biff Microsoft Excel MVP "aetejada" <aetejada@discussions.microsoft.com> wrote in message news:AA4E55F7-9435-4BB6-837A-416A59BB72A0@microsoft.com... > okay. So I am...

CRM 4.0 Connector for SQL Server Reporting Services
Do we have to install the connector if the SQL database, report server and CRM installation are all on one box? Reports will list in IE, but when I click on the Reports link in CRM I just get - "Error - and error has occurred" message; no further information. I thought reports were working fine when CRM was initially installed - no changes inbetween. THANKS FOR ANY HELP!! According to the installation document, you don't need to install the CRM 4.0 Connector if all the components are on the same box... In order to find the real problem, login to the server, open Program...

adding sql server compact in platform builder wince5.0
i would use sql server compact in .net (c#) what i have to add from catalog ? - SQL Compact - SQL Mobile - SQL Server CE 2.0 i tried on a device to create a db via SqlCeEngine.CreateDatabase on sotrage card but i get error: "SQL Mobile encountered problems when creating the database" HResult : -2147024882 NativeError : 28558 there is enough space on storage card .... You add what you want to use. SQL Compact is the most-recent of those. I don't usually build the database into the OS, myself. I feel like I'd rather install it to a completed device. ...

Backup of Remote SQL Server using VB Net Code
Jan 26, 2010 Hi all I am using VB Net 2008 and SQL 2008. I am taking a backup of a local SQL Server Database using the following code: Dim backup As SQLDMO.Backup = New SQLDMO.BackupClass() Dim sqlserver As SQLDMO.SQLServer = New SQLDMO.SQLServerClass() sqlserver.LoginSecure = False sqlserver.Connect("MIKETI-PC\sqlexpress", "sa", "sapassword") backup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database backup.Database = "TestDataBase" backup.Files = "C:\SQL_BACKUP\Test...

Problem to use fmode function
Hi, I have a problem to use fmode function in visual C++ If i use : fmode(-2,10) i obtain 0. But in using Microsoft Excel MODE function like this : MODE(-2;10) i obtain 8 Where is the problem. Thanks in advance ...

Change SQL compatibility level to 2005 after moving from SP2 to SP3?
I just noticed well after moving to SP3 that the SQL compatibility level for our upgraded SP2 database is on 80 (SQL2000, owing to SP2's MSDE) instead of 90 (SQL2005). We do have the database in SQL, not MSDE/WID. I'm wondering if abruptly switching is apt to cause a problem. Not changing it is probably fine, from what I've read, but it would be nice to do so, I think, in case we ever try to use any of the new functionality. SBS2003R2 Milhouse Van Houten wrote: >I just noticed well after moving to SP3 that the SQL compatibility level >for our upgraded S...