I have a working report which groups by a status field. Let's say I have Status (AA, BB, CC, DD, EE). It is grouping and summarizing fine. I would like to be able to merge some groups. So I would like to be able to Group on (AA/CC, BB, DD/EE).
What would be the best way to approach this?
My report is grouped by a Billing Group #...within each billing group there are sub invoice numbers that are tied to the main billing group # (they all have the same billing group # but different general invoice numbers). I need to pull data if one of the general invoice # invoice dates is greater than or equal to a specific date entered.
In other words: If a Billing Group # and one of the Invoice Dates is greater than or equal to a specific date entered then pull ALL GROUP data to report.
I have a table of about 250 items that are sorted based on two fields. I created a query of the table and hide one of the fields because it is only there for sorting purposes to group the data.
For example, I have a "Type" field. In the type field, I differentiate the items with different categories, such as "Materials", "Safety", etc. My next field is "subtype" which assists the "Type" field being sorted properly for my needs. In the "Materials" category, all of the items are either "Fencing", "Plastic", "Wire", or "Miscellaneous". I am sorting the "type" and "subtype" fields in ascending order so that all the "materials" are grouped together, and all of the "fencing, "plastic", etc are grouped together within the materials field.
I am creating a printable report and I do not want the "subtype" field to be on the report, but I need the "subtype" field's sort to be affecting my data so it is grouped properly. I'm having difficulty, is there a way to hide fields in reports?
I am in the process of creating a training database that includes levels of proficiency with certain tasks for employees.
In one of my reports I would like to appropriately display with tasks the employee "Cannot Perform";"Can Perform with Assistance";"Can Perform Alone";"Trainer" (straight from the field list of the task). But I can't seem to get the hierarchy correct. Tried it in a PivotTable too as I thought similar to PTs in Excel you could get some kind of "count" of values. Couldn't make that happen either.
Each employee has a proficiency rating on about 20 different tasks. Proficiency input is controlled by a field list. I would like to structure this part of the report like so:
------------------------------------------------------------
EMPLOYEE PROFICIENCIES
--Cannot Perform Task
----Cutting
----Trimming
----Grinding
--Can Perform With Assistance
----Painting
----Fixing
----Drilling
etc. etc. So in this case the Field itself would become the value being grouped. I know there has to be some logic either in a query or SQL.
Despite Google I can't seem to figure this out.
I have some data in a format similar to:
Name / Style / description / speed / distance
john / driver / careful / 80 / 5500
mary / driver / careful / 70 / 7000
pat / racer / reckless / 100 / 6000
anne / driver / careful / 75 / 1000
peter / racer / reckless / 110 / 6500
don / snail / slow / 60 / 6000
I want my report to total by style, without details and to look like:
driver careful 13500
racer reckless 12500
snail slow 6000
How do you get a report to sum the group items by a specific item and to hide the details of that group summing?
Is there a way to have an expression in the control source of a text box in a report, that re-starts or is exclusive for every group within the report?
View 5 Replies View RelatedI have a query called "Program Status Count" that keeps track of the current status of various programs. There are three statuses: Closed, Deficient, and WIP.On the Query, I used "Total:" row and have two fields: StatusID field set to "Group By" then in the next column I have the same StatusID Field set to "Count" (it auto-renamed itself to CountofStatusID)...which on the query does what it is supposed to do:
Closed, 2
Deficient, 1
WIP, 1
When I go to the Report Design and try to make a Pie Chart (or any graph) it asks me what query I want to use, so I point it to the Program Status Count Query and select the only two fields I have in the query.This is the Row Source Code:
SELECT [StatusID],Count([StatusID]) AS [CountOfStatusID] FROM [Program Status Count] GROUP BY [StatusID];
Sometimes, it will show the appropriate fields but then it will disappear in Report View, switching to Print Preview does not fix the issue. Actually, it often deletes the correct information in the chart and use the PK autonumber instead of the words.Othertimes, the default data shows up and nothing I do changes it. I have even done the copy/paste of the query table in the report (doesn't work when the graph needs to update.
I'm working on a project where I must send roughly a thousand individual reports to a thousand different email recipients with .pdf attachment.I know I'll need to add a table that designates the email address by SHIP_TO_CODE. Then create a form with the email subject, body, etc.. I don't neccessarily have to have the reports saved to a folder; I really just need them emailed to each account.
I was thinking I could modify the code some to accomplish my goal, but I'm not sure what to put. Maybe add a SendObject in there somewhere.CODE that I have now that saves the report to a folder. (I want to modify some to send email attachement instead)
''Module CODE:
Private Sub Form_Current()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [SHIP_TO_CODE] FROM [qryWty&PendingData];", dbOpenSnapshot)
Do While Not rst.EOF
strRptFilter = "[SHIP_TO_CODE] = " & Chr(34) & rst![SHIP_TO_CODE] & Chr(34)
DoCmd.OutputTo acOutputReport, "rptDraft", acFormatPDF, "C:UsersmrutherfordDesktopASC Daily Reports" & "" & rst![SHIP_TO_CODE] & ".pdf"
[code]....
I'm working on a project where I must save roughly 1000 individual access reports based on the group to a specific folder in pdf format.
Problem:The code I found on this site works, but not exactly the way I need it to. Using the current code (pasted below) all of the pages of the report are being saved to pdf for each group, instead of just the single page. For example, all 1000 pages of the report are being saved to each pdf file, when I only want the 1 page for each of the groups.
I believe that the problem lies in the filter for the Open Event code, but I don't know how to modifiy it to make it work the way I need.
CODE:
Option Compare Database
Public strRptFilter As String
Private Sub Report_Click()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [SHIP_TO_CODE] FROM [qryWty&PendingData] ORDER BY
[code]....
I have a form that resembles a spreadsheet and have data in certain fields that is longer than the field is wide. (The widths are set according to client preference and widening the field is not the solution.) I was thinking perhaps I could use the status bar to show the data when the focus is on that particular field. Right now, the only help on the StatusBarText topic that I can find indicates that the status bar can be "programmed" with static text only, but I was hoping to make it dynamic, depending on the field in focus.
If this is the wrong approach (i.e. can't be done), does anyone have any ideas about how to use an external control to show (and possibly edit) data on a spreadsheet-like form?
The client has been using MS Works and is being dragged into Access only if the transition isn't too painful (for him, lol) and having an ability to look and feel like the Works interface is important to him.
Thanks,
- Terri J.
Basically working on a Customer Order Database for my carpet shop.
I have a Customer, Job and Order table. The order table contains the line items for each job and each customer can have many jobs.
I have a field in the Order table showing the item order status being either "Instock", "To be Ordered" or "Ordered"
I also have a Job Status field in the Job Table
I would like that Field to be automated depending on the item order status for each item on the job.
At the moment the Job status is a manually selected drop down box of either; 'Ready To Fit', 'Awaiting Stock', 'Items To Order' or 'Job Complete'
For example if all of the items had a item order status of 'Instock' then the Job Status would display as 'Ready To Fit'
If any of the items were 'To Be Ordered' it would display as 'Awaiting Stock'
I dont know how i would solve the situation of some marked as say 'In Stock' some 'Ordered' and some 'To Be Ordered' ????
Also the 'Job Complete' would come from another field in the Job Table marked with a check box...
When editing a record in Access 2003, the status bar would show the value of each field's description from the table design grid. Is there a way to get Access 2010 to do the same?
View 1 Replies View RelatedI have several buttons on a form and I want to set enabled = false on one of them when I open the form - that's fine
I have two txt fields - ReceiptNo and BankPaymtId if the both have a value I want to make the button enabled = True
This is the code I have used on the BankPaymentId On Dirty event
Private Sub BankPaymentId_Dirty(Cancel As Integer)
If Me.[ReceiptNo] <> "" Then
If Me.[BankPaymtId] <> "" Then
Me.CreateOutlay.Enabled = True
End If
End If
End Sub
This is the one for ReceiptNum on Dirty event
Private Sub ReceiptNum_Dirty(Cancel As Integer)
If Me.[ReceiptNo] <> "" Then
If Me.[BankPaymtId] <> "" Then
Me.CreateOutlay.Enabled = True
End If
End If
End Sub
Nothing seems to be happening.
Even the Form on Dirty is not working now....
I have a query that gets it's data from tblContacts. In this table is a yes/no field for archiving and the query gets only those records with a No in the field. The default is no. Next I have a form based on this query, which I am using to hold a set of command buttons which act on a contact chosen from an unbound list. So far so good but now I would like to add a command that changes the contact's status in the archived field of the table to Yes, so that I can use the form to view/edit, or email, or archive.
View 1 Replies View RelatedMy database has a text field "Status" where the text is either, A, W, C. There is a form to update this field; currently it uses a standard Access created text field. The users want a checkbox which will show up as checked when the status is W, if the status is anything else, the checkbox will be blank. If the user clicks the checkbox within the form the status will be changed to W.
If [table.status] = 'W'
then checkbox = 0
else
checkbox = -1
and then somewhere on the update it would be
if checkbox = -1 then [table.status] = R
Except that Access doesn't think the way I do.
I have a subform with two fields [status] and [process], i am trying to get the backcolor of procces to change to red if the status field contains the text "expired".
I have tried vba. plced on the after update
if me.status = "expired" then
me.process.backcolor = vbred
else me.process backcolor = vbwhite
end if
this
Changes the color of the field [process] but also all process field colors to red even if the status field does not contain "expired".
I think conditional formatting is the way to go but i think i need an expression to complete this.
I have started work on a database to track the many (several hundred per annum) projects my company undertakes. A 'main' table lists projects, their fees, their key dates, their project numbers, etc. I have created another table for tasks. Each task is assigned to a project (via the project number), and may be 'open' or 'closed'. I have separated the tasks table from the projects table as there may be several different tasks for each project. It is also good to keep a record of the tasks.
I want to prepare a report from the projects table that lists projects IF they have any open tasks.
In my mind, this means 'If a task, with the corresponding project number, is open, put a 'Yes' in the 'Tasks open' field of the Projects table.'
I have a question. Please see attached database.
I have an option group called is there another insurance
in the table in the description field i have 1=Y, 2=N 3=None
If you take a look a the report displays as 1 2 3
Is there anyway that instead of 1 2 3 it will display an X
And if none is selected will print nothing on the report.
One more question
I have a date field set as MM/DD/YYYY format
everytime i try to format it example =now
Display computer date as well as time
is there any way that i can post the date only.
I have an access report that I should change so that it appears like this:
card number, name and surname, Performance, run dates
The run dates for each performance should pick me printed on a pre-printed form in this way:
data1, data2, data3, data4, Data5, data6, Data7, DATA8
The data are drawn from a query.
But currently I access a single printing performance with data for single line.
There is' a way to group all the same performance and to print on a single line the different dates?
How should I proceed if it is possible?
I have a union query of the Employees table and Dependents table on [Employee ID] which I build for a census report, and when I run the query, it sorts correctly, but it's not sorted at all on the report. I tried to add a sort on the group, but it just ignores it.
I need it grouped by [Employee ID], then sorted by [Last Name], then [Relationship], then [Age], so it will be alphabetical by the employees last name, then put the employee first, then the dependents by oldest to youngest. Is there a way to do this in the code, and where would I put that?
In my report when the Group Header and the Detail section will not fit onto one page, it prints the Group Header on one page, and all of the Detail on another page. Is there a way to keep them together?
I have already tried the 'Keep Together' option in the Sorting/Grouping window, setting it to both Whole Group and With First Detail, but nothing seems to work.
My report has an empty Page Header, a Group Header, Detail, and an empty Page Footer.
I have a form with an option group. Within the option group I have a series of queries (all records, closed records, open records, etc.) to sort the data. Everything works fine.
My issue is a cosmetic one. When I change the query via running a report versus the option group, the option group doesn't reflect the correct status (e.g., I run a report for all closed records but the option group still shows All Records). In my VBA, is there a line I can write to change the option group appearance from All to Closed, without clicking on the option group itself?
I am completing a grouped report. For each group I would like to cut off the number of entries displayed at 3. I would rather not do this in the query because more than one report uses this query. Id there away of changing the report settings?
View 1 Replies View RelatedIn my Db , Med. Lab Results
in RptMain
I want HeadSecndGrop is Hide if :
SubName , has one related Service (test).
and
if
I haven't enter all Group members of SubName (like in orderID 1)
I currently have a report that generates information based upon a query. The report is broken down into 3 subgroups (Temperature --> Vzip --> VDD). The information within each subgroup seems to be correct as the report cascades through each grouping, however the graph does not update with the new information and instead just copies a clone of itself throughout each Temperature group in the report.
That being said, the first graph that is generated is correct for that group (Temp: -55)... but the report simply copies that graph into the next temperature categories.
I've attached a .pdf of a report generated to visually show what I am referring to, ** notice it replicates the same graph for each group **
I need to build such a report, Unfortunately, I do not know how to use VBA to create each group (grouping by type) had a different header.
The problem becomes bigger that everything must generate queries dynamically cross, the number of columns in a given type may vary depending on the number or the size of the products in which they occur.