need some SQL Update Help

I have a Access 2000 db and I have these 2 tables. 

ArchInvoices
---------------
InvoiceID - Primary Key - Autonumber
InvoiceNumber - long
SaleType - char(1)
TransactionDate - DateTime
Discount - Integer
WholesaleFlag - Boolean
PaymentMethod - Char(6)
CustPONumber - Char(30)
ShortName - Char(30)
MdCountCode - char(2)
TaxFreeID - char(30)
Paid - Boolean
FestInvoiceNumber - long

The second Table is:

ArchLineItems
---------------
TransactionType - char(1)
Units - integer
UnitCost - Double
BottleSize - Double
LineItemId - PrimaryKey - Autonumber
InvoiceNumber - Long - Foreign Key Field from ArchInvoices
Productcode - char(6)
TransactionDate - DateTime
MdcountyCode - char(2)
ShortName - char(30)
Wholesalerflag - Boolean
Discount - integer
TaxFree - Boolean

Now, the two tables are in a 1 to Many relationship. For every 1 ArchInvoice 
record there are Many ArchLineItems records.

Now, I need to take the ArchLineItems.TransactionDate and set it to the 
ArchInvoices.TransactionDate.

I have tried many update statements and Access keeps on giving me an error 
saying missing 1 parameter. I need some sort of Update SQL that will do this. 
Can anybody help. Yes, I know the tables need to be normalized. Thanks in 
advanced.


0
Utf
9/25/2007 1:06:02 PM
access 16762 articles. 3 followers. Follow

1 Replies
532 Views

Similar Articles

[PageSpeed] 46

On Tue, 25 Sep 2007 06:06:02 -0700, Ed Cohen
<EdCohen@discussions.microsoft.com> wrote:

Create a new update query. In the QBE window select both tables. Join
them by InvoiceNumber. Drag LineItems.TransactionDate to the grid
below. Set its value to ArchInvoices.TransactionDate

Indeed, that's a terrible database design.

-Tom.


>I have a Access 2000 db and I have these 2 tables. 
>
>ArchInvoices
>---------------
>InvoiceID - Primary Key - Autonumber
>InvoiceNumber - long
>SaleType - char(1)
>TransactionDate - DateTime
>Discount - Integer
>WholesaleFlag - Boolean
>PaymentMethod - Char(6)
>CustPONumber - Char(30)
>ShortName - Char(30)
>MdCountCode - char(2)
>TaxFreeID - char(30)
>Paid - Boolean
>FestInvoiceNumber - long
>
>The second Table is:
>
>ArchLineItems
>---------------
>TransactionType - char(1)
>Units - integer
>UnitCost - Double
>BottleSize - Double
>LineItemId - PrimaryKey - Autonumber
>InvoiceNumber - Long - Foreign Key Field from ArchInvoices
>Productcode - char(6)
>TransactionDate - DateTime
>MdcountyCode - char(2)
>ShortName - char(30)
>Wholesalerflag - Boolean
>Discount - integer
>TaxFree - Boolean
>
>Now, the two tables are in a 1 to Many relationship. For every 1 ArchInvoice 
>record there are Many ArchLineItems records.
>
>Now, I need to take the ArchLineItems.TransactionDate and set it to the 
>ArchInvoices.TransactionDate.
>
>I have tried many update statements and Access keeps on giving me an error 
>saying missing 1 parameter. I need some sort of Update SQL that will do this. 
>Can anybody help. Yes, I know the tables need to be normalized. Thanks in 
>advanced.
>
0
Tom
9/25/2007 2:50:22 PM
Reply:

Similar Artilces:

macro needed
hello listers to begin with i have no great excel skills what I am looking for is a macro that I can easily use to send mail to a list of users I have made a web page that students sign up on http://biosciences.utoledo.edu/prs enter there info when they press submit the data is saved to a text file that can be opened as a csv file in excel I would like to have a macro go row by row down the page and extract the email from column G and insert that in to an email and also the students name from coulmn B. The csv file is set up like this ID Fname Lname StudentID TransmitID Section e...

Office X 10.1.6 Update: Fonts missing?
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3182711903_8674241 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit After updating to 10.1.6, some fonts are missing (KidPrint, Lucida handwriting, etc.) Any ideas? --B_3182711903_8674241 Content-type: text/html; charset="US-ASCII" Content-transfer-encoding: quoted-printable <HTML> <HEAD> <TITLE>Office X 10.1.6 Update: Fonts missing?</TITLE> </HEAD> <BODY> <FON...

Need VB code to edit cell values
I need some VB to do the following please (My VB is not good enough yet) Starting from the active cell (call it the StartCell) In the cell to the left.. ThreeChars = Value.right(3) (the last three chars) Value = Value - last three characters (assume value is text & at least 3 chars present) In StartCell.. value = ThreeChars (as text, overwrite any contents of StartCell) move active cell one down from StartCell Examples of before/after -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Stephen Ford" <Stephen_Ford_no@spam_uwclub....

Updating PO Line Qtys should not remove existing AA with 100% Assi
Assuming AA is setup and you have at least 1 Dimension linked to an Account, and that Dimension has a Dimension Code attached. 1. Go into PO Entry 2. Enter a line item 3. Go into Purchasing Item Detail entry and make sure your Inventory Account is an account linked to your AA Class and therefore Dimension. 4. Enter AA information against this account on the PO - using the default of 100% Assignment. 5. Return back to the PO Entry window. 6. Change the Qty on the PO Line to be 1 greater (or less). Press tab to move off the field. Actual Result: The already assigned AA data for that line...

Routing Engine not starting, please help ASAP
Exchange and Windows 2003 I cannot get the Exchange Routing engine to start up. Upon startup I get an event ID 7023 telling me that the path is not found. Everything else works and starts up just fine, people can open mailboxes, but cannot send/receive emails. I read online that it sounds like the metabase.bin file is corrupted. I uninstalled/reinstalled IIS, reinstalled Exchange 2003, and have reinstalled Exchange 2003 SP1, everything works except that I still cannot get the routing engine to kick back on. Any suggestions! Thanks! Was this an OEM preinstall? I recently bought a ser...

Help Microsoft Office 2003
After now using Microsoft Office 2003 for about 3 months now, have suddenly noticed that my email messages have just started showing strange symbols for certain punctuation. This does not show as I type... but the recipent sees these symbols when the message is received. I can see it when they reply back. The strange symbols show for the apostrophe and sometimes for the exclaimation. I do not recognize the symbols. I do not have caps lock on, do have number lock on. None of the symbols show on my keys. Help?! Thanks! Kim it's caused by character encoding. What email client and...

.pst folder help
How do I put a .pst folder in My Documents for constant up to date copying? Mine seems to be in a folder called Office Data File which I cannot open but do not see any pst file...can someone kindly help me out here? Thanks! Right click the pst in outlook and select properties and then click Advanced You will see the location of the file. Depending on your view settings it may be hidden. Close the pst, (right click it, close) Move the file to the location you desire. Open Outlook select File - Open - Outlook Data File Browse to the file and select it. OK Done Regards Mark Dormer &quo...

Help with macro to choose printer
Hi, I am new to macros in Excel and this is what I did to print a worksheet: ------------------------------------------------------------------------ Range("A1:W35").Select ActiveSheet.PageSetup.PrintArea = "$A$1:$W$35" Selection.PrintOut From:=1, To:=1, Copies:=1, Collate:=True End Sub ------------------------------------------------------------------------ Works great except that I want to select the printer to use and the macro won't stop at the point to choose printer. Any way to work around this? Any help is greatly appreciated. Emilio Emilio,this wil...

Help with an expression to calculate a profit from one of three fi
Using Access 2003 SP3 - in Forms I know I'm probably asking a very complex question, so a very big THANK YOU to whomever can help. I am doing a detailed inventory and need help with an expression in my form that will calculate my profit based on one of three fields. Only one field would have the end data. To clarify what I am working with: Most stock items come by the "unit" and contain multiple "subunits" which, in turn, contain smaller "pieces." However, some "units" only come with "subunits" and some only come as &quo...

