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]
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
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
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 have a problem I am working and and am not sure what the best approach is, hoping someone can lend me some insight:I have two fields on a table:SerialNumber....LocationI need to concatenate these two (no problem there). However my situation is such that my serial numbers vary in the number of digits. For example:serial - 55124 (five digits)serial - 552356 (six digits)serial - 5514235625 (ten digits)the serial number field is 10 characters and the serial numbers can be anywhere from one to ten in length (this is external data I am working with, not data I created)When I concatenate the two fields I need the location value to always be in the same place, so if I have a location called - TEST - I need the end result of my concatenate query to place the location always after the tenth place of the serial number position, like this:55124_____TEST552356____TEST5514235625TEST(Without the underscores)Don't ask me why...it's a long story.
View 2 Replies View RelatedI have a table that I would like to concatenate three name fields:
Last Name, First Name, Middle Name. I inserted a field between each one to create a space when the fields are brought together. The table looks like this:
Last Name, Blank 1, First Name, Blank 2, Middle Name
When these five fields are brought together they can not exceed 25 characters, so the middle name will cut off when the field reaches 25.
Any help will be appreciated.
Hoping someone can help me with this:
I have a table with two fields that I am wanting to concatenate. The two fields are: Order and Line#. The line numbers are sequential by tens, like this:
10, 20, 30...etc.
So I have order lines that are from two to four digits. Like this:
20
120
1020
All order numbers are six digits. I want to concatenate the order and line (easy to do) my problem is I want to append zeros in front of any line number less than four digits. For example, using the lines above and an order number of 111111 I want the result of the concatenation to be like this:
1111110020
1111110120
1111111020
Sorry to ask...but I don't know how to do it.
Thanks for looking!
I have two fields in a table.
client_id and property_id
client_id is an autonumber with a format of 0000000 (meaning the first record is 0000001)
property_id is made up from the first 4 letters of and address line (for example Smith Street would be shown as 'Smit')
I am trying to 'concatenate' the two id fields together, via an expression in a query, to get 'Smit / 0000001' but can only seem to get 'Smit / 1'
How can I solve this ?
My database has FirstName and LastName fields.
I would like to initialize a new field called UserID with the first letter of the first name concatenated to the last name. I have found references that point to using Left([FirstName],1)&[LastName] but am unsure if this can be done in the table definition or if it must be done using a query. Either way I could use some advise on initializing this fields since I have 3500 registered users.
Thanks
Here’s my goal.
1.)I have one column and want to add a 633 in front of if.
a.Expr3: "633 " & [Grp] & ""
b.This only shows 633 and asks for an entry for the GRP? Why? I want the format to be 633 xxx. If I do add, lets say, 123 in the popup, all of the columns are 633 123.
2.)After combining the following, I want to take that number, and look it up in another query. If the number matches a number in the other query, than I want it to enter the number in column 1. If not, I want it write no number. I have gotten this part to work using an IIF function but I just wanted to let you know what I am after.
I have this query
SELECT [9A].DO, [9A].WORK, [BATCH] & " " & [SOURCE] & " " & [NO] & " " & [SEQ] AS RECEIPT,
FROM 9A;
It returnes the results like this:
RECEIPT
6/2/2003 P 29 10
1/15/2003 P 54 55
3/3/2003 P 42 7
1/6/2003 P 39 35
12/30/2002 P 23 30
What changes are needed to the query to return it like this:
06022003P0029010
01152003P0054055
03032003P0042007
01062003P0039035
12302002P0023030
In other words
The date must have 8 charactors with no /
Then P with no spaces before or after
The next number must have 4 charactors no spaces
and the last number must have 3 charactors
Thanks
I have a table with a bunch of columns (call it Table A), one of those columns being a look-up (foreign key) to another table (call it Table B). I would like to have another column in Table A that is a concatenation of the value in 1 column on Table A with the look-up value from Table B. What is happening though is a concatenation of the value from Table A and the KEY from Table B. I am not aware of a way to get the concatenate to use the lookup value instead of the foreign key.
View 1 Replies View RelatedI concatenated two fields in a query for a cbo, one field has cpt# the other descriptions. CPT# can vary with respect to maybe a four digit, five digit, etc. Because of this, when joining these fields, descriptions are not lined up. Is it possible to fix this?
View 8 Replies View Relatedi'm using Master and Detail table. Master table i have TaskId, ResId where ResId is mapped to Detail table which contains resource working on the task. i want to generate a query where for each task i want to display resource id in single column like below.
Task Id Resource Id
1 222,233,244,255
Do i need to use recursive query or any other method to get results like above.
Hey all, hope this question isn't too newbiefied :D .
I have a database where I must run 10 tests depending on which product I have selected (from a drop box). Each product requires different tests (of the 10) and I am hiding the tests that I do not need after the product is selected. The way I have it set up right now is I have 10 different If statements...one for each test on the "On Change" event.
I have been trying to reduce this down to one If statement nested inside a while loop, but my visual basic isn't very good. I have named the Test buttons Test3- Test13, and want to increment just the number of the test. However, when I try to plug in my concatenated string, I have problems. My code is as follows:
Dim testcount As Integer
Dim testcheck as Boolean
Dim testnumber as String
testcount = 3
While (testcount < 14)
testnumber = "Test" & testcount
If Me.selectedproduct.Column(testcount) = True
Me.testnumber.Visible = True
Else:
Me.testnumber.visible = False
End If
testcount = testcount + 1
WEnd
Me.testnumber is obviously not an object on the Form...so I guess my real question is, how do I get visual basic to plugin the variable testnumber so that the code reads Me.Test3.Visible.
Any help would be appreciated.
I have a table called Export_Car which contains Data such as License Number and Make and Model.I am calling a VBA function (in my query) that I have found online.This works fine and concatanates all the License Numbers for that Dealer.
ConcatRelated("License & Chr(13) & Chr(10)","Export_Car","[Dealer_ID] = """ & [Dealer_ID] & """")
My question is how do I add more fields to concatanate, such as Make and Model.
I would like to join 3 values to one new column called incident number. Problem is that one of the value is from a lookup field.
If the table look like
ID Type PNumber
1 INR 2477
I generated Incident number as [ID] "-" [Type] "-" [PNumber]. the result should be 1-INR-2477 but its shows 1-1-2477 because the Type column is a lookup field and INR is on the first value of the same lookup table.
How I could resolve this in the table or Query.