update data from aggregate query

I have a table of PO's that includes a "flag" field to indicate whether a PO
is fully paid or not.  The PO table has a one to many relationship to a
payments table (multiple payments for 1 PO).

I've built an aggregate query that gives the total payments and last payment
date for the POs, but now I need to provide a user with an ability to update
the flag based on decisions from the aggregate query.

I'm building a form for this, but it I pull the flag field in the aggregate
query, it's not editable.  I could use a dlookup to reference the flag field
on the form, but the whole point is to be able to edit the flag field.

Any suggestions? 
MBR96

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200709/1

0
mbr96
9/20/2007 6:22:11 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1286 Views

Similar Articles

[PageSpeed] 27

Can you use a Form / Subform presentation?

The Form would be based only on the table, NOT on a join. The sub-form will 
be based on your aggregate-based query  (or computed expression). The Form 
will effectively 'join' the required fields though the parent/child form 
mechanic  (NOT through an SQL statement).  If so, you should be able to 
update the table represented by the form (but not anything in the subform).


Hoping it may help,
Vanderghast, Access MVP


"mbr96 via AccessMonster.com" <u8822@uwe> wrote in message 
news:7882d5c08253e@uwe...
>I have a table of PO's that includes a "flag" field to indicate whether a 
>PO
> is fully paid or not.  The PO table has a one to many relationship to a
> payments table (multiple payments for 1 PO).
>
> I've built an aggregate query that gives the total payments and last 
> payment
> date for the POs, but now I need to provide a user with an ability to 
> update
> the flag based on decisions from the aggregate query.
>
> I'm building a form for this, but it I pull the flag field in the 
> aggregate
> query, it's not editable.  I could use a dlookup to reference the flag 
> field
> on the form, but the whole point is to be able to edit the flag field.
>
> Any suggestions?
> MBR96
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200709/1
> 


0
Michel
9/20/2007 7:46:02 PM
The user needs to be able to type in various values into the flag field for
the PO, not just based on the payment data, but also other decisions in their
little brain (ahem).  I'm stumbling on how to give him the aggregate query
results (query is grouped on PO, sum on payments), but also show the flag
field and be able to edit it.

I put a field in the PO table to contain the PO total paid amount, but when I
try to update it using the grouped and summed query I get the "operation must
use and updateable query".

Still confused.  I could take the aggregate query and turn it into a make
table query, then use the resulting table to update the original PO table,
but that moves away from good table design (normalization).

Any other thoughts would help.  Appreciate this site alot!

MBR

S.Clark wrote:
>On a form, display the results of the queries.  On the form, add a button to 
>allow the user to "Mark as Paid". Use an Update query to update the field in 
>the table.
>
>> I have a table of PO's that includes a "flag" field to indicate whether a PO
>> is fully paid or not.  The PO table has a one to many relationship to a
>[quoted text clipped - 10 lines]
>> Any suggestions? 
>> MBR96

-- 
Message posted via http://www.accessmonster.com

0
mbr96
9/20/2007 7:49:23 PM
Reply:

Similar Artilces:

MSFlexGrig and SQL Query
How can i load dates in a MSFlexGrid Object whit a SQL Query???? ...

Push single contact field data into prepared excel workbook
I am new to macros but ameager to get some formulated to help run my small business. I have been trying to find some code which suits my requirement of pushing signgular fields from a contact into a pre-prepared excel book which is loaded with the next stage of macro-powered automation. I recieve enquiries both via email and by phone. I use my blackberry to save phone enquiries to address book and Anagram for Blackberry to capture the required contact data from our email enquiry form. Both are then synchronised with my Outlook contacts. Either from the point of creation, or through ...

