Access Queries

Groinshot

Hey, I'm really new to this, and I have bitten off more than I can chew. I have a pretty decent grasp of C++ for problem solving, but SQL is new to me, and I thought I'd be a little better at it. I'm trying to set up a database for a school library for a friend of mine who has put alot of faith in me for this and I don't want to let him down.The queries are boggline me. Is there any way I can combine queries to run consecutively. For instance- I want to withdraw a book, I key in the student number, and from that it selects the students names and the students number and inserts them into a new entry (have this bit) and then NEXT, I key in a book code, and it checks the book , and enters in the book code and the Book name, into the same entry as before, and another table is updated. The book names are coming from one table, the studentsdetails are coming from another, and being written into a third table. Is there any way I can do this?

ronkmonster

Not entirely sure, sql is tough unless you do a lot of it.

Can you not store the values you need for the 2 tables in variables

and then write them to 3rd table in one go

step 1: read student info and store in variable
step 2: read book info and store in variable
step 3: do an insert in 3rd table, with above details

Groinshot

Will give it a shot, thanks, I have alot of material on it, but struggling searching through it. Didn;t realise you could use variables in SQL, thought aliases were all you had, and couldn't reuse them except for later in the same query.

Apparently I can only store one value per variable?

ronkmonster

you can build sql strings and include variables in them. but it's considered bad practice. you should look into parameters.

building sql looks like this in c#
string myVar="ronkmonster";
string sql="select * from users where name="+myVar;

it's easy but a security risk so use parameters.


the way i've shown is just the most straight forward way to do it with little sql experience. you can test each section to make sure right values are loaded so easier to manage.

Seth Brundle

Groinshot wrote: »

Hey, I'm really new to this, and I have bitten off more than I can chew. I have a pretty decent grasp of C++ for problem solving, but SQL is new to me, and I thought I'd be a little better at it. I'm trying to set up a database for a school library for a friend of mine who has put alot of faith in me for this and I don't want to let him down.The queries are boggline me. Is there any way I can combine queries to run consecutively. For instance- I want to withdraw a book, I key in the student number, and from that it selects the students names and the students number and inserts them into a new entry (have this bit) and then NEXT, I key in a book code, and it checks the book , and enters in the book code and the Book name, into the same entry as before, and another table is updated. The book names are coming from one table, the studentsdetails are coming from another, and being written into a third table. Is there any way I can do this?

Use the wizard to create the join between your tables (assuming that you have the relationships set up).

Within the query Builder add something like the following as the criteria on the relevant field:

[Enter Name]

This will pop up a prompt dialog looking for a value which the user enters and the query returns results based on it.

Groinshot

Like the look of the last suggestion, I've decided to stuff SQL after this venture, so I'm gonna go for the point and click approach, rather than the C# approach. this is never gonna leave access, it's really basic, not going to go online either so no security threats are a problem.

Groinshot

Okay, I really don't understand this... I can;t figure out what I need to join, and what relationships I need to set up....

Dr.Silly

Groinshot wrote: »

Okay, I really don't understand this... I can;t figure out what I need to join, and what relationships I need to set up....

Hi there,

You really need to give us the table layout structure and columns, and exactly what you're trying to update/insert.
It sounds straight forward but without the details it will be difficult to help you.
Post it up and one of us will help you out quickly on this one.

Seth Brundle

http://cisnet.baruch.cuny.edu/holowczak/classes/2200/access/accessall.html
http://polaris.umuc.edu/~acarswel/ADMN640/AccessTutorial.html
http://www.brainbell.com/tutorials/ms-office/Access_2003/
and more...

Groinshot

Will look at kbannons links when I'm done posting here.
Fields I need to use

Table: Students
Last name, First name, StudentID, BooksOut, BooksRead

table :HistoryRecord
BookName, BookCode, Action, StudentNum,LastName, firstName

Table: BookRecords
BookName, BookCode, StudentNum, LastName, Firstname.

What I want to do, is set up a code that will read in a book code from the user, and find the title of that book, and take in a student Number form the user, find the name, and write them into the bookname, bookcode, studentnum, lastname, firstname fields, and change the action to withdrawn. I have all the dates set up already.
Thanks, I know it's a hell of a lot, but I relaly appreciate it.

Groinshot

Thanks alot to Dr.Silly who sorted me out with this, Thread can be closed

Groinshot

Hey again, just one more question on this. I now have two dates that I want to compare, I want a query to compare todays date to a date that was already entered in the form, and to tick a box if todays date is later than the date in the form..... Can anyone help me?

Seth Brundle

Groinshot wrote: »

Hey again, just one more question on this. I now have two dates that I want to compare, I want a query to compare todays date to a date that was already entered in the form, and to tick a box if todays date is later than the date in the form..... Can anyone help me?

use the following in the query field:

[Date Comparison]: iif(date()>[Forms].[frmForm].[txtDate], true, False)

frmForm = the name of your form
txtDate = the name of the textbox holding the date
Link the Date Comparison field in your query to the checkbox on the form.

Groinshot

Sorry, I must have mistyped that. In one of my tables, I have a checkbox (true or false value) which I want to set as true if todays date is greater than the date in the particular field. I'll have a look now at it and see fi I can figure it out with that, thanks

Seth Brundle

Is it an update or insert query?

Just use the stuff after the colon too populate the field in your query

Groinshot

Update query. Think I got it working anyqway thanks