Simple update Query
Hi, I'm sure I've done this in the past but can't figure how. I have 2 tables "A" and "B" both with the following fields Town; Council I want to update Table "A"'s Council field with the Council information in table "B" Could someone please give me the SQL code to do this. Thanks in advance UPDATE TableA INNER JOIN TableB ON TableA.Town = TableB.Town SET TableA.Council = TableB.Council. IF you can only do this using design view -- Open a new query -- Select the two tables -- Set up a join by dragging from town to town -- add the Cou...

Need a code
I need a code that does the following: If C3>D3, B3 value will start blink and if C3<D3, E3 value will start blink. Any help please. Thanks in advance. Personally, I would recommend against blinking text, but if you insist on employing it, this website should help you get started... http://www.cpearson.com/Excel/BlinkingText.aspx NOTE: Pay particular attention to the second of the two points delineated in the Summary section at the bottom of the webpage. -- Rick (MVP - Excel) "MAX" <MAX@discussions.microsoft.com> wrote in message news:C7E6...

Help with Lookup #2
I have an excel worksheet that has approximately 20,000+ records. I would like to classify these recorded based on two columns. The first column's name is "YB Description" and the other is "Sold Date". The YB column mainly contains 4 digit numerical values, with approximately 300-400 records that are text, ie. "classified", "not verified", "public use". The Sold Date column is pretty self-explanatory, all the records are dates with the following formatting - M/D/Y. What I would like to do is create a third column called YB/Sold that classi...

upgrade CRM 3.0 on SQL 2000 to CRM 4.0 on SQL 2008 (error)
I am trying to upgrade CRM 3.0 on SQL 2000 to CRM 4.0 on SQL 2008 and getting this error. (I know CRM 3.0 doesnt support SQL 2008, so a '1' step upgrade might have its risks). "Action Microsoft.CRM.Setup.Server.GrantConfigDBDatabaseAccessAction failed. The DEFAULT_SCHEMA clause cannot be used with a windows group or with principals mapped to certificates or asymmetric keys". I'm trying this in a virtual environment first. Any comments on this? Thanx Glen Hi Glen, Upgrade CRM 3.0 to SQL Server 2005 Evaluation . then upgrade to CRM 4.0 on SQL Server 2005. Take ...

Help with ACT! to CRM migration
Hi to anyone that can help. I am trying to do a trial data migration from our ACT! database to MSCRM to see what it will look like. I have installed the migration framework and appear to have CRM running fine on a server in our network (no errors on install and all windows appear ok). Since I do not have a strong database background I was wondering if someone could confirm what I believe is MS's way of migrating my data accross. I have exported the data from ACT! to a text file. I imported this into excel and repleaced the Free/busy data with a unique number for each record. I the...

Experts Needed for Color Coding
Hello: this ought to be a challenge. I have a spreadsheet with 10 columns Col A contains an ID There are various color coded cells in each; Row 1: B - C are red; F is yellow Row 2: C is green; E is yellow, etc. Row count could be up to 300 Objective: Before refreshing this report (from Access query) 1. identify cell color for cells in rows 2. After refresh, find row (per the ID field) and replicate the color Conditions: New unique rows may be added during the refresh; these will not be color coded The color is applied without consistent rules in many cases Rather than refreshing the repo...

Windows XP updates not successful
I am continually getting told by XP (Service Pack 3) that updates are ready for my computer. When I try and install them, I get this: "The following updates were not installed: KB971486, KB969059, KB958869." I went through my Windows Update history, and the first update that was unsuccessful was KB971486. So I downloaded this update to try and install it manually (with the "WindowsXP-KB931784-x86-ENU.exe" /o command), and I got a message saying that "Setup has detected that the Service Pack version of this system is newer than the update you are applying...

Help with Links to Files
Hello, I have a file in Word 97-2003 compatibility mode. When I select the Office Button / Prepare I see an option to Edit Links to Files. If I convert the file to 2007 format the option for Edit Links to Files is no longer there. Where did they go? thanks, mitch This is strange. The Edit Links to Files option should be available even if you save in Word 2007 format. Are you sure you didn't inadvertently break the link to the automation object? You could press Alt+F9 to see that the field code for the object is still intact. (Note that you can only see the field co...