Mapping Geographic Data
I want to create a data map and I need to have Microsoft Map installed. Apparently Microsoft Map was not installed when our IT Department installed Excel (I am running Excel 2003). I believe I ahve to rerun Setup to install Microsoft Map however my IT Department says they can't find Microsoft Map in the Setup file. Can any one help me tell them how to locate Microsoft Map in the Setup file? Hi, Map was dropped as a free add on to excel after xl2000, I think that's the version. Anyhow to get Maps in xl2003 you will need to buy the standard alone program. Cheers Andy Mike wro...

Workbooks that are not directly linked updating?
I have 3 workbooks: WB1, WB2, and WB3. WB1 and WB2 are both linked to WB3 but not each other. WB1 contains volumes and WB2 contains costs. When I open these two together and say no to updating the values change anyway. Any clue on why two workbooks update even if I say no or don't want to have WB3 open? -- MM ...

Linked cells, make the data go hard manually
I'd like to convert a cell linked to a previous day to hard numbers once a day. Is that possible? By 'hard number' you mean one that is no longer linked, you could copy, then paste as, paste value. HTH, Carole O "Johnny" wrote: > I'd like to convert a cell linked to a previous day to hard numbers once a > day. Is that possible? ...

Subform Data Entry Problems
I have a subform based on a query linked to the Form by "ContactID". The query works fine when run independent of the subform. I am able to enter all data fields and where necessary the autonumber function assigns properly. But when I try to use the subform all records related by my table "tblADDRESS" do not allow data entry (the fields are not locked). I imagine the ContactID Master/Child link in some way is interfering with the query in Form view, but I've tried every variation of join properties within the Query for the Subform and in the Query for the main...

Printing Autofilter Data
How do I print Autofilter data onto one sheet instead of a few lines on say 20 pages? In page Setup, select fit to print and select 1 page wide and 1 page high. -- Regards, Tom Ogilvy "Confused" <Confused@discussions.microsoft.com> wrote in message news:2C7DCB67-06B2-429C-99D9-D2B3A40FEF4A@microsoft.com... > How do I print Autofilter data onto one sheet instead of a few lines on say > 20 pages? Thank you! "Tom Ogilvy" wrote: > In page Setup, select fit to print and select 1 page wide and 1 page high. > > -- > Regards, > Tom Ogilvy >...

Parameter Query using date ranges
I am creating a parameter query where I want to pull data between 2 dates. In the date criteria of my query, if I write between #01/01/2008# and #01/31/2008# +1, I will get all data for January. If I use a form to enter the dates and have Between [Forms]![autoexec]![start] And [Forms]![autoexec]![end] +1 in the date criteria, I get an error. Does anyone know what I should put in the criteria to make the query work? Are you entering your dates as m/d/y? What happens if you try: DateAdd("d", 1, [Forms]![autoexec]![end]) Is the form open? -- Duane Hookom Microsoft Access...

&quot;New Web Query&quot; function
good day, are there any friends use New Web Query fuction under Data -> impor external data? My objective is to export (a list of stocks info) from web page t Excel spread sheet. e.g. the is is Microsoft's stock details (stock code: MSFT) http://finance.yahoo.com/q/ae?s=MSFT i can use "New Web Query" function to link this page to spread sheet. Question: how can I define a list of stocks (let say 10 differen stocks) that I want to monitor apart from creating 10 differen separate spreadsheets link with each individual stock code? any ideas are welcome thanks for your help ...

amend a PO with a query? Please help!
Hello, Our new warehouse manager entered, received and committed a PO from the wrong vendor - over 100 items! Does anyone know a way to either reverse the PO or change the vendor? Please help!!!! Thank you! diana Diana, You can reverse the PO by opening it again in receiving mode. Use the Quick Scan button and add an item to the PO (any item). This will 'unlock' or re-open the PO. You can now delete the item you just added back off. Next you would enter the quantity 'Received to Date' of each item into the 'Quantity Received' column as a negative. Fo...

