Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
skythexmoliron
Starting Member
10 Posts |
 Posted - 2004-06-29 : 09:03:42
|
| Alright, basically, I have several questions as per adding options to my database:First - I have several thousand codes that translate to a certain name. I have this in another table, where this table has a one-to-many relationship with the main table. I need to create a report that converts the numbers to the appropriate name, but I do not want it to replace the numbers in the main table. I'm not quite sure how I would do this without having to use a replace function. Second - On this same form, I would like to create a single form for a single report; this said, I would like the form to designate what the user would like to sort by.For Example:The user selects "Activity Codes" from a combo box, and therefore the report is generated sorted by Activity Codes. The rest of the report will remain the same - saying that the above question would apply after I made this selection. I'm going to suppose that it will just be a 'Forms!Form Name!Form Source' sort of thing, and I'm sure I could figure this out, but anything that would spare me a headache is always nice. Also, I currently have a form that filters based upon user input; the report should have the same sort of functionality. The current form stores the SQL code into a query, so I'm going to guess that I can reuse this for my purposes by the copy/paste method, as I don't really know how to declare a global variable in VB. Any information there would also help. I'd prefer to be able to refer to the query through several buttons that I will be adding to the form without having to copy/paste each time, but either way is sufficient. Third - After I create this report and it reports the figures, I would like to report the sum of each column that it reports. I know I can, again, create a query for this, but it would be very complex (as far as I know) by basing it on the filtered query. That would be a lot more VB than I'm interested in programming, as this is meant to be a fairly simple database. I would prefer making it based on user-input all around, though, as I am only a temp here and won't be around forever to show them how to use the appropriated forms etc. Plus, it will help me to learn more for future uses. Fourth - Why do my queries occasionally return nothing at all when I, in fact, know that there's at least one thing for it to query? It's quite frustrating; I've created several reporting forms that end up being null because of this, and there is no apparent reason. I once had a working report of the sum, but it would occasionally report blanks, which didn't make sense.That's all I can think of for now. Any assistance in all or one of the areas would, of course, help me out greatly. I'm starting to find that it's not worth my time experimenting to prepare this query and the last resort is to create 18 different reports for all of the possibilities then having a complex and unorganized switchboard. I'd rather keep it at one form, one query, one report. I have the form doing what I want, the query doing the same for the most part, but the report is giving me issues. Help would be appreciated! Thanks |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-06-30 : 05:13:38
|
| If you can supply sample input data, sample DDL and sample (matching) expected results...then this might move to a resolution faster....without seeing raw data in the flesh you're asking us to do a lot of 'inpaid' imagination.....if you're worried about confidentiality....dummy in made-up customer/staff names, etc.....Search here for examples of the sort of information I'm talking about....especially as espoused by tduggan...the keywords "DDL, CREATE, INSERT" should bring up some examples. |
 |
|
|
skythexmoliron
Starting Member
10 Posts |
Posted - 2004-06-30 : 07:25:06
|
| Ah, of course, my apologies. The information is semi-confidential, so I'll keep it low-key. Alright, here goes a possible example:Here is the Form I have and the SQL Code for the 'apply' part of the filter. Caps signifies a button.Year: _______ (Combo Box, selecting from 2003/2004)Filter Source: _________ (Combo box, selecting the column for which to look)Filter: _________ APPLYthen in the apply, here's my code:Private Sub Command8_Click()On Error GoTo Err_Command8_Click Dim filterSQL As String Dim Source As String Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim List As String Set db = CurrentDb Set qdf = db.QueryDefs("FilterQuery") Source = Forms!Filter!Source filterSQL = "SELECT [" & Source & _ "] FROM Fy0469all, Fy0369all WHERE [" & _ Source & "] LIKE '" & Forms!Filter!Filter & "'" qdf.SQL = filterSQL If Forms!Filter!Year = "2004" Then GoTo Year_2004 If Forms!Filter!Year = "2003" Then GoTo Year_2003 Year_2004: DoCmd.Close acTable, "Fy0469all", acSaveYes DoCmd.OpenTable "Fy0469all" DoCmd.ApplyFilter "FilterQuery" Exit SubYear_2003: DoCmd.Close acTable, "Fy0369all", acSaveYes DoCmd.OpenTable "Fy0369all" DoCmd.ApplyFilter "FilterQuery" Exit Sub Exit_Command8_Click: Exit SubErr_Command8_Click: MsgBox Err.Description Resume Exit_Command8_Click End SubThat is the format and code for the form. Now, say I insert this into the form:Year: 2004Filter Source: Activity CodeFilter: AThen, hitting the apply button, it would open the appropriate 2004 table and filter in Activity Code where Activity Code = A.This is essentially all I have working. By hitting the 'apply' button, the SQL is stored in a designated query. On this form, I would like to add a button that opens a different form where you would select how you would like to group the data in the report. For example, clicking on the button would bring up this form, from which a person could create a report based on the filter criterion:Sort by: ________ (Combo box) GENERATESelecting, for example, from this formSort by: Activity Code GENERATEClicking GENERATE would create the following report based on the filter criterion:Year (Which would be obtained from the filter criterion)Activity Code Supervisory Office Charter Number RegHrs. >RegHrs. ... Charter Number RegHrs. >RegHrs. ... (It would then group by supervisory office)Of the 400000+ records I'm working with, it would only report the information based on the criterion. In this report, however, is another problem. The "Supervisory Office" is reported as a number in the main table, and, as I said, I have another table that translates the number to the name of the office. I would like for the report to display the office name, not number. Then, after each "Activity Code", or rather user-input, it would report the sum of the hours, looking something like this:YearActivity Code Supervisory Office Charter Number RegHr. >RegHr. ... ... Charter Number RegHr. >RegHr. ... SUM(RegHr.) SUM(>RegHr.) ...And, of course, those sums would be based on the Activity Code (Or really the User-input). No idea where to start on that one. I would know how to do an overall sum, however. This feature isn't absolutely necessary, but it would be a 'nice thing to add.' |
|
|
|
skythexmoliron
Starting Member
10 Posts |
Posted - 2004-06-30 : 07:43:21
|
| Sample Run-Through:Year: 2004Filter Source: Project CodeFilter: LSAMClick GENERATE REPORT button. The Following comes up, and I select:Sort by: Activity CodeClick GENERATE. It generates a report that looks like this:Year 2004Activity Code A______Sup.Off.______Charter#________RegHrs._______>RegHrs.____...______Dallas,TX_____2122____________80____________40__________...____________________3221____________55____________4___________...______London________3433____________86____________20__________......____________________________________221___________64__________...Activity Code B...I do not want the report to report null values. With this example report, I would be able to tell how much time was spent on project 'LSAM' and where it was being spent (Since the report was based on a filter based on 'Project Code' LSAM). I have, again, the filter part of it working fine, and my code is displayed above for the 'apply' button. Being as this is what I want to do, I ultimately would have 2 Forms, 2 Queries, and 1 Report. (Query for user-input filter, another for hours sum) Hope this helps resolve to a quick resolution. I think I could figure it out in time, but I would rather tap available resources and spend my time on other work in the mean time. Thanks for all your help! |
|
|
|
|
|
|
|
|
Several Questions