I am trying to add data to the same column which is in multiple tables at once by running a macro that has multiple queries. I run a macro, and the first is a delete macro, which deletes the column and then the next is an append query which then adds the new column wtih the new data....and it works, for adding it to one table. But when I try to do this for updating two tables from the one macro (using 4 queries, 2 add, 2 delete), I get the usual warning messages "you are about to add/delete 10 entries, and this is for the first table, so i just lclick "ok" "ok" and then i get the message "you are about to delete 10 entries" for the 2nd table, PROBLEM IS THEN I Get message "You are about to append 0 entries" IT SHOULD BE APPEND 10, its the exact same query setup.....it deletes them from all the tables, but then it will not add them to the 2nd, 3rd, 4th table etc etc.....it keeps saying "will append 0 entries" PLEASE HELP, otherwise im screwd lol.
Hi all Can someone help me with an Append query problem I am having. The end result I need is a Work sheet that has a Customers details, Plant details, Work required and a Subcontractor name. I have a search function that I use to select either a Customer name or Plant ID. Once I have selected the Customer name/Plant ID, I use a button to append the Customer Name and the Plant Id to a Plant_History table. This table uses an Autonumber field to create a unique identifier for each entry which I am using as the Work Sheet number. I have then added coding to open a form which accesses the Plant_History table directly so I can enter the work required and the contractor to carry out the work. My problem is that the Customer and/or Plant will have multiple entries in the Plant_History table. Currently, when I select the button to append the details to the Plant_History table, if the Plant ID is already in the table, the number of records is doubled in the Plant_History table (eg if the record has previously been entered 4 times, 8 records will be appended to the table). I am not sure why this happens, if someone could point out the fault in my work, it would be much appreciated. Regards Craig
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 have a table in access which is updated weekly; I need to create two tables from this updated table.
1st table will consist of all the new entries for the current week
2nd table will consist of all the entries from the previous week - an amalgamation of all the entries which are not from the "current week" (table) For example; the table below shows the two entries from last week.
ID Name
1 Adam
2 Ben
This week I have three new entries New entries
ID Name
3 Charles
4 Richard
So when I run the same query next week I will get something like this.
Old Entries ID Name
1 Adam
2 Ben
3 Charles
4 Richard
[code]....
How do I get a query /queries which divides up the weeks new entries and also all the old entries.
I currently have a DB that has a field in a table that records which shift (A, B, or C) that some data was collected on. I am trying to write a Query in order to filter the results so that either the data from shifts A,B, or C show up, or if "All Shifts" is selected, then all the data shows up. I have another table set up that records the shift selection from a form. I was trying to use an Iif statement but it does not seem to be working for the "All Shifts" option. Selecting "Shift A" or "Shift B" etc. works fine.
**[ShiftSelect] containts the users selection for which shift they want to view.
I have also tried this with a wildcard statement (Like "*") instead of "Shift [ABC]", to see if I could get any data to show but I still get nothing when "All Shifts" is selected!
Let me know if you can see the problem, or know another way to go about this query!
I was just wondering if its possible, i'm sure it is....... I've got a query to pull out invoices for a certain Project it brings up all project invoices and puts it into a report which works fine...... what i want to do tho is perform the same query but after i type in project code in criteria [] i then want to type peoples name or ID's that i want to print as some will be sent at different time intervals and i don't want to have to sort through invoice's to then send? is it possible? any one got any examples?
I have 2 tables... 1 is customer info (name, address, etc...), and the second is shipments (ShipToCustomerName, ShipmentDate, etc...).
Now, I wish to query the shipments table to get ONLY the LATEST shipment information for EACH customer.
If customer A has a shipment in Jan 1, Feb 2, and March 3... while customer B has a shipment in April 4 and May 5. then the result of the query should be
customer A - March 3 customer B - May 5
Is there a simple way to construct such a query?!?
Hello, I have searched the forums and found plenty of information about eliminating duplicate values. What I would like to be able to do is run a query and have it show me only the records that have the field "serial number" that contain the same number. That is, I want to be able to query and see which serial numbers have been entered more than one time. The serial number is not the primary key, this database keeps track of product repairs and is used to log them in by the primary key serial number/date. Thanks in advance for any help. Tim
I have a form that feeds information to a query which in turn sets up a report. For clarity i will list out in basic terms what I have
Input Form - Check Box to activate/De-activate a text field [chk-active] - Text field for a parameter [txt-Parameter]
the query has a column that has null values from the originating table. These values will be added a t a later date, but need to be queried and reported at some business intervals.
In the query criteria for this column, I have the following Criteria
Like IIF([Forms]![ReportGenerator]![chk-Active]=0, "*", [Forms]![ReportGenerator]![txt-Parameter])[/I][/I]
I have tried for the last 5 hours to figure out how to write the formula to be able to get the blank entries to show up as well but have had no luck.
Hi, I'm trying to import data from another database (codes), and the text field in that database is set to 7.
Our new database only needs the first 5 digits of the codes, so I'm wondering is it possible to create a query that filters any entries where the code field has more then 5 digits?
I am attempting to filter the data in query design such that it will not return anyone in the database has there name present more than one time.
For example, in the image below under the Owner_Name field there is "Abbot, Mark C Etux Wanda C". This name appears twice in the database. My criteria is the name can only appear once, therefore this record would not be returned after the query. In query design I would like it to return only name records that appear once in the entire database if there is a way to structure that.
I have two tables: tblOne and tblTwo with identical categories. TblOne and tblTwo both group by Category and Duration. When I make a union query between tblOne and tblTwo, I notice that if I have the exact same category and duration for BOTH tblOne and tblTwo, the union query "eats up" one of the identical entries. How can I fix this?
I need to have two different tables because they correspond to two different machines (but are both the same type of machine, hence the identical categories)
In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
I have made a crosstab query that is sporatically making duplicate counts. I'd like to know how to fix the problem, but more importantly - I'd like to know why and how my query is giving me these results so I can avoid making this same mistake again.
The relationship is set up so that all records from one table are returned, with only matching records from the other - so no problems that way as far as I can see. This is my crosstab query that is giving some counts of the same record as being matched sometimes two, three, or even four times:
TRANSFORM Count([Testing DB].[DB_ID]) AS [CountOfDB_ID]
SELECT [Testing DB].State, [Location].[North], Count([Testing DB].[DB_ID]) AS [Total Of DB_ID]
FROM [Testing DB] LEFT JOIN [Location] ON [Testing DB].[Address] = [Location].Address
WHERE (([Testing DB].[Window Length]) Is Not Null))
GROUP BY [Testing DB].State, [Location].[Size]
ORDER BY [Testing DB].State, [Location].[Size]
PIVOT Format([Date],"mmm-yyyy");
When I do a simple query for a list of the records being counted by this query, I can see the duplicate entries and can eliminate them simply by adding "Distinct" to the select statement - but I haven't had any luck adding any kind of distinction with this crosstab that will do the same.
How can I eliminate duplicate counts of records in my crosstab? If anyone has a suggestion, it would be much appreciated.
I have a query with various entries, pertaining to various invoices. Each line of the query corresponds to a piece of work done, and there may be several lines in the query pertaining to one invoice. I've written the code to input data to a report.
If there are multiple lines in the query to be added to the one invoice, I don't know how to write code that will add those multiple lines in the detail section. Same type of data on each line, just basically pertains to several different lines of work.
Is it possible to create a query that asks for multi entries in one column .....
For example : If I have parts that I purchase and some of them get rejected I want to inform someone of those parts, these parts could be on order numbers, 1,2,8,10 ....
I cannot just search on rejected because some parts could have been reject say 2 weeks ago but new ones have not arrived yet so the flag "rejected" is still showing ....
Is it possible to set the criteria that when asked for the order number/s you could type in ,1,2,8,10 ....
I want to 'append' individual records from 2 tables and place in an archive table or within another database, whichever is the best option. I then want to be able to 'delete' the relevant records from one table.
The tables are tproperty and trents. This property paid rents but has since been sold. Therefore it no longer belongs in the database, however client wishes to keep details of the property/person/and rents paid in past, for any future ref. These tables are linked in relationships to tlessee and tbilling. I've read books/notes/looked on here for inspiration and the right direction! If I choose 'append' which seems pretty straight forward then a 'delete' query, how do I choose only one record? Is a make-table onto a different database a better option? It would appear that the whole table is copied over? Can't understand the issue about auto-numbers being copied over? Do I have to use an append/delete query for each individual property that's ever removed? I'm at a loss! :) Thanks
I have a database that contain foreclosure records. I'd like to create a query that will ask for a date and all records that are LESS than the date will be moved to a different table.
I'd also created an icon on my form and I'd like to attach this query to it.
Please how can i use a procedure to create a query, then append the content of the query to a table (am using MS Access Project)? i did it in Microsoft acess database but now i need it on Microsoft access Project beacuase am transfering to SQL server. Thanks
Now i want to create an append query that will add in table ZALGA fealds PROIZ_I, TIP_I, DN_I, KOS_A, KOS_I by critera if TIP_A = TIP_I AND DN_A = DN_I then calculate KOS_Z = KOS_I - KOS_A?
I am building a database to hold information of training courses and staff that have requested or completed the training course.
I have built a form which the team leaders can use to request training for their team, basically when the form opens up it asks for team name and training session and then appends the names and training session ID to the main table. This then allows the team leader to tick the "request training" tick box which updates the table for each member they request trainig for. They would then send this using a custom command button which is linked through outlook.
The problem I am encountering is that if they were to selct the same team and training session again it would then append the same data to the table and this would create duplicate entries.
How can I set it up so it appends only once and then any other time the same data is selected by team leaders it would populate the form with the existing date rather than appending the same data.
Dear all, I have a trouble to run the append query. It is failed due to the key violations. Could you please help me how to solve this problem? Many thanks. Bich
Any assistance someone can give me with this append querry issue is appreciated.
I have a table called tblRoleAssignments that has three fields RoleAssignmentID (PK), RoleprofileID & ApplicationrightID. It looks something like this.
my append query will write new records depending on the RoleprofileID's I entered, for example If I run my append query on the above list I get the following output
My problem is when I run my append query I want it to enter a specific value in the role profile ID column (taken ideally from a field on a form that will be loaded) so instead of the query entering a zero it writes to the table a vaule I want. Below is how I want my append querry to write the data if I wanted the RoleprofileID to be set to 35.
I think i need to use an append query for this but have never used on before so just need to know if it is the right thing to do.
I have two tables [tblPoles] and [tblPoleInstructions].
I have a form set up for [tblPoles] so the user can enter the data required. One piece of data required is a start date. The only fields the tables have in common are the PK which is the Pole Number and the start date.
I want the user to be able to enter the start date on the form for tblPoles and it to be automatcially entered into tblPoleInstructions. Is an append query the right way to do this?
I have made some amendments to my brothers Database. Now I have a problem. How the hell do I append the data from his old one into his new one? It all seems very complicated. Can anybody advise? Basically the tables of primary interest are:
Append From tblCustomers1 to tblCustomers Append From tblCustomerContacts1 to tblCustomerContacts Append From tblOrders1 to tblOrders Append From tblOrderDetails1 to tblOrderDetails Append From tblPayments1 to tblPayments
How is this possible as Orders relate to Customers, Order Details relate to Orders, Payments to Orders, Contacts to Customers.....my brain is scrambled!!
Help on understanding Append queries appreciated. Regards, Phil.
I have a simple one here I think, but I am a newbie to SQL and Append Queries.
I have an Order Tracking Database with three tables: Orders (contains PK OrderID) OrderDetails (Contains PK OrderDetailsID and FK OrderID) Updates (Contains PK UpdateID and FK OrderDetailID)
The Orders table contains customer info and an order number (OrderID). This is manually entered, not an Autonumber. The OrderDetails table contains line items for products sold with that order. The OrderDetailID is an autonumber, and each record contains the OrderID field as a FK in a one-to-many relationship. Basic stuff. The Updates table is used for tracking the status of each product associated with an order as it flows through the back-end sales process, from production to shipping. The UpdateID field is the PK (Autonumber) and each record contains the OrderDetailID as a FK in a one-to-many relationship.
I do a monthly update of Orders and OrderDetails into those respective tables in Access on a monthly basis. I am just using cut and paste from a .csv file at this time. I am doing this because our company uses a Siebel program for tracking front end sales, but it does not do any back-end tracking at this time. So, I export the monthly sales as a .csv and import into Access. It seems to work fine for now, but it has been suggested in other discussion groups to use a temp table of the raw .csv data, import into Access, and create an Append query to update the Orders and OrderDetails. I will look into that.
The problem I have today is how to get Access to automatically create a new record in the Updates table for each OrderDetailID. So, when I paste (or later append) OrderDetails into the OrderDetails table, Access creates at the same time a new corresponding record in the Updates table. Each OrderDetailID can have many UpdateID's (one-to-many relationship).
I think I need a SQL statement that says "Update the Update table UpdateID with a new autonumber if the FK field OrderDetailsID within the Update table is null" or something like that.