Automatically pull data from one file to populate a template
Hi Everyone... Just beginning to get the hang of vba coding in excel. I would like to implement a new feature in a spreadsheet here at the office. We currently have a master records sheet containing vital information on unit processing. Each unit then has a sub file containing additional information. Currently one must populate the master record file. Then open a template and populate another file with the same information. I would like to streamline this process and have the data in the master record file automatically inserted into the template, allowing the user to only have to ...

Will Exchange 2000 SP4 update the VSAPI?
Exchange 2000 uses VSAPI v2.0. Will there be a SP4 (and if so when) and will it include VSAPI v2.5? Thanks David I don't know if/when any SP4 will be released, but if/when it ever does, I would be very surprised if VSAPI 2.5 were backported to it. -- Scott Schnoll This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email directly to this alias. This alias is for newsgroup purposes only. "David Beaven" <technet@ids.ac.uk> wrote in message news:exIS3v52EHA.1152@TK2MSFTNGP14.phx.gbl... > Exchange 2000 uses VSAPI...

SQL Server (XML Data) Through ODBC to MS Access
Hello, In one database in my company. Previously the data in SQL Server, the tables were simple text and number formats and it was easy for me to link the data through ODBC to MS Access and I could do all sorts of queries and calculations, etc. But, now (after several months) I realize that the DBA and Programmer changed, the data in SQL Server to be XML Data Type in the new setup. In SQL Server. dbo.FlightDocuments Table docID, int docInfo, xml docLogs, xml and so on. and I see the data in Access (through ODBC) for one of the fields of the above table like this: ...

Using SQL Query to mass update a field.
Having a problem in eOrder where the pictures are not showing up. I found out the Inventory image in internet information is mapped to a drive letter. If I remove the drive letter, I can see the picture in eOrder. I would like to mass update the SY01200 INET4 field and change all from f:\inventory pictures\image.jpg to image.jpg (where image.jpg is the file name). It sounds like I need to create a cursor script but am not sure how to do this. Can anyone help me with this? Thanks. Backup you data first. You might be able to use DTS to trim the data with a string transformation. Or you ...

Save data in a worksheet in fixed length fields...
I have data in a worksheet (Office 2000) which I would like to export in fixed length format for importing into another application. Is there a way to use column settings or a wizard to export the file in correct format rather than exporting and then having to do signficant editing before I can import the file into the other application? one way: http://www.mcgimpsey.com/excel/textfiles.html#fixedfield In article <9C97DB7F-65EB-4438-926E-23906D56E75D@microsoft.com>, dietzd <dietzd@discussions.microsoft.com> wrote: > I have data in a worksheet (Office 2000) which ...

I want to enter a customer # and retreive their data
How do I retreive data, which I've entered, from another worksheet when I enter a code or # that is specific to that data? Assuming you have a yable and the data is set up like code1 value value etc code2 value value etc and so on then you can use =VLOOKUP(C1,Sheet2!A2:C50,2,0) where C1 is the typed in lookup value (code) and A2:C50 the table and this particular formula will return the value from column 2 lookup VLOOKUP in help -- Regards, Peo Sjoblom Nothwest Excel Solutions www.nwexcelsolutions.com remove ^^ from email "It is a good thing to fol...

Aggregating Data in Fifteen Unopened Spreadsheets into Single Consolidated Sheet
I have a series of 15 spreadsheets, all having the same column structure. They all have the same filename barring first two letters which refer to a persons initials. The 15 files are stored in the same directory. In that directory I'd like to create a sixteenth file which consolidates much of the data in the 15 files. I want to use that consolidated data in a pivot table. I know that the top-left- hand-corner of the range I want within each of the 15 spreadsheet files is the Cell A10. I cannot be so precise with the bottom right hand corner (BRHC) as the number of rows is variable i...

