Thanks for taking the time to look at my problem first of all. I have a form that shows records but I want a button next to each record that will append that one single record to another table that is built exactly the same, which is for historic records. Not the brightest on Access and I can only manage to append all records.
As an example, I have an employee record that have a task, when the task is complete I want to append the record to a historic table that I can look up later on. However, just that one employee and not all that are in the current table.
I am creating a database where the records of individual students are to be moved to different table depending on where they are in the graduation process. In order to do this, the secretary will enter the student in the "90 Hr Request" table (think of it as the first step), and move them down the line of tables (4 in total) until the final "Completed" table. Each table in the progression has more and more fields. However, a student (with their ID number as the primary key) can only be in one table at a time.
I understand this does not sound like a traditional database, as the data is not normalized. However, this is being used more as a filing system than anything else. Their data is primarily paper based (for legality reasons), but wishes to keep it organized in a database so they can run queries and print reports.
Currently, I am creating a macro that will run specific queries (in an order). I have made an append query that will move the records over, then I used a update (to null) and delete query combo to delete the old individual record. I made the delete + update query work by using a selected criteria.
How to make the append query move only one student's record at a time.
Hi, I am trying to run an append query, but instead of appending the whole table, I would like to only append a single record.
I have an append query, and it works like a charm. I can also get it to work with prompting the user for a parameter (in my case LeaseId which is a primary key). When the user is prompted and enters the LeaseId it only appends the single matching record. Works like a charm.
However, I don't want to prompt the user for the paramater. I want the user to generate the LeaseId based on the selections in two combo boxes.
So far the I do get the right LeaseId, but I have no idea how to actually pass the LeaseId to the query. I've tried the following, but I know it's completely wrong.
I have tables Account (PK acctnum) and Orders (linked via acctnum to Account). There are multiple orders per account that need to be billed individually. When creating an invoice I need to add the Account to the Invoice table and the Orders to the InvoiceDetail table. I use 2 queries to accomplish this. The first one though adds multiple records to the invoice table (because of the one to many relationship). I need to keep that so I don't bill anyone with no orders.
INSERT INTO Invoices ( InvoiceDate, AcctNum ) SELECT DISTINCT Date() AS InvoiceDate, Account.AcctNum FROM Account INNER JOIN [Order] ON Account.AcctNum = Order.AcctNum WHERE (((Order.OrderNum)=[Forms]![Account]![Order].[Form]![OrderNum]) AND ((Account.BillingCycle)="on discharge") AND ((Order.EndDate) Is Not Null) AND ((Order.Closed)=Yes) AND ((Order.PatientName)=[Forms]![Account]![Order].[Form]![PatientName]));
Is there any way to get it to only add one record? I've tried limiting it with several parameters to no avail.
I have a query run that gives me a list of records that I view on a continuos form. What I want is to press a button and run a macro/Append Query to add a Single Summary record to another table.
For example my query spits out this data
Part # Quantity Serial Number GO2 1 123 GO2 2 456 GO2 2 789
What I'm looking to get is
Part Number Total Quantity Serial Number 1 Serial Number 2 .. GO2 5 123 456
I'm stuck on a couple of things.
1. Getting a new single row to append. 2. Getting Serial Numbers from several records to save on to a single record.
hi Guys, I have been looking at different post and checking Microsoft help files as well, but still can't seem to fix this problem.
I am having 2 tables. The first table is connected to a form for viewing and entering data, and in the second table i am just copying 3-4 fields from the first table.
I am trying to use the insert statement to insert records in the second table, and everytime i click on the "Add" button to add the records i get the following error "MS access can't append all the records in the append query ... blah blah blah"
However if i close the form and reopen it, and goto the record (as it is saved in the first database) and now click on the add button to add the fields to the second table/database, it works.
I need help with ACCESS query. I am not at all good with access queries and am unable to figure this out even though it may have an easy solution.
There is an existing query designed by our company in a database. This query links different fields from 4 more queries and finally generates 16 columns as shown below. (I have also attached excel file with the same example as the text below is not aligned)
In this example I am just showing 8 records, but in reality there usually about 4000 records and the number changes every day.
Now I am asked to design a query based on the query that gives the above results.
The 14th column (heading –MV by Fund code) actually sums up the market values in column 12 (BMV) that has same fund code in column 15. For example for fund code “abcd” the BMV are $150, $256 and $325 which sum upto $731 and hence this number shows up in column 14 against each record with “abcd” fund code.
Now there is a difference of $14 between column 15 (heading-Total TNAD) and column 14 (heading- MV by Fund Code) for fund code “abcd”. I am asked to design a query that will add records on the results generated above by the existing query.
The new record added should show the difference of $14 in column 12 and text “OTH” column 8 and column 9 and should show the fund code “abcd” . The values in remaining columns should be the same as the fund code “abcd”.
The above logic would then apply to funds “wxyz”, “pqrs” and so on
In this example the following three records should be added below the above 8 records with these details
My user opens frm_ENTRY. Which is pulling data from tbl_TEMP. They clean up a few fields on the form. Then click a button which executes an append query which appends the data to tbl_MAIN.
My problem is sometimes when it appends the data it is overriding older data. It is not creating a new line each time. It simply overrides an existing record. Does this make any sense at all?
There are many other forms, queries, tables and macros in this DB. If that matters at all. I think it has something to do with a bound form that is connected to a drop down from a query, but I have no way of trouble shooting this? Where do I start?
How can I modify the below code so that it only adds new records to AttributesTBL from ProductInformation-Consumer?
INSERT INTO AttributesTBL ( Material ) SELECT [ProductInformation-Consumer].Material FROM AttributesTBL INNER JOIN [ProductInformation-Consumer] ON AttributesTBL.Material = [ProductInformation-Consumer].Material;
I have attached an image of the 2 tables concerning my question.
The main "transaction" table is the tblAssessments and a linked table tblRisks drives a subform - showing multiple Risks per assessment.
The field "OccupantID" identifies the facility where Assessments are done (there are other tables, of course).
I am using an OccupantID "00000" to store templates - pre-filled assessments with most common options selected.
I am trying to develop code and/or query or SQL that would do this:
copy all templates (records from OccupantID 00000) and corresponding sub-records from tblRisks into the same tables, but under a different (selected by user) OccupantID.
I have no problem just using an Append Query (actually a SQL statement in VBA with variable parameters), but that only lets me copy into 1 table - so I can copy just the tblAssessments records.
but how do I then copy the tblRisks related records and make sure I attach them to the correct AssessmentID?
Can anyone help me with this - I have an append query to a statement table for invoices with a macro set. When the query is run the data is added to the statement query.
what is happening is that it is adding the data ok on a seaparate line but is also updating any previous invoices for the same client to the new invoice number obviously not what I want.
I have an order system whereby there is a "basket" table and an order detail table.
I want to use an append query to move all the records from the basket table to the order detail table.
However, I also need to mark each record that gets appended to the order detail table with an "Order Id" that has already been saved in an Order table.
I.e. I save the main details of an order into the order table then copy records from the basket table into the order detail table along with an order ID that comes from a form.
I'm using an append query that needs to add the records from another database into a table. I can get it to get the data and add the records. There are other columns in the database that are not in the one the data is pulled from. If I would run the append query again, it would add the same records again at the end. How can I avoid this? I only need to add new records that are not yet in the database I want to pull the records to.
I created a subform that displays the records of a select query after selecting the desired data from a single combo box (for instructors name) and then using an OpenQuery macro attached to a button.
Only 1 record is displayed in the subform each time I hit the button. If I look at the query return there is no change. However, if I close the query and select a new instructors name, although I only get one record in the subform, the query when opened shows all the correct records.
I have an append query that everytime it runs it will simply duplicate the records. ex.. sample table has 11 records if you run it again the tableB will now have 22 records. I need it to only update the records, so if someone makes in change / new record in tableA it will only update the records in TableB. I have the Primary Key set to Yes no duplicates. TableA Primary Key is ID TableB Primary Key is CustomerID
So, I've been searching through this forum and can't seem to find the answer to this one. I would like to capture a value from a main form and have it used as a value in an append query, in order to populate a subform based on the main form, like so:
INSERT INTO tblTakenSurveys ( VisitID, SurveyQuestionID, ResponseCodeID) SELECT Forms![frmMyFormName]!VisitID, tblSurveyQuestions.SurveyQuestionID, 66 FROM SurveyQuestions WHERE SurveyID = 3;
Might help to explain some of the terms in this statement: tblTakenSurveys is where I need the new data to be entered via the subform. Forms![frmMyFormName]!VisitID is a textbox control bound to a PK in another table that has a one-to-many relationship with tblTakenSurveys. tblTakenSurveys.ResponseCodeID is a foreign key that represents respondents actual answers to questions. 66 is a value for a ResponseCodeID that stands for a dummy value meaning "data not yet entered"
As per advice I received from others on this forum, I have set a query like the above to run from a command button to populate the subform (in theory). But I'm sure I've done something wrong within the query because it will not return a value from the form "VisitID" control and therefore will not append the rows. Without the appended rows, my subform will not populate. And this has me running in circles...
I pasted the link to another thread below, where I originally received a lot of input as far as the table structure. I did not start this one, but my posts are the most recent (as of now anyway). Pat Hartman had given me a lot of the guidance here.
- I have a module which runs queries on linked sales spreadsheets, to merge them in to one Access table. - To prevent duplication of sales, the primary key merges the sales record and item number fields.
Today, it's found 103 key duplication errors, which is fine. But it's still adding data to the table. The data seems to be fields which aren't even mentioned in the query. It only does this when the query is ran from VBA.
I am trying to create a form with a button attached to each record that would allow the user to click the button and it would automatically open outlook and fill in the TO:, SUBJECT: and BODY: fields. Here is the code that I currently have:
Code: Private Sub Command33_Click() Dim strEmail As String Dim strMsg As String Dim oLook As Object Dim oMail As Object Set oLook = CreateObject("Outlook.Application") Set oMail = oLook.CreateItem(0)
[code]...
There are two issues I keep running into:
1. This code opens outlook and populates all of the fields but pastes the email incorrectly. Instead of pasting just the email (email@email.com) it pastes the html tags as well (email@email.com#mailto:email@email.com#) which means that the user would have to delete everything between the #'s in order to send the email every time.
2. I currently have the email BODY pulling from a table but this obviously limits what I can do. I would like to simply encode the BODY within the VBA code. The setup I am looking for is: one paragraph a blank line a hyperlink to a website a blank line another paragraph
I have created a linked Excel table in Access 2010 called 'tblExcelLinked' and I have a form called 'ASB Log Form' for the purposes of presenting the data in a more readable manner that is easier to view, plus link other fields of data that are not directly related to the 'tblExcelLinked'.
Because there is no unique ID in the 'tblExcelLinked' to create a relationship, I have created a table called 'tblASB', which allows me to add other table data linked from same d/b.
I now want to update the 'tblASB' with data from the 'tblExcelLinked', but only append new records from 'tblExcelLinked', but my inadequate append query is duplicating the records each time I run it, rather than just adding the new ones.
Once sorted my next challenge is a macro so that this runs automatically rather than being manually triggered.
I have a database with a table with company names, then a relationship to another table that shows that companies' address, but I also want there to be an address 2 and 3 and so on, and some of our companies have multiple sites.So what Im asking is how would you be able to show multiple data, on a sing record.
Hi everyone . Please I need help with this I have a db with 22 records: (A123,B456,C789...Z999) I want to be able to read each record and extract from each one the same data fields and make a big single record out of the reading:A23+B56+C89...Z99 The reason of doing is I want to be able to show on a form the data extracted from the multiple reading. I tried a crosstable, but gave no much room to work with. I think if I use 22 different queries it wont look professional and hard to maintain. I've being considering better a loop with maybe multiple if conditions but I dont know how to apply it. Something like newrecord = "" for x=1 to 22 read record x newrecord = newrecord + record x next
I'm not sure what the definition of this type of concatenation is, so I'm having trouble searching for it.
I have three tables - one is tblNames (with ID, first_name, last_name) with 25 records, and tblStates (with StateID, state_name, state_abbrev) with 50 records, and the requisite join table between them - tblNamesState (with autonumber and the two FKs), because some of the folks in tblNames can have locations in multiple states. Good enough.
But now it's output-to-spreadsheet time, and I want to run a query that will give me 25 records, and concatenate the location records in a single row. An example would be:
first_name | last_name | state_name John | Johnson | AL, AK, TX, FL Eddie | Edwards | MA, TX, FL Sally | Smith | VT, WA, CA, TX
How do I concatenate records from a single column?
I already have a form where you specific the date and the store I've purchased something at. I want to be able to add several products I've purchased to a table and define their price, using the same form, so I don't have to input the date and store type for each purchased product.
So, when the form updates the table, it will create a new record for each product, using the product description and price - defined for each product - and using the date and store - defined once - as elements of the record. Is there a way of doing this?
I'm quite familiar with creating tables, forms and queries with the design wizard - not so familiar with using scripts, but I've had a small amount of experience with manipulating databases using MySQL, so I'm willing to learn anything that may be necessary to achieve what I need.
I tried the idea sugested in post (http://www.access-programmers.co.uk/forums/showthread.php?p=423080#post423080) but it didn't seem to give the cross referencing that I had hoped for .... example 1 is related to 4,3, and 5 while 6 is related to 1 thus implying an extended relationship to 4,3, and 5.
In a standard one-to-many I'd look at record 1 and see that it is related to 4,3,5 but if I were to look at record 6 I'd only see that it is related to 1. How would I set up the table relationships to drill further to see that 1 is also related to 4,3,5?
How do you do a many-to-many from one table back to that same table?
tblEvent EventID(AutoNumber) EventTitle(Memo) 1 memo content 2 data 3 more memo content 4 some info 5 more stuff 6 other text
Hi I would like to exclude two specific records from my recordset.
For example suppose I have a Flights Database and I want to see all the flight records for Aircrafts 132 and 232 only, between the date jan 1, 2007 to Sep 12, 2007.
The above I know how to do achieve...
([FltNum] = 132 or [FltNum] 232) AND ([FltDate] >= Jan 1, 2007 and [FltDate] <= Sep 12, 2007)
However, among this set of records I want to exclude flight 132 on Aug 1, 2007 and flight 232 on Sept 1, 2007.
([FltNum] = 132 or [FltNum] 232) AND ([FltDate] >= Jan 1, 2007 AND [FltDate] <= Sep 12, 2007) AND NOT ([FltNum]=132 AND [FltDate] = Aug 1, 2007 ) AND NOT ([FltNum]=232 AND [FltDate] = Sep 1, 2007)
I haven't been able to accomplish this using the query grid. Is it possible or do I have to write SQL?
