Im trying to calculate two feilds sounds easy right.
My numbers are stored 0000003000 and 0000002000
the feilds are Price and price two in trying to caluclate these but what im gettign is 00000030000000002000
how can i either check to add only numbers or add them and get 0000005000
this is money feilds
Hi all,
I am creating tables in SQL view in Acess, Have managed to create the tables, but am having a little trouble understanding what to do next?
I have an INSERT statement to add names, addresses etc, but cant seem to get it to work.
I am trying to insert into the SQL view in queries, thats where i have created the tables.
Should i use the same querie view or another one, or simply imput the information manually.
INSERT INTO Student ( Student_No, Name, Address, Tel_No, Course_No )
SELECT 'A001', 'Jones B', '12 New Road', '469006', 'HNC34';
this is the statment that i am trying to use:
hope someone can help
doj
Dear All,
I have a database with build in (access) users and passwords. I have a admin user but I forgot the password to this user.
Is it possible to retrieve this information somehow?
Thx in advance,
Dzordz
Hi Folks!
Need help! Is there a BCP equivalent in access. I want to load 2 million rows into an access database.
Any pointers will be of great help.
Thanks,
Moksha
Hi ...
I have a table called new items which has these fields
Name (Primary)
Serial No
Tag Type
Description
Status
I want the name to be unique and generates automatically and be SerialNo + Tag Type + Description <<<< NAME
I tried a querie which doesnt work
Code:[Description] & ", " & [SerialNo] & " " & [AssetType]
its creation of simple reservation. both files explain what should be included...bluprint is also included
http://rapidshare.com/files/76226655/Comm499i_MiniCases_Access_notes.pdf.html
http://rapidshare.com/files/76226689/Comm499i_MiniCases_Access_slides.pdf.html
15 to 20 mins if u know ur things
Please i will highly apprecite it..i don't wanna fail...helppppppp
10$ rewards as apreciation of your time.
All rite i get it..i guess i became the joke of the week..hehe..u guys must having a blast laughing at me...well yea i should do my work on my own then get help..
however i managed to do the tables but now i am stuck....only have few hours left..
Any idea why i can't do the relationship to none of the table...
it gives me error
the relationship works fine when
its
Enforce Referential Integrity is enabled, Cascade Delete Enabled
when
Cascade Update is also enabled then i get
"Invalid field definition 'Itemnumberid' in definition of index or relationship." for all the table
"Invalid field definition 'Itemnumberid' in definition of index or relationship." for all the table
http://rapidshare.com/files/76611241/reservation.mdb.html
How do i implant the followings ... i just don't get it how to ue it in acess
BUSINESS RULES
• Structural Constraints
• Derived Facts:
• Hotel total = hotel rate/night * # of nights reserved.
• Vehicle total = rental rate/day * # of days rented.
• Reservation total = hotel total + vehicle total + cruise rate +
flight rate
• Operational Constraints
• A person may make a reservation only if he/she is over 18.
• A person may make a car reservation only if he/she is over 21 and
holds a valid driver’s license.
• A person making a flight reservation to certain Island, can only pick up
a car, or book a hotel, etc, to that certain Island
Hi ,
I have two queries . one to list the data for the current month and the for the pervious month which I used to get a union query. From the union query I created another query which I had put in the report_open as a query def.
It works perfectly well on small databases upto 50 mb but when tested with 70mb databases ,report tabkes 30 minutes to execute and also throws the error"query is too complex"
please advise how I can avoid this error and also speed up the report.
Thanks in advance.
the code under report_open looks like this
Dim MyWorkspace As WorkSpace, MyDB As Database, MyQuery As QueryDef
Dim MySet As Recordset
Dim psSql As String
Dim inputno As Integer
Set MyWorkspace = DBEngine.Workspaces(0)
Set MyDB = MyWorkspace.Databases(0)
inputno = store
psSql = "SELECT DISTINCTROW QryRepCMISUnionLargeMovements.Customer_Name,"
psSql = psSql & " QryRepCMISUnionLargeMovements.CUST_ID, QryRepCMISUnionLargeMovements.CRG_CODE,"
psSql = psSql & " QryRepCMISUnionLargeMovements.CurrLimits1, QryRepCMISUnionLargeMovements.PrevLimits1,"
psSql = psSql & " QryRepCMISUnionLargeMovements.Lim1Var AS Lim1Var11, QryRepCMISUnionLargeMovements.CurrLimits2,"
psSql = psSql & " QryRepCMISUnionLargeMovements.PrevLimits2, QryRepCMISUnionLargeMovements.Lim2Var AS Lim2Var11,"
psSql = psSql & " QryRepCMISUnionLargeMovements.Input_Section"
psSql = psSql & " FROM QryRepCMISUnionLargeMovements;"
MyDB.QueryDefs("a").SQL = psSql
Set MyQuery = MyDB.QueryDefs("a")
I have upto 10 queries which in turn produces 10 reports. Each query has a 'current_year' parameter input by user. Storing 'current_year' value in table and accessing it in queries was an option which was discarded because of the possibility of inner queries using 'current_year' value from a table could slow down the query.
Is there any way to set parameter values for 10 queries in a single stretch. In all the queries parameter name is the same.
Here is a sample query. CURRENT_YEAR is the parameter
===================================
SELECT "1. Candidates in full time Education in Government Schools" as CAPTION,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR ) AS CURRENT_TOTAL,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR AND C_P='S' AND CENTRE <= 'BW835' ) AS CURRENT_NUM, Round(CURRENT_NUM*100/CURRENT_TOTAL,2) AS CURRENT_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 1 ) AS CURRENT_MINUS1_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 1 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS1_NUM, Round(CURRENT_MINUS1_NUM*100/CURRENT_MINUS1_TOTAL,2) AS CURRENT_MINUS1_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 2 ) AS CURRENT_MINUS2_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 2 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS2_NUM, Round(CURRENT_MINUS2_NUM*100/CURRENT_MINUS2_TOTAL,2) AS CURRENT_MINUS2_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 3 ) AS CURRENT_MINUS3_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 3 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS3_NUM, Round(CURRENT_MINUS3_NUM*100/CURRENT_MINUS3_TOTAL,2) AS CURRENT_MINUS3_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 4 ) AS CURRENT_MINUS4_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 4 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS4_NUM, Round(CURRENT_MINUS4_NUM*100/CURRENT_MINUS4_TOTAL,2) AS CURRENT_MINUS4_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 5 ) AS CURRENT_MINUS5_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 5 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS5_NUM,
Round(CURRENT_MINUS5_NUM*100/CURRENT_MINUS5_TOTAL,2) AS CURRENT_MINUS5_NUM_PERCENT, ROUND(CURRENT_NUM_PERCENT-CURRENT_MINUS1_NUM_PERCENT,2) AS DIFFERENCE1, ROUND(CURRENT_MINUS1_NUM_PERCENT-CURRENT_MINUS2_NUM_PERCENT,2) AS DIFFERENCE2, ROUND(CURRENT_MINUS2_NUM_PERCENT-CURRENT_MINUS3_NUM_PERCENT,2) AS DIFFERENCE3, ROUND(CURRENT_MINUS3_NUM_PERCENT-CURRENT_MINUS4_NUM_PERCENT,2) AS DIFFERENCE4, ROUND(CURRENT_MINUS4_NUM_PERCENT-CURRENT_MINUS5_NUM_PERCENT,2) AS DIFFERENCE5
FROM MERGED_TABLE AS A
WHERE YEAR In (CURRENT_YEAR)
===============================================
I have upto 10 queries which in turn produces 10 reports. Each query has a 'current_year' parameter input by user. Storing 'current_year' value in table and accessing it in queries was an option which was discarded because of the possibility of inner queries using 'current_year' value from a table could slow down the query.
Is there any way to set parameter values for 10 queries in a single stretch. In all the queries parameter name is the same.
Here is a sample query. CURRENT_YEAR is the parameter
===================================
SELECT "1. Candidates in full time Education in Government Schools" as CAPTION,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR ) AS CURRENT_TOTAL,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR AND C_P='S' AND CENTRE <= 'BW835' ) AS CURRENT_NUM, Round(CURRENT_NUM*100/CURRENT_TOTAL,2) AS CURRENT_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 1 ) AS CURRENT_MINUS1_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 1 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS1_NUM, Round(CURRENT_MINUS1_NUM*100/CURRENT_MINUS1_TOTAL,2) AS CURRENT_MINUS1_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 2 ) AS CURRENT_MINUS2_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 2 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS2_NUM, Round(CURRENT_MINUS2_NUM*100/CURRENT_MINUS2_TOTAL,2) AS CURRENT_MINUS2_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 3 ) AS CURRENT_MINUS3_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 3 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS3_NUM, Round(CURRENT_MINUS3_NUM*100/CURRENT_MINUS3_TOTAL,2) AS CURRENT_MINUS3_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 4 ) AS CURRENT_MINUS4_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 4 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS4_NUM, Round(CURRENT_MINUS4_NUM*100/CURRENT_MINUS4_TOTAL,2) AS CURRENT_MINUS4_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 5 ) AS CURRENT_MINUS5_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 5 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS5_NUM,
Round(CURRENT_MINUS5_NUM*100/CURRENT_MINUS5_TOTAL,2) AS CURRENT_MINUS5_NUM_PERCENT, ROUND(CURRENT_NUM_PERCENT-CURRENT_MINUS1_NUM_PERCENT,2) AS DIFFERENCE1, ROUND(CURRENT_MINUS1_NUM_PERCENT-CURRENT_MINUS2_NUM_PERCENT,2) AS DIFFERENCE2, ROUND(CURRENT_MINUS2_NUM_PERCENT-CURRENT_MINUS3_NUM_PERCENT,2) AS DIFFERENCE3, ROUND(CURRENT_MINUS3_NUM_PERCENT-CURRENT_MINUS4_NUM_PERCENT,2) AS DIFFERENCE4, ROUND(CURRENT_MINUS4_NUM_PERCENT-CURRENT_MINUS5_NUM_PERCENT,2) AS DIFFERENCE5
FROM MERGED_TABLE AS A
WHERE YEAR In (CURRENT_YEAR)
===============================================
I've just started using 2003 and had a query give me incomplete results. One table contains 6 numbers stored as text joined to the corresponding code in the data set table. Both fields are formatted as text. When using the table as criteria I do not get all of the expected records, I had to type in("1000", "2000",...) in order to retrieve all of my data.
Any ideas on what could be happening?
I use the text setting to avoid problems importing the data set which begins life as a csv file.
hello,
I have query , which has got 2 outer joins. The query is:
SELECT A.Project_ID, A.Title, A.comm1 AS Comments, A.Partner AS PM, A.Staff_Assigned AS TL, A.Contact_Name AS FL, A.MD, A.Status, A.Project_Type,
ISNULL(B.Delivered_Date, B.Delivery_Date) AS Start_Date, ISNULL(C.Delivered_Date, C.Delivery_Date) AS End_Date
FROM dbo.PROJECT A LEFT OUTER JOIN
dbo.PROJDATE B ON A.Project_ID = B.Project_ID AND B.Date_Type = "Start Date" LEFT OUTER JOIN
dbo.PROJDATE C ON A.Project_ID = C.Project_ID AND C.Date_Type = "End Date"
Can anybody help me out the error with this. I get an error : at dbo.PROJDATE B ON A.Project_ID = B.Project_ID AND B.Date_Type = "Start Date" .
Do I need to use any parantheris or change anthing.
Thanks
Hi there
Strange problem, i'm using XP Pro and Access from Office 2003 (both real full versions from work) - and in general use, when the mouse hovers over the 'print' icon (at the top left, undernear file, edit, view, insert, near 'save', 'new', etc), then access will hang for about 20 seconds then snap back into action.
Now, i don't even want to print anything! But every now and again i accidentally hover over it and it's bugging the hell out of me. Everything is updated (but i will check again now).
Anyone know how to make this stop? I tried a quick search but didn't get far.
Cheers!
Edd
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
Ok so in excel I have some numbers that are stored as text. The reason being that they are zip codes and some begin with 0 and excel doesn't want numbers to start with 0....so when I import these into an access field that has an input mask for zip codes...will it convert these correctly since the field is a text with input mask?
View 1 Replies View RelatedI have 2 fields that I would like to automate if possible
One field is called "p/o number" and another field called "line no"
These fields are part of an ordering database
Let say I have 200 items to purchase form 10 suppliers
And form example 20 items from each supplier
What I do at present is put the order number on each line item and the line number
example
p/o number line no
1 1
1 2
1 3
2 1
2 2
2 3
2 4
What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.
Example
1 1
2 2
3 3
2 1
2 2
2 3
I have a table with fields like this one but the weeks go all the way up to 52. What I am trying to do is count the number of consecutive zeros and if it is more than five, count how many of the following fields have a number in them and if that number is less than the number of zeros preceding it identify that person.
For example Joe would be identified below because he had 6 consecutive zeros and then he had 5 weeks of numbers immediately following the string of zeros. Bob would not be identified because he had 5 consecutive zeros and then 5 sets of numbers immediately following the string of zeros so the zero frequency isnt higher than the number frequency immediately following.
ID
Name
Date of Hire
1
2
3
4
5
[code]...
I am trying to write a function along the lines of the following:
If Me.Text1 > 0 but < 2 Then
Something happens
Clearly the top line isnt correct, so how do I write a function like that? Thanks
My brain has locked up, I just added a field to an existing db and I need to to hold exactly 8 intergers / numbers; typically the first few numbers are 0's, but the 0 do not display; still using 97, what is the field size/format/ going to be?
Thank you in advance
Not sure if this is easy or not, I have searched the forum but am not finding what I am looking for. I need to see if there is a way, in a query to extract data specific to either even or odd numbers. So if I have a field on a table with data like this:A01AA02AA03AA04AA05AA06AI want to pull just the even or odd numbered values.Any ideas??
View 5 Replies View RelatedIs there a way to write a query where it will only capture numbers? For example, if I have the following:
"Testing transactions 11100202020 in the following order"
What I would like to do is capture the numbers that is in the middle of the sentence. I have tried using the len, left, mid, right functions in my expressions, but the sentence length may change from time to time. Is ther a way to tell access to only capture the numbers?
Thanks in advance
mlr0911
The database that I will be referring to in this querstion ws developed to help track interviews at a local helth care facility (dates, times, location, ethnicity, age, state etc.)
I am trying to develop a few queries to pull certain information out this database. Specifically I am trying to pull out information in NUMBER or % format. Currently I can only figure out how to pull out the information that is currently listed. For example: We have had 57 interviews come into the door this month. All of these inteviews are going to different areas and they all are of different ethnic origin and all have an age difference. When I go to query how many total interviews we have had, I am getting names and not numbers. When I try to query how many different ethnicities have been interviewed, I still get names attached to what ethnicity they are.....so on and so forth. What I would like to do is turn these names into statistics or numbers. Is there any way to query this information and get it to come out in number format? The following is an example of different metrics that I am trying to get from this database.How many total out of state interviews per monthThe number of interviews per state per monthHow many african americans interviews peer per month, how many pacific islander interviewed per month, how many hispanic interviews per month........etcHow many internal candidates iterviewed per month/How many external candidates interviewed per month.The information entered into my fields are not entered in a numercial format. They are intered as names, places, dates, times, etc....My question is can I query this information in such a way that I can generate a report that would allow me to view it in numerical formatAny help will be GREATLY appreciated. Thank you soooo much!!!!!
Hello,I am new here and new to Access.
I have started to build a database that will be for storing the results of horse races.
However my problem arises when inputting the weight a horse carries.
The weight can be input as, for example, 11.12 or 11.00 or 10.10 however whilst I have no problem with 11.12 the other two appear as 11 and 10.1 is there any way round this.
Thanks for any Help
Treggy
hi,
I have a table that has results from races, eg.
Race No, Racer ID, Time taken.
i want to have a position field that will say which position the runner finished in. This is complecated as there are many different races.
At the moment the best thing ive been able to do is set up a query with a parameter on the race no. then the time taken is put in order and i have to manually put in the positions.
Manual is bad!
does any one have any idea about how i could make this automatic.
Thanx,
MAtt
Tried to search for this, maybe I am searching the wrong term.
Anyways.. I have a table linked from a large mainframe, and what I am trying to do is trim any numbers that are 1 million and over, and still keep it a number.
Currently I have this:
VEHICLE_KMS2: IIf([VEHICLE_KMS]>999999,Left([VEHICLE_KMS],6),[VEHICLE_KMS])
What I need is to have it remain as a number, seems that the left function turns it into a string value.
Thanks
I have 8 databases (A97) (don't even ask me why) they are identical.
I want to combine all the tables from them into one big table.
There are two tables 1 called Transactions and it has a transactionID (number) (parent of DetailID) and the other table is called Detail and it has a DetailID (number) (Child of TransactionID).
The problem is in all 8 databases the transactionID is an autonumber starting at 1 so I will have duplicate TransactionID & DetailID numbers. Each of the databases comes from one of eigh different "Regions" of our company and we assign Region numbers I can use as a prefix to the transID/detailID.
I tried to put a "25" (region#) in front of the one of the TransID# by using the format property in the table design and that seems to act as some sort of gost because while you can see it in datasheet view when you click on the cell the "25" disapears and all you can see is transID#1...#2....and it also gets dropped on paste append to the new combined table.
The question: is there an easy way to put a region number prefix on transID# so I can merge all 8 transaction tables together into one combined table?
Thank you