Create/Update Excel Objects in Power Point
I developed an add-in in Power Point 97, 2000, XP and 2003 for creating Excel charts/tables and being able to update them. The code I used is below: Dim PPT_CHRT As OLEFormat Dim PPT_SLD As Slide 'ADD a chart/table PPT_SLD.Shapes.AddOLEObject Left:=var_chrt_L, Top:=var_chrt_T, Width:=var_chrt_W, Height:=var_chrt_H, ClassName:="Excel.Chart.8", Link:=msoFalse Set PPT_CHRT = PPT_SLD.Shapes(var_SHP_count_no).OLEFormat PPT_SLD.Shapes(var_SHP_count_no).Select 'EDIT chart/table Set PPT_SLD = ActivePresentation.Slides(var_SHP_SLD_no) Set PPT_CHRT...

Need help backing up OL2002 items (or folders)
HELP says: "If you want to create a folder to which you can drag items for safekeeping or to distribute to another computer. Create a new data file (.pst) and drag items to it. If you used the default name for the folder when you created the data file, the folder will be called Personal Folders . In the Folder List (Folder List: Displays the folders available in your mailbox. To view subfolders, click the plus sign (+) next to the folder. If the Folder List is not visible, on the View menu, click Folder List.), drag any item from your current folders to the new folder. Press CTRL...

NEed report of income and expenses broken down
I'm using Microsoft Money 2006 and I entered all of my checking account transactions for last year and categorized everything. Now I need a report of the income and of the expenses broken down by category, with the transactions listed. If I go to reports, all I can get is a pie chart or bar chart. I need a printed report with numbers to give to the accountant tomorrow. I was able to export a report to a spreadsheet, but all it did was show the total expenses in each category. What I need is a report showing the categories for income and for expenses, list the transactions in each ca...

Need to sort dates before 1900 in proper order
I need to sort dates before 1900 in proper order -- ones after 1900 are all fine but any dates like 03/29/1865 will always sort by month and day but NEVER the year except in the month area. Don't know if I am making sense -- but will be like: 03/29/1865 03/29/1866 03/29/1873 04/01/1863 04/01/1868 etc. Thanks, Judy M. I can provide a chunky workaround, until someone provides a better answer: Assuming your 5 sample dates are in cells A1 ~ A5, and that single digit months and days are always expressed with a leading zero: In cell B1, enter this formula: =VALUE(MID(A1,1,2)) In C1, ente...

Never ending Updating your accounts
Hi, I upgraded my money to Plus Home and Business (v17). Since then I can not use the software because it always "updating your accounts" I already done couple repairs of the money file, removed all items from home page. I do not really want to delete all my bills. Is there anything I can do else to stop this. This is some kind of bug that money folks should repair ASAP. Are you getting a message that money is updating accounts? I am having a problem when inputing a deposit or payment scheduled for about a month in advance. When the entry is made money freezes and I have ...

Can databases be merged to update data?
Recently, two employees worked on the same database at two different locations on two different workstations. After we entered data into the databases, we cannot seem to merge our information back into one database. Atleast the help files didn't provide a solution here. Can anyone direct us to the appropriate newsgroup if this isn't the site for the answer we are seeking? If you can answer our question and provide us with the information to merge our databases into one of the databases, please help. Thank you in advance. Hi Mark, Working with copies of your databases, u...

password
Hi I have problem with Microsoft Outlook XP. Very often Outlook won't remember the password. Thx I am having a similar problem. Using Outlook 2002 (XP). I have several POP3 accounts, and Outlook requires the passwords every time. I've tried everything, and it still forgets them. Thanks to anyone that can help. >-----Original Message----- >Hi >I have problem with Microsoft Outlook XP. Very often >Outlook won't remember the password. > >Thx >. > Earlier reply had the incorrect e-mail address. This address is correct. Jim V >-----Original Mess...

Help w/ Accounts
Hi, I have Outlook 2003, I used to have four email accounts in Outlook. One of those accounts was set up to check the email but leave the original in the server. It was working fine until all of a sudden it started downloading the same email twice and deleting it from the server. I deleted the account from Outlook, but it is still downloading the email from that account. I went to Email accounts and I don't see that particular email account listed there. Does anyone have any idea how to get rid of this "invisible" email account that does not show up in the email accounts an...