I need to show field values in relation to dates, but I want the dates to be shown horizontally in the report.
I tried the crosstap quarey but it is not working for me, as i need to report many unrelated raws.
I've attached brief explanation of what i want.
I have a report that lists part numbers. With beside the part numbers are the components that go into the part. It looks something like this:
Part # Component Part 1 Component Part 2
1 abc
abd
2 abg
3 abc
abd
abg
abf
Part 1 has 2 components (abc,abd) how can I get these to display side by side.
Products report
shows on report as:
Order no Product
Order no Product
Order no Product
eg.
012345 table
012345 chairs
012345 lamp
I want to show
Order no Product, Product, Product
eg.
012345 table, chairs, lamp
I have a listbox inside name of workers which is coming from table (Workersdetail) on the otherside I have report from the same table which still showing all records of worker, this report execute through the form and by selection of listbox values. look into my following codes.
Dim strWhereworker As String
strWhereworker = "workername = Forms![workermenu]!workerslist"<--(Workerlist is the name of listbox)
Select Case Me!reporttoview
Case 3
'DoCmd.OpenReport "workerindividual1", acViewReport, , strWhereworker
Problem is, in the report all workers with related values showing while I want to get only the worker name I select from the listbox, I could not understand where I made mistake.
I've created a query that runs from a form and I would like the data to be shown subtotaled on a report.
Basically, I have Year Group, Behaviour Type, Behaviour Location and I would just like subtotals and %'s for each change. So, for example, there may be 1000 records in total 200 in Year 3, 500 in Year 4, 100 in Year 5 and 200 in Year 6. I would like to show this in a table with %'s as well as figures if possible (a pie chart is the eventual aim!)
Then I would like to subtotal the same records for the changes in Behaviour Type e.g. Bullying, Verbal Aggression etc to find out how many issues of each type have occured and the percentages.
I can get this information in a report using the group feature but it's a real mess and it's broken down far more than I expected.
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
View 1 Replies View RelatedI have only one bound text field in my report. By default, it shows the results (150 records ) vertically. I wish to show them horizontally, 5 in a row. How to do this.
View 11 Replies View RelatedI have a table with columns
position, DateTime, value
(datetime being a text field by the way, not my fault :-))
I wrote a simple pivot query
TRANSFORM Avg(value) AS AvgOfvalue
SELECT Position
FROM table
WHERE (dateTIME)>format(Date()-2,"DD-MMM-YY")
GROUP BY position
PIVOT dateTIME;
The idea was that I could report each last 2 days with this query.The pivot query works OK.Creating a report based on this query, the control source of the fields is linked to columns like "12-JUN-15 07:00" which will not be available after some days.Isn't it possible to create a pivot report on a pivot query based on dates?
I need the total of days in a report but exclude the repeated ones.
So user are working sometimes in different work orders on the same day but our administration only needs to know the number of days worked in one period of time.
i send a jpg with the example i use the =Nz(Count([Date Worked]),0) but that way i get all the entries counted
Any code for viewing Report between two dates. So when you open the report it will ask for the starting date and ending date.
View 4 Replies View RelatedIs it possible to list out each consecutive date in a report when given a range of dates without having to create a table of dates?
For example, given the date range 12/01/2013 to 12/05/2013:
In report:
12/01/2013
12/02/2013
12/03/2013
12/04/2013
12/05/2013
Is it possible to do this dynamically in vba?
I want to ultimately join this to a recordset for the report.
I would like my user to be able to enter dates on a report.
I have entered unbound fields on the report; but it doesn't keep any of the information (dates) for printing or otherwise. (I don't need to save these dates since they change monthly.)
I've restructured my 'application' to have one table and a lot of query/forms. In light of not figuring out the combo box situation I altered the requirements and what I needed thinking I may have an easier go of it, but alas not exactly my experience. I have 5 reports that I want to filter them by week(start of the week). I have a function which can turn any regular date entered to the start day of the week(monday). I thought my best way about this would be to fill a combo box with all the accepted dates through a separate table's column.
Is there a way to pop a dialog box when the user clicks to generate the report that will prompt them to choose a week then run that criteria against the report and only bring back that date?
I managed to create a report by user using a combo box. When an user selects a name from the combo box, it generates a report showing all the records by that name.
However, now I need to be able to generate a report as above but between 2 dates. How??
I am having two sets of criteria.
1) by user
2) between 2 dates
Can this be done?
In the test db included the report works fine if every Rep (representative or User/Admin) has records assigned. But, for example, if I go to the table and re-assign the first record to another user/admin (pick list in the table field [Rep]), then `User01’ will have no record assigned at all. Then an error message appears: The Microsoft Office Access database engine does not recognize 'User01' as a valid field name or expression. And no report is produced.
I wanted to try solving it with the info on thread searched by: "cross tab query reports", (13th result) but to be honest I really don’t know how to implement.
I have a retort that I want to use an option button to filter the report is a specific date is 48 hours before the current time. I'm not having any luck. Below is the code I have been playing with, but no go.
Private Sub NoMove_Click()
Select Case Me.NoMove.VALUE
Case 1
Me.Filter = "CLM = 'Now() - 48'"
Me.FilterOn = True
End Select
If Me.Filter = "" Then
MsgBox "The filter returned Null"
Else
End If
End Sub
If you have a one - many relationship between data, normally in the table it is captured, you would find multiple rows for every 'thing', each row detailing different 'functions' performed on it.
Is there a way that one can display these functions horizontally, instead of vertically, i.e, have one row per 'thing', instead of multiple rows.
Let's say I have:
Customer Table - CustID
Product Table - ProdID
CustomerProducts - Contains the CustID and ProdID
I have an "input" form with Customer Name, Address, etc and a subform that allow selecting his/her products.
I want to DISPLAY (only display), in another subform, on this form, (not a report), every customer that has a product in common (even if just one) with the main form customer. Also need it laidout this way:
For example, if Joe Robins has the following products selected: celery, lettuce, tomatoes. The following customers would appear in that subform - because they have ONE or MORE products in common with him:
John Doe (carrots, celery, potatoes)
Mike Smith (carrots, radish, tomatoes)
but this customer would not show up:
Mary Lamb (potatoes, cauliflower)
Is there an easy way [in a query or a series of queries?] to display this:
Code:
Branch Officer Total Loans
1 ABC $5,678,555
1 DEF $6,678,555
2 GHI $7,678,555
2 JKL $8,678,555
2 MNO $9,678,555
Like this:
Code:
Branch Officer1 TotalLoans1 Officer2 TotalLoans2 Officer3 TotalLoans3 . . .
1 ABC $5,678,555 DEF $6,678,555
2 GHI $7,678,555 JKL $8,678,555 MNO $9,678,555
There is an indeterminate and ever-changing possible-number-of-Officers per Branch.
If this is not possible using queries, is there a better way than writing code to loop through the recordset to get the desired output?I've done it before where I've written output to text and also to a table using VBA & recordset - but I'd like to know if there is a way I can get Access to do the 'heavy lifting'.
I need to create a query that strings data horizontally that corresponds to the primary key.
Example of what I am looking for
Fields:
Part_No,
Mfg_No,
Mfg_Name
Query Columns: Part_No, Mfg_Info (Mfg_No: Mfg_Name)
Also if the part_no (primary key) has multiple values I need the data to string horizontally like this:
Part_No, Mfg_Info (Mfg_No1: Mfg_Name1),
Mfg_Info (Mfg_No2: Mfg_Name2).......
I have been an MS Excel man all along my career and I am a novice in MS Access.I have created a table, [Initial Customer Approval] which records data from a Form, [Initial Customer Approval]. Once the data is entered in the Form, I need to do some calculations based on the data entered in some of the fields in the form.I created 6 different queries for the six possible values in those fields. now for each of those queries I created respective reports.I placed a Print command button in the Form.
1. When I press the Print button it should open the report for the current record in the Form. (Currently It Opens all the reports simulatneously, with only one relevant report containing the current record; other opened reports being blank.)
2. If user presses the Print button before pressing Save button then system should prompt user.
Here is the code (Please note [reference number] is the unique ID generated for each record entered in the tabe through form):
Private bSaveClicked As Boolean
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not bSaveClicked Then
MsgBox "You are trying to navigate away from the active record. Please either save your changes, or press ESC to cancel your changes.", vbOKOnly + vbInformation
Cancel = True
[code]...
I am trying to create a P&L statement in access. I know what I want to come out at the end. I am just starting to play with access and having trouble getting what I want to come out of it.
On the sales side I have a query that gathers all the revenue sources and calculates a total for each date. I then use a second query to just take out the data I want for the P&L report. I created a sub-report that displays the data I want. I use the grouping and grand total features to get the total into the report footer. So far so good.
On the expense side I created a form of a query to manage the one to many relationship to capture the data for expenses (one purchase with many line items). I created a query based of this query to get the relevant data for my expense sub-report. I created the sub-report and got everything looking and calculating the way I want it to. I use the same grouping and grand total features to display the data in the report footer. Still good.
I created a new main parent report with the two sub-reports (sales & expenses) on it and even was able to pull the totals from the sub-reports into the main (so currently the subtotals of the two sub-reports are displayed twice). Now when I try to use the textboxes I used to pull the sub-report totals into the main report to perform additional calculations (sales - expenses) I get #error. I have tried different things and gotten ?name.
Control source for the two textbox controls on the main that display correctly, but don't let me do any further calculations.
=[rptP&LExpensesOverview]![AccessTotalsAmount]
=[rptP&LSalesOverview]![AccessTotalsTotal Sales]
To do the subtraction I have tried using the references above, as well as just using the names of the unbound text boxes in the report that bring the totals into the main report.
As a work around, I tried to build one query with all the data from sales and expenses, but can't "filter" based on date and get the data I want in the query results because the two sets of data are not necessarily related. I either get a long list of records, or no records (I am currently only playing with about 5 days of data).
In my report I have date fields that span over 5 years with different pull dates ex. 3, 6, 9,12,24,36,48 and 60 months. I have successfully formatted these fields using the conditional format by:
Expression Is Date()<[9 Month] and Date()>[6 Month]
for the 9 month field and so forth for each field. This has allowed me to highlight the field that is the next upcomming date. One report I have this works fine for, very simple report. In a more complicated report, I have moved the fields together and have highlighted every other row to segregate them this formatting does not work. It will highlight the field, but it will pull in earlier dates, from 2004. Is there another way to format the fields with conditions other than the condition format? Possible to do it in code similar to highlighting every other line? The example I followed was here:
http://support.microsoft.com/kb/q210392
I will be out and about for a couple of days, but will return any replys to questions when I get back.
Thanks
I have an activity report which has multiple entries for the same date by club members. I have managed to build the database:
2 Tables
2 Queries
2 Forms
1 Report
Which list attendance's and activities by Member and Date and Prints each one starting on a fresh page..What I wish to do is count each Club Members total days attendance. I have tried using the Totals button but I don't know enough about expressions to get it to count multiple dates as 1.
Everywhere I have looked can tell me how to count between dates, around dates, workdays in a year etc. etc. etc. but none give me multiple dates the same counted as 1.
The aim of what I am doing is to create a monthly statement to give to our intermediaries that shows the commission they will receive each month for the deals they have referred. I have managed to create this report, HOWEVER I can't figure out how to filter out which month I need, so I a report for Jan, Feb Mar etc... The idea is that at the end of each month I need to run the report so only the latest month shows...
View 3 Replies View RelatedHow do I hide the report footer based on the report's data ?
I'm trying to hide if number of users = 1
The report's data is a query built inside the report's RecordSource, not a self standing query.