I need some help here, I am in the final design stages of my database… I am working on creating some reports and I need some help getting the info I need in the right place. Let me summarize the problem, then I will give you a rundown of my table structure and what I need to do, finally explain how far I am at this point.
I need to create, a complex query for use of a report I am creating. Here is a summary of what info the final query needs to include:
CurrentRank / LastYrRank / BusType / BusName / BusPhone / etc. / Cat1 / Cat2 / Cat3 / etc.
*******************
Here is my table Structure
*******************
Now, each BusinessType has several associated BusinessCategories (3-8)… all of the businesses use that set of categories to create a unique set of categories along with a value and a year. So, all businesses with a common businessType will have a common set of categories and values, and will have a set for each year
Additionally each business within a businessType is ranked by those categories for each year. (I am currently setting the business rank by year manually, though I will eventually create a module to do it for me.)
With me so far?
*************************
Here is The Report I need to make
*************************
This is great… it is almost finished… Now, I need the list to include the current year and previous year rank (lets say 2006, 2005 respectively) and sort the list of businesses according to the current year rank… (in case of rank ties sorting by business name) to end of with this:
CurrentRank / LastYrRank / BusType / BusName / BusPhone / etc. / Cat1 / Cat2 / Cat3 / etc.
That is where I get stuck! Can I create a nested Crosstab?
I have tried to create a query using CTOGolfCourses and tblRank linking BusinessID then crosstab that query to get the results I want, however it just ends up creating a records for each business type to have every available rank with every available year, so when I crosstab it, it ranks all the businesses as #1 for both years.
If I can lay this out manually, I know that I can code this using VBA to generate the exact reports I need, using the varying criteria, however I cannot quite get the results I need manually.
Wow, this is a long post! Thanks for hanging in there! I really appreciate any help you can give on this complex problem!
I have a query that when I run it normally (just click on it) then it runs fine. (It is a union query, getting it's data from 8 other queries (who has their dependancies)
But when I want to run a report from it, Access gives me an error saying "query is too complex".
I am flattered, but I would prefer access to work than say I write stuff that is too complex for it. :cool:
Any ideas?
I am confused by the fact that it runs when I double click the query, but the report bugs it out.
TRANSFORM Workersdetail.workername AS CountOfedate SELECT Workersdetail.[attendance], Count(Workersdetail.[edate]) AS [Total Of edate] FROM Workersdetail GROUP BY Workersdetail.[Workername], Workersdetail.[attendance], Workersdetail.[workerhourenter] PIVOT site+Cstr([workerhourenter])
I wanted to know that, is there any option through which my report gets autoupdate or refresh incase of addition in SITE field (as mentioned with PIVOT)?
I've got a self updating crosstab query, its essentially a monthly summary and every month a new column is added (one corresponding to the current month, i.e., next month the new column will be may, following that the new one will be june, etc)
I've designed a report to be based on this query and i tested it out by manually adding data for next month into a table, the query auto updated however the report remained the same (ended in april instead of adding a new column for may).
Just curious if there is a way to automatically add these new columns to the report every month or will i have to do so manually?
I then created a 2nd select query with inner joins to join both crosstab queries on similar fields (activity & projectname).
SELECT QUERY 2: projectname, activityname, employeename (totalhoursworked as value), employeename (calculatedcost as value)
It gives me this:
However, I want it like this:
Those employeename... refers to more employees being added after a period of time. Hence I want to know if I could use vba to generate a report every time a button is pressed on a form? I know how to link the form to the query.
I want to create a report for the Monthly cleaning plan of a hotel. For each day, how many rooms need new sheets, how many need new towels etc.
At this point I can generate a report for any given day.
This could be an example of what I want to achieve
Code: ------------ Date | Date+1 | Date+2 | Date+3 New Sheets 2 1 0 2 New Towels 1 3 0 1 Full Clean 0 1 2 0
"Date" is a date tat you can set, after which you'll get the following 30 days("Date+1","Date+2" etc)
I thought that a CrossTab query would give me what I want,but using the wizzard I can't get the result that I want. Haven't worked with crosstab queries before so maybe I'm doing something wrong, or maybe this isn't even possible with a crosstab query.
I am creating a crosstab query in VBA to report on claims on a paid and incurred basis. I would like the query to have 13 columns - one for each month of the current year and one for all claims paid prior to January of the current year.
Is there a way to lump all data with a date less than Jan 1 into a single field while retaining the monthly detail for the current year?
All of the data is coming from a single table. Sample code below functions, but provides a column for every month a claim was incurred.
Sub Triangle() 'Triangle Reports 'Check Registers
On Error GoTo Error_Handler:
Dim DB As DAO.Database Dim RS As DAO.Recordset Dim QRY As DAO.QueryDef
I have an Access application that print a report with 7 sub-reports in it. When I preview the report, it looks fine. When I print it directly to the printer, it looks fine. The problem is when I print from the preview, Access error with a "...too complex..." error and crashes the application.
Hi all, I am utterly unsure if what I want to do is even possible:
I have two crosstab queries, qryRewCOCredit and qryWrapCOCredit which show the changeover (CO) times for the specified machine when they are NOT zero. (all zero entries don't show up).
There are many cases when there is a CO for the Rewinder on a specific day, but not for the Wrapper, and vice versa.
I want to make another crosstab query which performs a calculation. To keep it simple:
If (RewCOCredit>WrapCOCredit) Then 5-RewCOCredit Else 'WrapCOCredit>RewCOCredit 5-WrapCOCredit
it is selecting "EN-02", "EN-04", "EN-05" as a value.
The problem occurs because there is no control source for a value in the crosstab.
The problem is that if the query returns and there is no value for "EN-02" then the report fails and say that it can't recognize "EN-02" as a valid field name or expression in the report because there is no value in the query for it.
How can I get the report to load the returned values no matter what is returned from the query?
ie if there is no control source, set the value to say "No flow"
If intControlCount < intColCount Then intColCount = intControlCount End If
' Fill in information for the necessary controls. For i = 1 To intColCount strName = rst.Fields(i - 1).Name Me.Controls("lblHeader" & i).Caption = strName Me.Controls("txtData" & i).ControlSource = strName Me.Controls("txtSum" & i).ControlSource = _ "=Sum([" & strName & "])"
Next i
' Hide the extra controls. For i = intColCount + 1 To intControlCount Me.Controls("txtData" & i).Visible = False Me.Controls("lblHeader" & i).Visible = False Me.Controls("txtSum" & i).Visible = False Next i
' Close the recordset. rst.Close End Sub
The bold text should give me the details, but it does not show on the report. I can only see the header.
I am using the following cde to generate a Dynamic Crosstab Report. I can get the column labels to work but Ican't get the values to appear in the detail section all I get is #Name? I am using the following code in the report
Private Sub Report_Open(Cancel As Integer) Dim rst As DAO.Recordset Dim db As DAO.Database Dim i As Integer Dim j As Integer Set db = CurrentDb Set rst = db.OpenRecordset("select * from test2") rst.MoveFirst j = 0 i = 0 For i = 0 To rst.Fields.Count - 1 If rst.Fields(i).NAME Like "*test" Then GoTo skip_it j = j + 1 Select Case j Case 0 Me.Field0.ControlSource = rst.Fields(i).NAME Case 1 Me.Field1.ControlSource = rst.Fields(i).NAME Case 2 Me.Field2.ControlSource = rst.Fields(i).NAME Case 3 Me.Field3.ControlSource = rst.Fields(i).NAME Case 4 Me.Field4.ControlSource = rst.Fields(i).NAME Case 5 Me.Field5.ControlSource = rst.Fields(i).NAME Case 6 Me.Field6.ControlSource = rst.Fields(i).NAME Case 7 Me.Field7.ControlSource = rst.Fields(i).NAME Case 8 Me.Field8.ControlSource = rst.Fields(i).NAME Case 9 Me.Field9.ControlSource = rst.Fields(i).NAME End Select skip_it: Next i rst.Close Set rst = Nothing End Sub Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer) Dim rst As DAO.Recordset Dim db As DAO.Database Dim i As Integer Dim j As Integer Set db = CurrentDb Set rst = db.OpenRecordset("select * from test2") rst.MoveFirst j = 0 i = 0 For i = 0 To rst.Fields.Count - 1 If rst.Fields(i).NAME Like "*test" Then GoTo skip_it j = j + 1 Select Case j Case 0 Me.Label0.Caption = rst.Fields(1).NAME Case 1 Me.Label1.Caption = rst.Fields(i).NAME Case 2 Me.Label2.Caption = rst.Fields(i).NAME Case 3 Me.Label3.Caption = rst.Fields(i).NAME Case 4 Me.Label4.Caption = rst.Fields(i).NAME Case 5 Me.Label5.Caption = rst.Fields(i).NAME Case 6 Me.Label6.Caption = rst.Fields(i).NAME Case 7 Me.Label7.Caption = rst.Fields(i).NAME Case 8 Me.Label8.Caption = rst.Fields(i).NAME Case 9 Me.Label9.Caption = rst.Fields(i).NAME End Select skip_it: Next rst.Clone Set rst = Nothing End Sub
What else do I need to do to get data in the detail section In anticipation
I have a crosstab Query as the source for my report, of course the issue is the column headings on the report. I have Purchasers as a row heading, Year as a row heading, Month/Year as a row heading, Meter as a column heading, PaidMCF as Value and, a total as a Row heading. My issue is feeding the column headings on my report with the meter names.
Purchaser 1 has 23 meters attached Purchaser 2 has 1 meter attached Purchaser 3 has 6 meters attached Purchaser 4 has 2 meters attached Purchaser 5 has 16 meters attached Purchaser 6 has 11 meters attached.
I have a crosstab query which returns values in a range of categories. I have 5 categories. My problem is that sometimes a category may not return a value in 1 or more of the categories. This means that when I am displaying this data in my report I haven't got the data in the correct columns.
In the underlying queries for this I tried using a LEFT JOIN forcing the category, but when I executed the crosstab I got errors in all fields.
I am playing with a report. Crosstab report works, but I would like to print check mark if value is "X". Other values are "A" and "E" and they need to remain as they are.
I've made a crosstab query and would like to use it to create a subreport. In the column headers I have names of courses. Courses can be added or removed. How can I make a crosstab report with dynamic columns?
This code runs to the set frm part then i get a type mismatch? ive tried a few different things and still nothing?
Code:
Private Sub Report_Open(Cancel As Integer) ' Create underlying recordset for report using criteria entered in ' EmployeeSalesDialogBox form. Dim intX As Integer Dim qdf As QueryDef Dim frm As Form ' Set database variable to current database. Set dbsReport = CurrentDb
Hello , I had incountered with a huge problem in my project and I need your help guys. I have a huge table contains alot of data about many people- I wanted the data will be checked and sent to a query. Here is an example for a little project beacuse I couldn't have the big one. (This example works fine) My big project is pretty the same but after I try to get the query out I get an error that the query is too complex....(It's really too big when you have 20texts to be checked with 9000 fields) If you check the query fields you may see how the OR is getting separted and because every text has OR statement everything is hanged by everything. Someone told me that I can't use query options and mannge it and I should use VB SELECT option - but I can't make it work too. So I can't show you the real example at all beacsue I can't have it to my computer and It will take alot of time to have 9000 different cells ; SO if someone knows what I am talking about and ever encoutered it , I would be really thankful ! (Also - You may see some problems with the OR ("") but I mannged it to work with the BIG project so it doesn't matter) I don't want you to focus the conditions but just the problem itself ...." the query is too complex"...
This one is a pain in the but. I tried everything, but why would it just won't work. It always says the query is to complex. Have included the database, anyone a solution?
I need to perform a query on a database (not designed by me) that is not normalized. In fact it is only one table with numerous fields (many of them Date/Time). I need to query the table based on the date fields only.
Basically the table is used to track when specific functions are completed to determine the time required to perform the individual tasks (accuracy to one day is acceptable) and find the ones which are falling behind. Since several individuals are required to complete each project each step has an average value (based on historical data.)
The following is a short narrative:
1. Step 1 is scheduled for 1/5/05 and the task is not started untill 1/7/05. This is not acceptable. There is a 1 day window. I need to flag this record (via report) if the start date exceeds the schedule date by more than 1 day. If a start date has been entered this record does not need flagged (regardless of the alloted time).
2. Step 2 is based on the amount of time alloted to complete the task once started. If the task takes more than 2 days the project needs to be included in the query for the report. As above, if the date is entered the record is not needed since there is no need to try to determine the when the project will start.
3. Step 3 measures the number of days from the project completion untill the product is sent to the central office. If the time exceeds 2 days the record needs to be included in the dataset. Again, once a date is entered in the received field there is no need to include the record.
There are a couple more steps but they follow the same criteria as the first three.
I have not been able to figure out how to get these requirements entered into the query design view.
The query I'm trying to get is: Who owes me money for the service and How much.
Each car had several treatments in the past and some have missed a payment or I didn't notice and just issued a new order.
I need a query that runs through the entire database, does a summary of all the amount owed to me per Car, and then deducts the total payments made per Car. If the balance is > than Zero,. Show me that car and the bottom line amount.
Hi guys, long time surfer, first time poster here :)
We use a prehistoric Process Plant design software package, and it stores all its information in Oracle 8i.
A report I need to pull takes data from a stack of tables, each with a unique number.
PDTABLE_113 contains a list of models, each model having a unique PARTITION_NO.
Every PARTITION_NO entry has a matching table called PDTABLE_21_XXXX where XXXX is the PARTITION_NO of the model. Each PDTABLE_21_XXXX has a row for every piece of equipment in the relevant model.
PDTABLE_21, for argument sake, has the columns EQUIPMENT_ID, EQUIPMENT_DESCRIPTION, and EQUIPMENT_STATUS.
Is there some way to query the database so I get something that looks kinda like this:
this shows that I can have multiple entries in a day for a particular serial number.
I need to select ONLY ONE serial number and corresponding data for each day (or week).
I tried to format the date to remove the time and then select the distinct date (so 1 record a day for each serial was displayed), this worked. BUT I could not link it successfully to other tables because I had to format the corresponding match date to avoid a type mismatch thus invalidating what i was doing by selecting the distinct record.
Here is the original query i was using that selected a range of dates (which included multiple dates in a single day with a single serial).
SELECT TBLRemoteUnitInfo.TankSize, TBLRemoteUnitInfo.TankName, * FROM TBLRemoteUnitRequests, TBLRemoteUnitInfo WHERE (TBLRemoteUnitRequests.RemoteFeildUnitID = TBLRemoteUnitInfo.RemoteFeildUnitID) AND (TBLRemoteUnitRequests.SerialNumber=:SerialNumber) AND (TBLRemoteUnitRequests.DateReceived BETWEEN :Datestart AND :Dateend) ORDER BY TBLRemoteUnitRequests.UnitRequestID';
how can i take a sample of the range of dates (ie 1 a day/week or month), i assume i need to create a filtered table via query first then query that table.
Its killing me!
if i am being unclear please let me know and ill try to clarify
I have come to a dead end with my query. Any help would be really appreciated.
I have a query which i have written:
SELECT TblBurnleyWwTw.MetricID FROM TblBurnleyWwTw WHERE (((TblBurnleyWwTw.[Data Source])="OMS")) OR (((TblBurnleyWwTw.TAGFunction)="CHP")) GROUP BY TblBurnleyWwTw.MetricID, TblBurnleyWwTw.[Metric Required], TblBurnleyWwTw.CALCULATIONS, TblBurnleyWwTw.PIPointSource, TblBurnleyWwTw.PILoc1, TblBurnleyWwTw.[High Level KPI] HAVING (((TblBurnleyWwTw.MetricID) Like "130*") AND ((TblBurnleyWwTw.[Metric Required])="-1") AND ((TblBurnleyWwTw.PIPointSource)<>"L"));
This retreives all codes relating to what i want. The problem is, I need this data to try and match within another column of calculations E.g. Data retreived from query I have may be 13001, 13002, 13003. What I would like to do with this data is to lookup in a calculations column if any of the above data matches to bring back the metric ID which is realted to it?
Hope i have stated this clearly enough for anyone to understand???
As i say any help or recommednations for a solution would be really appreciated
SELECT [UK Table].[Business Name], [UK Table].[Business Type], [UK Table].Address, [UK Table].City, [UK Table].Country, [UK Table].[Telephone Number], [UK Table].[Website Address], [UK Table].[Email Address] FROM [UK Table] WHERE ((([UK Table].[Business Name]) Like [Forms]![frmNz]![txtBusinessName] & '*' Or [Forms]![frmNz]![txtBusinessName] Is Null) AND (([UK Table].[Business Type]) Like [Forms]![frmNz]![txtBusinessType] & '*' Or [Forms]![frmNz]![txtBusinessType] Is Null) AND (([UK Table].Address) Like [Forms]![frmNz]![txtAddress] & '*' Or [Forms]![frmNz]![txtAddress] Is Null) AND (([UK Table].City) Like [Forms]![frmNz]![txtCity] & '*' Or [Forms]![frmNz]![txtCity] Is Null) AND (([UK Table].Country) Like [Forms]![frmNz]![txtCountry] & '*' Or [Forms]![frmNz]![txtCountry] Is Null) AND (([UK Table].[Telephone Number]) Like [Forms]![frmNz]![txtTelephoneNumber] & '*' Or [Forms]![frmNz]![txtTelephoneNumber] Is Null) AND (([UK Table].[Website Address]) Like [Forms]![frmNz]![txtWebsiteAddress] & '*' Or [Forms]![frmNz]![txtWebsiteAddress] Is Null) AND (([UK Table].[Email Address]) Like [Forms]![frmNz]![txtEmailAddress] & '*' Or [Forms]![frmNz]![txtEmailAddress] Is Null));
What is wrong? I have attached the database. The form frmNz is what I want to work. I should be able to enter company information into at least one field, allowing me to retrieve the specific company data from the UK Table in the subform.
