Queries And Forms Need Help Geting An Understanding
Jun 11, 2007
Hello i am a college student and i was just getting on with my database assignment. i don't want any one to do it for me i just want someone to point me in the right direction. i am making a basic Encyclopedia on planets for my project. i have made one form which i want all my other forms to look like. this form is getting its information out of queries. this form is opened when i click a picture of the planet. so i was wondering if it was possible if i could link all the picture up to the one form but each picture opens a different set of data with in this form. i have already thought of just copying the forms and changing the control source. just wondering if there is a different way of doing it.
I was hoping you guys would have a recommendation. Do you know of a book or web site that offers workbook examples to help practice learning how to understand existing databases?
My biggest problem with databases has never been designing them but learning how to follow someone else's work. Some place to practice on this deficiency of mine would be great. Any thoughts?
I have tried to figure out what the difference in functionality would be between 1 and 2. In the latter I have just created a series of relations between the last two tables (but that structure could be used throughout the whole DB instead of hanging it together with one relation between tables as is the case for the rest of the DB in this picture).
So there can be three different situations with this table structure: - linking all tables together via multiple relations - linking all tables together via a single relation - a combination of both, such as in the images.
Why would someone choose one versus the other? "Which is better in what situation?"
I've been fiddling about for a while now, but in a structure such as this it doesn't seem to make a difference. Or maybe there are differences in use/design, but on a level that I have not explored yet.
Could anyone explain this to me a bit? I have multiple books, all of which handle basic relations but I have not yet seen an explanation for this question. I think understanding the whole relationship topic is critical to be able to see other pieces of the puzzle fall into place later on. And as of yet, this is really fuzzy to me.
Omits records that contain duplicate data in the selected fields. To be included in the results of the query, the values for each field listed in the SELECT statement must be unique. For example, several employees listed in an Employees table may have the same last name. If two records contain Smith in the LastName field, the following SQL statement returns only one record that contains Smith: SELECT DISTINCT LastName FROM Employees;
If you omit DISTINCT, this query returns both Smith records.
If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results.
The output of a query that uses DISTINCT is not updatable and does not reflect subsequent changes made by other users.
So, from what I understand of this, if any of the fields in the record (or row, whatever) repeat then it is not displayed. Correct me if I am wrong.
The problem is that when I try and use it in my DB it return all the fields regardless of the Distict clause, unless I am only querying one field, which is next to useless.
For example when I make a test table that has repeating words in one field, with a unique ID for each one, the distinct clause works when I display the words field alone and not when I display the ID field as well.
Can anyone explain what is happening or what I am doing wrong?
I have to create reports from an Access database used in a commercial application. The backend uses Access but the front end does not.
The vendor does not document the data or provide any support for that. The database has over one hundred tables and thousands of fields.
I'm thinking I'll create small transactions in the application and then study the database to see what has changed. But this seems overwhelming considering how many tables and fields there are. So I'm looking for advice on how to approach this.
ok, let me please ask this question regarding a payment relationship in my database
it will be paid in four ways, cash, creditcard, cheque, paypal
each of these payment types will have their own features..
eg creditcard (name on card, card number)
cheque (cheque date, date cleared)
paypal (username...YOU GET THE PICTURE
so is this below way a good way to store these details, or can i do it with 4 less tables..
http://bb.1asphost.com/antonyx6/untitled2.jpg
if this way is correct, i dont see how each id will be entered.. eg if i enter some cheque details.. will that lead the chequeid to be linked to the paymentid, and the paymentid to the bookingid, and the bookingid to the booking details etc
I am building a database which tracks tasks assigned to employees. I want to have one of my forms displaying a view which shows at a glance which tasks are assigned to whom at what times of the day, with a click opening a task for editing. My current attempt is to create a crosstab query with the employees as the column headings ("dynamic crosstab" query) and a table of time values for the hours of the day providing the row headings, with the task as the value.
I then created a form based on the query with Datasheet view as the default view (apparently necessary to set properties/event handlers to query values. I got the idea from the Northwind sample database) However, the form fields do not update to reflect changes to the employee list and therefore the onClick handlers are not applied to newly added employees. What to do, besides for programmatically setting the event handlers?
I have a couple forms with Combo boxes that look up data in queries. I noticed in testing that I could accidently type a "~" (and other characters) in the field and once I do, it causes a runtime error that shuts down the entire app (as opposed to letting the user backspace out of it or resetting the field). Before I put in some "Before Update" code to prevent the system from crashing, I want to make sure I am on the right track. I have already set the combo box to just show the list, not allow edits, limit to list, etc. Is there a way to prevent the user from doing this either by preventing the keyboard from working on combo boxes (not preferred) or by trapping it before the system crashes? I would think I could validate with a recordset, and create my own path out for the user, but I was hoping the combo box would have sort of done that work for me.
How to make my queries run when they are in a navigation subform. I am having a hard time finding something on line that explains this process well? I keep getting prompted to enter a parameter when i go to the form in the navigation setup, I have the query running off a combobox in he stand alone form, and i can bypass the error if i make the form a popup, but i want to learn how to keep it as part of the navigation form itself.
Is there any way to make a form that will allow me to select the query which i wish to use, rather than just having a different button that will execute each query individually?
I have a database that I use to track my workouts. I am very new to Access and am just starting to learn some simple VBA code.
I have the following tables:
tblBodyFocus Body Type
tblExcercises Excercise_ID Excercise Body Type (Foreign)
tblWorkoutLog Log_ID Excercise_ID (Foreign) Date
What I would like to do is generate a query that will give me a summary of workout dates and Body Type. Each date can have several excercises but each excercise only has 1 body type associated with it. What I want to avoid is multiple listings for body types. In other words I want to generate this:
Date -Body Type 10/25/05 -Arms 10/25/05 -Legs 11/2/05 -Arms 11/2/05 -Abs
but not:
Date -Body Type 10/25/05 -Arms 10/25/05 -Arms 10/25/05 -Arms
I'm new to this so I may not have given you enough information. I don't have any code to post at this point but please let me know if I you need any additional info.
Hi All, 1st post or should it be last post..:) I am a complete novice with access so please be gentle. My problem is simple well not to me. I have a small db in access 2003 with which i created a form with 6 buttons each to supposedly run a querie..the queries have all been written previous as the db was originally created in access1.0. I am unfortunately unable to do this can anyone point me in the right direction please...thank you Williebear
I don't know if this could be done but is there a way that a form can be created where users type in Employee Info such as name or SSN and it calculates the total number of vacation, sick and person time he has left.
I've already created the queries, but i don't know how or IF i can integrate the two.
There are several tables Employee SickTime PersonalTime VacationTime
For each employee in the "Time" tables, a sum of their time taken and time accrued is calculated to provide the total time available.
I've read quite a few posts and noticed that quite a few people have said that forms should be created from queries. Is this personal preference or a must.
Like I said on a previous post I am going on a one day Access soon, so maybe I should stop reading and playing until I have completed that, but my mind is running away with what I want to do.
I am trying to open an Access database which was created a few years ago... When I open it to edit one of the forms I fail to see the forms etc that are in there.
Is there a hot key you hold down when opening the database to be able to open Access in full edit mode showing all forms queries etc
I have just put together a small database, and have used the user-level security wizzard to set up groups so i can restrict access to the database.
I have also been through all the user and group permissions and tried to set the security up so that the data entry staff can enter data but not change any of the forms or queries.
This is where i am hitting the wall.... although i have set up this protection, when i access the database under the staff name and password I am still able to view the forms and queries in design view and change them.
Can someone plaese point out where I am going wrong
I'm new to the forum as well as I am new in using MS Access, so please don't lough :) I wasn't even sure whether this is the right subforum to post to....
Anyway here it goes....I have this really big database (nearly 5000 records) and I was wondering how could I make a query, perhaps outside of the Design View, and making it appear like a form e.g. let's say I want the query to display in form style (or even report...) the rest of the fields concerning a certain record when I just give the ID number of a person.
I am working on my first access project (please be patient and try to explain like to a child). My problems however are past the basic level so please bear with me ....
I am working on a datbase which deals with laboratory data from patients which have different date stamps - ie. each record has a 'valid time' (time of blood drowing) a 'transaction time' (time of entry into the DB) and 'delete time' (deleted recordes are just given a date and not really deleted).
The idea is that the user should be able to watch the status of the DB at different past dates (ie what did the doctor know about a patient blood tests a month ago when he made the decision to give him a medication which eventually harmed his kideneys). The user should also be able to change values of records, delete records (which means to add a date to the deleted date field) and to add new records.
I want to do it all using one form.
Things I have done so far: 1. Query by form which locates a specific record (Main-Form & singleRecQuery) 2. A subform in Main-Form which displays the results of singleRecQuery (Single-subform). The query and subform aer updated by a macro once I enter all the data into main-form. 3. A qury by form derived from Main-Form which locates a range of values (Range-subfrom & Range-Query)
My problem spans both forms and queries (so I am posting in both forums): 1. I want to create an update query that will draw its data from Single-subform (the form which displays the results of singleRecQuery). I have created the qury without a problem but the fields in the form are locked for entering data (maybe because they are bound). Is there any way to enter data in such a form which is based on a query and then use the new data in another query ? (I tried allowing data entery in the forms properties but it deos't work).
2. I have tried to put a criterion in the queries on the "Delete Date" field so that the quries will not display the deleted records (I have tried "is null" "is empty" and "is missing") but when I run the query with these parameters I get no records back.
3. In the form I have a command buton which runs a macro that runs the qury and then update dthe subform. Is there a way to get this button to run different macros after different events in the form or do I have to add a button for every specific task I want.
4. As I have mentioned the Main-fom is supposed to run 3 differnt queries and display the results on the same form. I know how to make a subform invisable but is there a way to place all three result subforms in the same spot ?
I have a form and its source is a query. There are only 5 things on the form that can be modified, the rest is just displayed.
the 5 things are within a table b. In my design view i link up table b with the already made crosstab query. (everything within the crosstab query are just for display on the form). Within the design view I put * from the crosstab query (everything) and then the fields from table b (all 5 of them).Everything on the form is working and is linked up properly, but when i go to type in them it is locked. (well they arn't within the property), but i cant type in them. This is for all 5 fields on the form.
When i then go back into the source and remove the crosstab query, i can then type into the fields.
Any ideas?
** p.s When i create the same query normal it doesn't allow me to type in the datasheet view either **
I am creating a program that will allow the user to select a sale and pull up only those product items associated to that sale in the details. Any help would be appreciated. Then with in the details section the user will/should be able to enter in the Quantity needed to order. Any suggestions on how to do this. My tblSale does provide the data needed so how do I get the data to go to the form?
I am new to this forum so if I have not asked properly I apologise now.
The Database is attached. Form Orders is the form that I am using to accomplish my task. The getproducts4Sale Query is the information needed.
I currently have a query and a table which I'd like to create a form for. The query has people's information in it and the table has RSVP information in it (if they need parking, etc.). I'd like to create a form so that I can type in the person's name and then it brings up their address (just to confirm it's the right person) and then i can enter in the RSVP information. Any suggestions?
I have a form that allows me to sell animals in a group. While some fields, (i.e. buyersname, sellingunits, datesold) are good for all, some fields (i.e. AnimalID, SellingWgt, Sellingprice, Picture) need to be specific.
Having said this, Using a query for GroupSales, I filled a list box of all the animals that are to be sold. Below the list box two command buttons (1-ALL) and (2-by Group).The only difference being here, is that by clicking cmdALL all animalIDs listed in the list Box will be highlighted. The By-Group means the user has to highlight the IDs they want to sell..
In either event, there will multiple IDs. If I can use arrays here, I could include the other fields for each animal that has been chosen. These would then be updated/appended to other tbls.
I am building a very simple Form that will check prices against my main database. So far the user can input a barcode into a text box, which then drives a query which in turn opens a datasheet with the results.
What i'm trying to do is get the query to return the results in a Subform on the original form. But currently i can't get the subform to refresh upon entry of a barcode into the text box.
Can anyone help? Apologies if my explanation is not very clear.
I have a query which shows data according to a field on a form. I am trying to get this across the department on the intranet, so I replicated the form as a data access page but i can't see how to link the Criteria row on the paricular column in my query with the field on my data Access form.
Am I missing something, like data access forms are only for displaying or something ? :o
If you have a form that is based on a select query that has selected no records, the form will display as totally blank.
One way to avoid this is to ensure that the query is always able to select at lest one record; but Is there a way a message can be displayed if no records are selected?
