Feb 20 2012, 03:39 PM
I have attached a zipped Access 2003 file in the hopes that it will assist in someone understanding what I'm looking for. Also, should this be the wrong forum, feel free to move this thread to the correct one.
In the file I have some tables which contain the field "Freq". The contents of that field have been changed to things like 01A (annual, January), 03Q (quarterly, third month of each quarter), 05S (semiannual, starting in May). My ultimate objective is to somehow tweak rptWhatItemWhatMonthPerClient so that anything which starts, for example, with 03 will show up under a heading of March (or 03) instead of having a listing for 03S, a listing for 03Q, and a listing for 03A. In other words, everything which would be due in March would show up under one heading.
Please respond as if I am a neophyte, because I am. I'll probably need step-by-step instructions.
Thanks in advance for any and all assistance. If I need to provide more specific information, let me know if it's appropriate to do through this forum or through another method of communication.
Feb 20 2012, 03:55 PM
Copy the following into your Query, "qryItemsPerMonthPerClient" as a Field:
Feb 20 2012, 03:57 PM
I think what you want is the Left() function. Use it to select the first two characters in the string. That will identify 01, 02, etc.
However, you have a deeper problem.
This table is a good idea, but not well implemented. The "Freq" field contains TWO pieces of data, not one. It is never easy to work with data when you have fields that moosh multiple data points into one.
It should have THREE fields: the Primary Key, the actual "Frequency" and the "Start Month".
That allows you to identify all "Annual"tasks without regard to which month they start, and all tasks that start in a given month without regard to whether they are quarterly, annual, semiannual or monthly, etc. PLUS, it allows you to combine the criteria to get annual tasks starting in January.
In other words, the reason this query is tough is that the table design in not right. If you are not too far down the path to be able to correct the table design, I'd say that's the right place to start.
Otherwise, you'll be using some workarounds, like the Left() and Mid() functions to parse the data, and that's always more risky.
Feb 20 2012, 04:29 PM
Thanks for the prompt replies. The suggestions worked almost like I wanted. Now all I have to do is figure out how to have the months display in the report in actual month order as opposed to alphabetic order!
The table data was imported from an Excel spreadsheet, so that's why it isn't done precisely the way a database should be done. I'd wondered how to change the Freq field, and the given suggestion will be taken into account and hopefully successfully implemented. Thanks again for the help.
Feb 20 2012, 04:34 PM
To sort by date use the Function that George mentioned:
This will show the dates as numbers instead.
Feb 20 2012, 04:51 PM
Numbers successfully obtained.
As I was checking the results, I noticed that my totals were coming up short. I figured out that what was happening was that items which had semiannual or quarterly frequencies were only showing up in the initial month of the frequency; i.e., something listed as 03S was only showing up in March and not showing up also in September. What's the best way to design a table that has the following info:
01Q = Jan, April, Jul, Oct
01S = Jan, Jul
01A = Jan
02Q = Feb, May, Aug, Nov
02S = Feb, Aug
02A = Feb
03Q = Mar, Jun, Sep, Dec
03S = Mar, Sep
03A = Mar
Do I do one table with only the month numbers, another table with the frequency, then relate them to a third table which has the month names (separate fields or all in one field)? In my inexperienced thoughts, what George suggested would do what my current results are--show starting months only. Does his suggestion show items in all months tested (have to admit I haven't gone that far yet), or does there need to be a bit more manipulation?
Thanks again for all the help. I'm definitely farther along than I was.
Feb 20 2012, 10:35 PM
You've banged head on into one of the problems with trying to smoosh two different types of data into a single field. It gets crazy.
You can create a single table, I think, to manage this. It has one or two columns. I'd use two. The "FrequencyCode", which is "A", "Q", etc and the "FrequencyDescription", which is "Annual", etc.
There is a better way to handle this, but I need to know a few additional pieces of information to know how to proceed.
What are "LastPM" and "NextPM". In one table they are defined as dates, and in another as numbers. It looks like dates would make more sense, correct?
Also, you've set up three tables where one will do. The "inactive" and "active" tables are redundant. All you need is a flag field called "IsActive" in "tblYVFWC".
Tell me about the numbers in LastPm and NextPM and I can figure it out from there, I think.
Also, there is an "R" in some of the Frequency fields. What does that represent?
Feb 21 2012, 06:52 AM
When I imported the info from the Excel spreadsheet (which I got from a potential customer--attempting to bid a contract and figure out the best way to schedule things), I imported each worksheet as it was, hence the different tables. LastPM and NextPM are date fields; in one of the Excel worksheets they were Julian numbers, so that's what the scoop is there. Anything with an R I believe also has a text value of 00/00/0000. No PM needed or done. Actually, for the purpose of this question to the forum, the LastPM and NextPM fields can be deleted or ignored. However, if in the course of making a better database, you can provide corrections, I'm all for it. The flag for active or inactive makes sense, also. Again, I just imported from the spreadsheet. Except for actually eliminating having to enter the data, I'd bet that can (as it has) violate database sense more often than not.
Thanks for your assistance.
Feb 21 2012, 10:43 AM
Here is a Revision which should help you get the answers you need. I split the "Freq" field into two fields, "Frequency" and "StartMonth". I also added the IsACtive field. No need for separate tables.Click to view attachment
Feb 21 2012, 05:21 PM
Thanks. I'll give it a shot and see what happens. Appreciate your help.
Feb 21 2012, 06:01 PM
Continued success with your project.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here