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 Union query as follows;
SELECT ALL *
FROM 1st_Lives
UNION SELECT ALL *
FROM 2nd_Lives;
1st Lives has 465,414 records and 2nd Lives has 151,852 records.
When I run the query I only get 604,976 records instead of 617,266 (I basically just want to add the two data items together).
Using Access 2007
Win 7
Total_Time: Format(nz(DateDiff("n",[Start],[End])/60))
This is my expression in a query.
I need to only show 2 decimal places in the results field.
I want to print the results of a query where some values are whole numbers, some have 1 decimal place, some have 0.3333333 recurring etc. Problem is that in order to fit all the columns on a page the column width does not allow all the 0.33333... to display. So what I see is like this (there are lots of columns, I am just giving an example from one column)
891
833.5
######
I don't want to fix decimal places to 1 because then I would see 891.0 instead of 891. How can I format the number field so that whole numbers are shown without decimal place, numbers with 1 decimal place stay like that i.e. 833.5 is fine as it is. But display something like 313.666666666667 as 313.67 rather than ##### getting put in as not enough room for all the decimal numbers.
I have a statement as follows:
Code:
ohc: IIf(([oh]/[csqty])>1,[oh]/[case_qty] & " (LTSU?)",[oh]/[case_qty])
The intention was that it would add " (LTSU?)" to the calculated number where it was less than 1 (or not a whole number). Stupidm, me didnt account for fractions greater than 1 (ie 1.566). Just wanted to know how I can identify all 'non-whole' numbers in the above IIF statement.
Is it possible to have a query retrieve all information from one table, but only partial information from another?
For example, suppose I have two tables, Jobs and Employees.
The Jobs table is a list of all available jobs in the company; some filled, some not. Each job is assigned a Job Number which is unique.
The Employee table is a list of all employees in that company. Each employee is assigned to a particular job, referenced by the Job Number.
The query's job is to provide a list of all Jobs, but with employee information added to those jobs that are filled.
The Jobs table has two fields; JobNum and JobDesc.
The Employee table has three fields; EmpNum, EmpName, JobNum.
Jobs has five records;
100, Boss
105, Aide
200, Manager
201, Worker
202, Worker
Employee has four records;
111, Montgomery Burns, 100
112, Waylon Smithers, 105
121, Homer Simpson, 201
122, Lenny Leonard, 202
Jobnum is the link between the two tables in the query and its fields are;
JobNum, JobDesc, EmpNum, EmpName
What I want is;
100, Boss, 111, Montgomery Burns
105, Aide, 112, Waylon Smithers
200, Manager
201, Worker, 121, Homer Simpson
202, Worker, 122, Lenny Leonard
But, what I get is;
100, Boss, 111, Montgomery Burns
105, Aide, 112, Waylon Smithers
201, Worker, 121, Homer Simpson
202, Worker, 122, Lenny Leonard
Job Number 200, the Manager slot, is being dropped out.
This is the SQL code for the query;
SELECT Jobs.JobNum, Jobs.JobDesc, Employee.EmpNum, Employee.EmpName
FROM Jobs INNER JOIN Employee ON Jobs.JobNum = Employee.JobNum;
How can I get the complete Job list with employee data added to those jobs which are filled, while leaving the vacant positions - well, vacant, but still included in the query's result?
Thanx!
When using the carriage return how do I format the data so it will export with 2 decimals?
In the example below I need the Estimated Material Cost to export in in format: 500.00 --- NOT 500.
MTLCost: [Estimated Material Cost] & Chr(13) & Chr(10) & [C]
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies View RelatedI am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.
Tables:
Days with 7 records
Hours with 24 records
Workers with as many people that sign up to work the different hours
Schedule signifying the worker, day, and hour which are signed up.
As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.Currently my Schedule table has the following:
WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
16 | 5 | 15
What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:
WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
| | 14
16 | 5 | 15
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?
View 2 Replies View RelatedI work with access web database. In access web aggregate functions are disabled in query design. So I made a query in client and i thought then i can insert this data to another web table. but i don't know how to update this table.
Because the source table of the query is also updated.
I upload the database with a table in it and a query. I want to add the data of this query to another web table....
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.
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 enter values into a table with 2 decimal places.
I have a query that applies percents to the values - I have set the results to have 2 decimal places.
I have another query that summarises data from the previous query, however the results I get are not right, the decimals differ from the actual result, if I make a sum of the values by hand.
It seems as if the query I have made to apply percents only DISPLAYS data with 2 decimals instead of actually rounding it, so that the other query summarises data with more than 2 decimals, and thats where the difference int the end result comes from. I don't know how to fix this however.
Can someone please help.
Thanks!
I want to create a append query in access 2003 to insert data into an existing table from Excel workbook.My Table name is TokenDetail in Access 2003.
And Excel File is TokenCreation.i want to create query with msg box and requered file path for data becuase my excel files have various path and name.
How to take the results of query and copy them to a column in a table?
I am using sharepoint and ms access together, and Sharpoint in this instance will not reference the result of the query. I can only use table fields with Sharepoint...major pain....
Example, I need riders name, weight and height from the column in ALL INfo query to automatically fill the column table in ALL Info
queryreservations!AllINfo.value=tablereservations! Allinfo.value
and how to loop through records that are not filled out already?
seems like it should be a simple Do.command but I am not sure how to code it..
I have two tables each with an ID field (autonumber/PK/No Dup etc).
I want to append two fields from one table to the other table. I have set up an Append Query to do this but it won't work - I get the following error - "The INSERT INTO statement contains the following unknown field: 'FiID'...."
I am building an access database for my college project and I essentially have a quotation form that when I click a button 'Convert to Invoice' it creates a new record in the invoice table and then creates new records in the invoice details table which match the quotation details table. This is working as it should but for only the first 2 customers in my customer table?
On the quote form I have a combo box which is linked to the customer table and updates the quote table based on the selection. If I select customer 1 or 2 and click 'convert to invoice' it works and opens an invoice form based on the inserted data however if I select any other customer it returns an error that the record wasn't added to the table due to key violations?
As far as I can tell I am not trying to update the primary keys in the Invoice Table or the Invoice Details Tables.
I'm trying to duplicate the records in a subform to a new form but keep getting a too few parameters error.
Code:
strSql = "INSERT INTO [OrderDetailT] ( OrderID, ProductID, Quantity, DiscountPercentage ) " & _
"SELECT " & lngID & " As NewOrderID, ProductID, Quantity, DiscountPercentage " & _
"FROM [OrderDetailT] WHERE OrderNumber = " & Me.OrderNumber & ";"
The debug.print comes out as below:
INSERT INTO [OrderDetailT] ( OrderID, ProductID, Quantity, DiscountPercentage ) SELECT 49 As NewOrderID, ProductID, Quantity, DiscountPercentage FROM [OrderDetailT] WHERE OrderT!OrderNumber = 11;
Any possible solutions to this issue would be much obliged, it's one of two absolutely stupid issues that I'm just working around since I can't resolve...
I have a parameter query set up on a Date/Time field that is returning results not including the last date entered as a parameter. So if the user enters "2007-04-07" for the start date, and "2007-04-30" for the end date, the records with "2007-04-30" are not included.
I know it's some stupid thing I've done with this query that is causing it to do this, but since when does "<= some date" and ">= some date" not include the last date retrieved in the result set... do I need some programming lessons on operators, or what?:confused:
Table TBL_NEWDATA is used to append new data to table TBL_PERSON_ALLOCATIONS.
TBL_NEWDATA { Person_ID, Department_ID }
TBL_PERSON_ALLOCATIONS { Person_ID, Department_ID, ... }
I need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there. i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.
INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID)
SELECT Person_ID, Department_ID
FROM TBL_NEWDATA
WHERE TBL_NEWDATA.Department_ID='Form...'
[code]...
This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.
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 am building a database for a client that tracks construction drawings. The client came today to check on progress. Just before he came, I changed the start-up properties to hide the database window, menus etc so it would look more like the finished product.
Problem was, I lost alot of the functionality and had to change it back to demonstrate:( . Is this common and can anyone explain why? much appreciated
Hi Everyone
I wonder if anyone can help me please.
I have a form that nearly fills an a4 sheet of paper when printed. For this reason I have to set the margins from 24.99 down to 5.
This is ok and the form works well and prints out properly about 75% of the time. Occasionaly when I open the form it has reverted back to the 24.99 margins which of course prints out on three sheets of paper and is all over the place.
Is ther a way to set the margins on load and can anyone suggest an appropriate bit of code.
Kindest regards
Tony
Hi,
There's not much space in the title but I hope I caught someones attention that can help.
For starters it's ACCESS97
The database is split into front end and linked tables.
We added few fields a couple of days ago. ( We have a full back-up)
What is happening now is that after a period of time the buttons stop working and give a message saying something like...
The On Click can't find the file or info that it is supposed to be doing. That isn't the exact words, but I don't currently have the damaged version it is deleted. There is no error message number.
The same thing has happened 3 times. Each time I delete the front end and replace it with the backed up copy. This then works again.
Also....
Once the corruption or whatever it is happens, you cannot add any buttons in design view and also you cannot get to the Event Proceedures through the properties and also the Build Event doesn't let you in either. All you get whet trying any of these is a File Not Found message.
Any ideas out there or do I just have to revert back to the copy before the changes ?
It's a very big database about 90Meg Front end and 8Meg Tables
Help Please.
Thank you
Mike:confused:
I've looked at so many options for writing this query that I can't see the forest for the trees and hoping you kind folks will point me in the right direction.
I have a table called services that includes:
service_id
prop_id
service_year (ie: 2007, 2008)
service_date (ie: 3/3/08)
service_id is a unique ID for each service which relates to data I will need to pull from another table.
prop_id relates to a property ID in another table, not unique as multiple services against one property.
The problem that I have is that each prop_id can have more than one service_date for the same service_year. I need to be able to find the latest service_date on a prop_id and its associated service_year and service_id.
Any pointers would be greatly appreciated. BTW, using this in Access 2003 & 2007