PartID and SpecID are long integer, Sequence is integer.
I originally had Me.txtKey in the SQL, but I saw a comment in a post about that being a possible culprit. So I copied it into a long integer, still didn't work (latest trial above).
I have a query that checks a table where there's a field that only has numbers from 0 to 100 (a grade), let's call that field "average" (note, the values 0 to 100 are actual numeric values, not percentages)
here's the problem:
when I filter the query using a parameter like <[value] on the average field, the query does show the expected records that have an average value that is less than the value that I input when prompted... except that it also includes the records on which the average field is 100 ... ... for some uknown reason.
to clarify:it won't show anything over the imput value, it just shows anything under the value I imput (good) and anything that has an average of 100
when I hard code the value for the parameter say <65 the query gives me the results expected (anything less than 65 in the average field) without including records with average equal to 100
some details:
the average field has this code: Average: CInt(Nz([Grade]))
I made a query that requires to enter two parameters (both are Number data type). I need the 2 parameters to be a criteria in the same row (AND). When I run the query it gives me zero result (and the data is already exists in the table). What is the mistake?
When running a query in Access 2013 or 2010 we get an ODBC call failed. However when we run just the form, which the query connects to, it works just fine.
I have a large table (>1M rows), and I have searched various forums for a way to add sequential numbers by Group. The query I have works, but since it's a large table, I broke it up, and did everything what starts with A-E, the F-Q, etc, and appended to a new table.
This query works, on anything that starts with the letter D or later... A-C will not work.
Basically, the source table is a list of all combinations of Part_ID and UPC_Code. I am trying to number the UPC_Code field, per Part_ID. There is an AutoNumber field (ID) that is in the table as well.
This is the SQL. Query1 is the query that is being executed, so the DCount is within this same query, if that makes sense.
Code: SELECT tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code, DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "'")-DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "' AND [ID] > " & [ID]) AS Seq_Num FROM tbl_upc GROUP BY tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code ORDER BY tbl_upc.ID;
The results of this query are that all Part_IDs that start with A through C produces a Seq_Num of 0, but any that start with a "D" or later number correctly - in other words, the first instance of a particular Part_ID is 1, then 2, and so on up to the total count of that Part_ID.
I have a normalized DB with one to many relationships, using Primary and Foreign Keys.
I need to do inserts and maintain the PK/FK relationship, which means when I add a new PK I need to insert that PK as FK in other tables in the same transaction.
How do I do this in Access? SQL Server I use transactions, but I can't lock up the tables in Access like that.
I did a bunch of searching and found nothing, which leads me to believe I am way off in my thinking. Below are the dirty details
I have 2 tables, tblName and tblPhone. 1 name can have many phones.
tblName has PKName. tblPhone has PKPhone, FKName.
I have a form where user enters a new Name and PhoneNumber.
Name gets inserted to tblName, assigned with PKName = 100
Phone should get inserted into tblPhone with PKName.
IE Insert into tblPhone (FKName, PhoneNum) VALUES (100,"212-555-1212").
The dumb way I am doing it now is I insert to tblName, query tblname for the PK, then write to tblPhone. This can't be right.
I'm trying to set up a data import function as follows: The user fills out a form, generating an entry in the Orders table. After pushing a button, line item data will be imported from an excel spreadsheet. I've got most of this working- I have a temp table that gets cleared out and then populated from the excel spreadsheet which is then deleted, all working fine. My hangup is when I try and get the line item data out of the temp table. I need to append the Order unique ID from the textbox on the form to each line item. Here's the select statement part of the insert into:
SELECT Forms!frmOrder!OrderID AS OrderID, ItemsID, NumUsed FROM tblTempOrder
This gets the line items fine, but puts an Ankh symbol as the OrderID for each line item.
I am trying to reformat a string of text so that it is easier to read on a report.Based on the text, it seems like the best thing to do is to insert a carriage return before a date/time stamp.Here is the data (it's a conversation log)...it is three unique conversation log entries:
04/13/2014 8:21PM - all work and no play is no fun - Bob Smith04/13/2014 8:21PM - this is a test of the emergency broadcast system - Bob Smith04/13/2014 8:20PM - log testing - Bob Smith
If the carriage return is before the date/time string, then all entries would look the same. If the carriage return is after the date/time string.A user had suggested a child table to break out the data into separate records, but I'm sad to say that I never worked with child tables. Any way for replacing 'Date/time stamp' with 'carriage return' & 'Date/time stamp"
Where would you put the INSERT INTO statement in a query? Would it go after the select statement but before the From statement or would it go at the end?
I have moved from Access 2003 to 2010. In Access 2003 I was able to create query groups that contained shortcuts to queries in the main database. This worked great for us because we did not want to create user interfaces that required support- but we could have multiple queries in a database and have users look only in their query group.
The database I am using was created in 2003 and had those groups. Is there a way to view, edit and create a similar view in 2010?
I was wondering if someone could help? I am using Access 2002 and I am struggling to find out out how you can insert/update/delete records through a form using the design view. Is this possible or do you need to do this another way?
Could some one point me in the direction of a comprehensive tutorial or outline some instructions for what I need to do?
I need to create a form that inserts people's details into a table
When user types in a surname as a parameter query, up pops the form with the details of the person stored in the database, and the user can update the details through the form and the details are saved to the table they came from.
I have a UNION of two SELECT queries. It works very well but I need the end-user to be able to modify the data so I am using INSERT INTO tablename. This will work for one query at a time but if I try to use the UNION it reports “Syntax error in FROM clause”. Can you use UNION in this case or can anyone see my error?
The full statement is rather complex, I have added a few spaces and linebreaks to make it readable as follows:
INSERT INTO TableCompleteForMailing
SELECT [Address List].[FamilySurname] AS Surname,
[Address List].[DEARFirstnames] AS FirstName,
[Address List].[Mailing] AS Mailing,
[Address List].[Christmas Mailing] AS OtherMailing, [Address List].[Address 1] AS Address1,
[Address List].[Postcode] AS Postcode
FROM [Address List]
UNION
SELECT [Names].[LastName] AS Surname,
[Names].[FirstName] AS FirstName,
[Names].[MailingList] AS Mailing,
[Names].[Selected] AS OtherMailing,
[Address List].[Address 1] AS Address1,
[Address List].[Postcode] AS Postcode
FROM [Names],[Address List]
WHERE [Names].[AddressListID]=[Address List].[AddressListID]
So, I am inserting new records into a table. The values in the decimals are being stored as zeroes.
Field Properties Long Integer Standard (I also tried Currency) 2 Decimal Places
When I remove the "INSERT INTO" part of the query, the decimal data is in the results. I started out not using the FORMAT function. Then I tried it using "Standard", "Currency", and "#,##0.00"
I have a form with many fields and one field has an OnChange event to run an insert query or log an entry in a table about the change. I get insert entry written with everything except one field.The OnChange event code is:
Code: If AddressStatusFld = 2 Then 'If it has changed from Current to Non-Current create a Contact Log entry for this matte If (Val([MatterShortNoFld]) Mod 2 = 1) Then Me.OperatorFld = 16
[code]....
I just can't work out why the record in MatterContactsMade table has a null value for the field Operator.
I want to insert an if statement in Access report that states.If the interviewer field is not null them put in the interviewer. If it is null then don't put anything.I have 5 of the interviewer fields and don't want empty lines in the report.
there is a query which i made which counts data from two tables RAUTH AND PERS
SELECT RAUTH.coy, RAUTH.Auth, Count(RAUTH.coy) AS Held FROM pers INNER JOIN RAUTH ON pers.coy = RAUTH.coy GROUP BY RAUTH.coy, RAUTH.Auth;
THERE IS A BLANK FIELD NAMED HELD in table RAUTH i want to update the held result which i quired into HELD fiedl of RAUTH table where COY (Field) is COY field of PERS table
RAUTH table structure
Coy Auth Held
Tata 12 Dell 11 HP 21 Opera 11 Bangour 10 pers table
Persno Name Coy Qual
124578 Gita Tata Bsc 235689 Sonu Dell BA 748596 Kiran HP BCA 459687 Sony Tata MCA 965214 Baiju Bangour CCNA 963656 Ashok Opera MCA
I have a table (Quarter) which acts as a source for another table. When I create a query based on the latter in the Datasheet view the entries of the second column of table Quarter are shown. The PivotTable field however displays the primary key.Where do I need to make the change to only have the second column entries (Q1, Q2, ...) shown in the PivotTable view as well?
I need to know the best way to insert data into the backend without cause major bloating of the backend. I am currently inserting about 20k records at a time using an INSERT INTO statement. The backend went from 83mb to 511mb trying to insert the last 20k records.
I created an update query to a text field in a table. However, it inserts the data at the end of the string and I need to insert it at the beginning of the string that already exists. How do I move it to the front instead of the end?