Listbox and sheet updating issue
I need your assistance with a problem I have with a listbox. I have a sheet containing a range "Reporting_Periods" which has col(1) = a number 1 to 15 (Period), and Col(2) a StartDate and col(3) an EndDate. I have a userform with one listbox, and 3 textboxes - Period, Startdate, Enddate. The users may change the dates in an existing Period, and if the click in the blank line under the last populated row in the listbox, they can create a new Period. The listbox is populated at Userform_Initialize which sets the row source and the bound column (Period). Everything works O...

How can I update multiple cells from a UDF?
How can I get a UDF to change the contents of several cells other than the one from which it was called? Can I use named cells or must I use A1 addressing? Prof Wonmug - > How can I get a UDF to change the contents of several cells other than the > one from which it was called? < You can't. A UDF entered into a worksheet cell or cells can only return a value to that cell or those cells. - Mike http://www.MikeMiddleton.com "Prof Wonmug" <wonmug@e.mcc> wrote in message news:v805m510gm5b4e52oefvce4mes88r5bqnu@4ax.com... How can I get...

How to organize data?
I am a frustrated Excel newbie. I grit my teeth every time I have to use Excel. But I need to create a small database of records and chart it's monthly progression, and I think Excel is probably the best program for the job. Only, I don't know who to do this. Please bear with me as I really need help with this. Say that I have a short list of vendors in different cities. For each vendor I have a total # of items in their inventory and another # of the # of items sold from the inventory, and a percentage of that. So I have the vendors listed in Column A. # of items in inventory ...

Data Normalization
Greetings, I am not sure which newsgroup to ask this question. Any directions would be most appreciated. I have a list of about 5000 customers over a 30 year period. I am trying to give each customer a unique ID. Some of these customers have moved a few times, some of them have gotten married a few times (we deal with the wives primarily), some have multiple service addresses. This question has plagued me for the last 30 years!!!! Any suggestions would be most welcome. TIA -Minitman Hi probably better asked in a database related newsgroup. What you're asking for is quite typic...

Using CTreeCtrl to display streaming data
Hi there, I'm going to be creating an application that displays test data to a certain processor. The default view is to show the overall status of the processor. If there is an error shown, the user can click that processor and specific devices within the processor will be shown with their status. Now here is what I want to do with CTreeCtrl, since I'm brand new to MFC I just need to know what I want is feasible... to get simpler test data, a certain message has to be continously pinged to the processor. To get the detailed test data, more messages have to be sent. Is t...

Exclude a column from data import
Hi there, I am using Excel 2003 and am importing live data into a sheet. Everytime the page loads the current data is pulled in from an SQL 2000 view. The person who wants to use this spreadsheet wants to put an extra column in the middle of the data. I have created a null column in the view so that it is blank, but when you insert something into the column and and refresh occurs, the column quite rightly gets overwritten with null. Is there a way to remove the data from just one column? I understand (I think) that if the order changes, or if a row is deleted from the table, the manual...

Database query from same worksheet
I know how to create a database query from another worksheet. But can the query be from the SAME worksheet? How is that done? If you want to 'query' data on the same worksheet, I would use Auto or advanced filter -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Hall" <hall@garp.org> wrote in message news:%23Fx89OEQFHA.3496@TK2MSFTNGP09.phx.gbl... >I know how to create a database query from another worksheet. > > But can the query be from the SAME worksheet? How is that done? > > But that&...

Problem with Code Because I already have an Open Query
Hi, I have already open a Query named ACCOUNTS and I want to copy the already filtered results into a Table name ACCOUNTS CHARGED. The code is stuck on the Set Myset=CurrentDb("ACCOUNTS") What do I have to adjust to make it work? Thanks Private Sub ACCOUNTS_CHARGE_Click() Dim PoseidonHotelProgram As Database Dim rsta As DAO.Recordset Dim frm As Form Dim Msg, Style, Title, Response, MyString Set Myset = CurrentDb("ACCOUNTS") Set Myset2 = db1.OpenRecordset("ACCOUNTS CHARGED") With Myset If Not .EOF Then .MoveFirst End If Do While ...