 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2008monthly query
I want to ask the same criteria (to find items of a certain date) in 18 different fields. Is there an easy way, or do i just copy & paste? I want to run the same query every month, is there a quick way of updating the month, or do i have to enter the complete criteria every time.  SignatureJohnwats >I want to ask the same criteria (to find items of a certain date) in 18 >different fields. Is there an easy way, or do i just copy & paste? >I want to run the same query every month, is there a quick way of updating >the month, or do i have to enter the complete criteria every time. Do you have 18 date/time fields and need to query all of them? If so, your table structure is almost certainly WRONG. What's the structure of your table? You can use Access' builtin date/time functions to create a dynamic criterion. Since you chose not to post any information about *what* certain date you are searching for, I can't make a specific suggestion; but two hints - Date() will give you today's date DateSerial(Year(Date()), Month(Date()), 1) will give you the first day of the current month SignatureJohn W. Vinson [MVP] Thanks for the reply. It wouldn't be a surprise if the table structure was wrong, this is my first attempt. I have a group of students who can take a selection of 18 different modules. I have a table (& form) with all 18 modules on. I enter the date a student completes a particular module. Each month i want to query who completed a module that month. So the criteria changes each month. I can run the query & revise it each month, but i have to do it all manually. I am hoping there is a way of speeding things up. thanks John SignatureJohnwats > >I want to ask the same criteria (to find items of a certain date) in 18 > >different fields. Is there an easy way, or do i just copy & paste? [quoted text clipped - 15 lines] > > will give you the first day of the current month >Thanks for the reply. It wouldn't be a surprise if the table structure was >wrong, this is my first attempt. [quoted text clipped - 8 lines] >thanks >John Yep. Your structure is wrong (but it's a very typical mistake for people learning Access!) "Fields are expensive, records are cheap". What will you do when the administration cancels a module, or adds two more? Redesign your table, your form, all your queries, all your forms, all your reports? Ouch! Each Student can take many Modules. Each Module can be taken by many Students. This is a classic many to many relationship, and the correct structure involves THREE tables: Students StudentID <Primary Key> LastName FirstName DateOfBirth <other appropriate individual biographic data> Modules ModuleNo <Primary Key> ModuleName <information about the module as a real-life thing> Enrollment StudentID <who's involved in this module> ModuleNo <which module they are involved in> StartDate CompletionDate <other info, e.g. comments, satisfactory/unsatisfactory, etc.> If a student takes 11 modules, there would be eleven records in the Enrollment table. If you add a new module, you just need to add a new record in the Modules table; to find who completed which modules during a month, you have *ONE* field to search - CompletionDate - in a three table query. The criterion for "completed during the previous month" would be >= DateSerial(Year(Date()), Month(Date()) - 1, 1) AND < DateSerial(Year(Date()), Month(Date()), 1) on the DateCompleted field (though you can use this as a criterion on your 18 fields while you're still using this wide-flat spreadsheet design). SignatureJohn W. Vinson [MVP] I knew i was going wrong, but couldn't adjust my thinking. I was on the verge of giving up, but will now try again & hope i can put your advice into practice. Just a last (dumb) question. In your example, you had a primary key in the first & second table, but not in the third one. Do i need one there? Many thanks you have been a real help. SignatureJohnwats > >Thanks for the reply. It wouldn't be a surprise if the table structure was > >wrong, this is my first attempt. [quoted text clipped - 50 lines] > on the DateCompleted field (though you can use this as a criterion on your 18 > fields while you're still using this wide-flat spreadsheet design). >I knew i was going wrong, but couldn't adjust my thinking. I was on the >verge of giving up, but will now try again & hope i can put your advice into >practice. > >Just a last (dumb) question. In your example, you had a primary key in the >first & second table, but not in the third one. Do i need one there? Sorry! Yes, *EVERY* table needs a primary key. Contrary to the impression Access may give, it is *not* necessary that the primary key be an Autonumber; if you have (say) an administration-assigned student ID, or a stable, unique ModuleNumber, those fields would be perfectly suitable as a PK. >Many thanks you have been a real help. You're welcome! Good luck. SignatureJohn W. Vinson [MVP] Why have to have primary key? > >I knew i was going wrong, but couldn't adjust my thinking. I was on the > >verge of giving up, but will now try again & hope i can put your advice into [quoted text clipped - 11 lines] > > You're welcome! Good luck. >Why have to have primary key? So you can uniquely identify the record. If there is no primary key there is nothing to prevent you from having two, or three, or 871 identical records; a JOIN or a DELETE query will not be able to distinguish which of them you want. SignatureJohn W. Vinson [MVP] Even though index is set to yes (no duplicates)? > >Why have to have primary key? > > So you can uniquely identify the record. If there is no primary key there is > nothing to prevent you from having two, or three, or 871 identical records; a > JOIN or a DELETE query will not be able to distinguish which of them you want. >Even though index is set to yes (no duplicates)? That will help, but it will not allow you to establish referential integrity, whereas a primary key will. What's your objection to the PK? SignatureJohn W. Vinson [MVP] No objection - what you are saying that there must be a table with a field set to yes (no duplicates) and also set this field as pk. I have on at least one occasion used two or three fields as the primary key (individual fields could have duplicate records but the combination of these fields can not have a duplicate record). > >Even though index is set to yes (no duplicates)? > > That will help, but it will not allow you to establish referential integrity, > whereas a primary key will. What's your objection to the PK? >No objection - what you are saying that there must be a table with a field >set to yes (no duplicates) and also set this field as pk. I have on at least >one occasion used two or three fields as the primary key (individual fields >could have duplicate records but the combination of these fields can not >have a duplicate record). Sorry... should have clarified. Your table needs a Primary Key, but that key need not consist of just one field, it could have ten. SignatureJohn W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.