January 21st, 2006, 03:44 PM
Need help with MS-Access
I created a database to monitor the work log of my firm. In this the customers come enter their userid and other details.
Now, I want to create a query based on the user input! What I want is that when a customer comes back after submitting his job details, he enter his userid and then access displays all the jobs matching the userid!!
I think this can be done by some kind of user-interactive query.
Any suggestions how can this be done??
Remember, I dont want to use any other software except MS-ACCESS!!
January 21st, 2006, 04:01 PM
You need the following tables:-
Login table with three fields:- UniqueID, (autonumber), Username, (text), password, (text)
Jobs table with a UniqueID, (autonumber), UserID (long integer), and any other firlds you require for the job details
Go to "Create a new Query", select the two tables so they appear in the top area. left click and hold the UniqueID field in the Login table and drag it to the UserID field in the Jobs table. You will see a line join the two fields with an arrow at the Jobs table end. This is called a relationship.
Then drag the UserID and Username fields down into the grid at the bottom along with any fields you want for the results. When you put a userID in the "criteria" area and run the query then the results will only be those belonging to the UserID selected.
Now you can incorporate that query in any number of ways into your app.
Don\'t SYN us.... We\'ll SYN you.....
\"A nation that draws too broad a difference between its scholars and its warriors will have its thinking done by cowards, and its fighting done by fools.\" - Thucydides
January 21st, 2006, 10:52 PM
If we assume for a second that you already have all the necessary security setup for the DB and you have a table that is similar to tigers jobs table then actually the query is really simple. Try the following:
What this will do is every time the query is run it will first pop up a box asking for the user. It will then produce all the records in the jobs table that match the user_id.
SELECT Jobs_Table.Unique_id, Jobs_Table.User_id, Jobs_Table.Job_details, Jobs_Table.Other_details
Take a look at the file attached, run the report, i have only put in 4 records, and the user_ids are 1, 2 and 3.
Have a play