Using SQL In Acess???

Apr 10, 2007

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

View Replies


ADVERTISEMENT

MS Acess Db Security - Help!

Jun 19, 2007

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

View 4 Replies View Related

BCP Equivalent In MS Acess

May 20, 2005

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

View 1 Replies View Related

Acess Numbers

Nov 30, 2004

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

View 4 Replies View Related

Concatenate In Acess

Apr 4, 2006

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]

View 1 Replies View Related

Can Anyone Do Me This Acess Project With In 48 Hours

Dec 12, 2007

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.

View 14 Replies View Related

Acess Problem With Relationship

Dec 14, 2007

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

View 2 Replies View Related

Query Is Too Complex In Ms Acess 2.0

Jun 13, 2006

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")

View 3 Replies View Related

Parameter In Queries - Acess 2002

Feb 7, 2006

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)


===============================================

View 2 Replies View Related

Parameter In Queries - Acess 2002

Feb 7, 2006

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)


===============================================

View 2 Replies View Related

Query Behavior Acess 2003

Aug 31, 2007

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.

View 1 Replies View Related

Help With Outer Join Query In Acess 2003

Apr 11, 2007

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

View 5 Replies View Related

Um... Acess Hangs Temporarily When My Mouse Hovers Over 'Print' Icon

Mar 20, 2006

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

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved