Reports :: List All Clients And All Months
Apr 11, 2014
I need to create a report from a query. I have created the query that pulls the below data from the relevant tables:
ClientName;invoiceAmount;InvoiceDate
Using the wizard I have created a basic report that shows the information in the query, but I need it to list all clients and all months - even if some months show no invoices sent.
Ideally it would show as follows (with an option for the relevant year to display):
______ Jan Feb Mar Apr May Jun Jul Aug Sep Oct Dec
Client1
Client2
Client3
Client4
Do I need to create a separate tables for Month/Year and link these to the query? Or is there a simple function I can put into the query to achieve this? It seems a simple task, but after playing for a week I've got no where
View Replies
ADVERTISEMENT
Sep 21, 2014
I have a report that has multiple clients on each page. In the report, some clients have data for a specific date while others do not.
I would like to be able to Save As PDF each page to a specified folder but only if that client has data in the report. I am currently doing this in Excel, but would like to move my data and reports over to Access. My Excel VBA code is:
Sub SaveAsPDF()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Range("B66").Value <> "" And _
ws.Name <> "Notes" And _
ws.Name <> "Lookups" Then
[code]...
Which says if B66 is empty then don't Save As PDF. Is this possible to do in Access?I'm very new to Access and have never used VBA in it before.I would like to be able to create a button in a form that when I click it will run this code.
View 1 Replies
View Related
Aug 5, 2015
I am printing statements for clients. Depending on certain criteria about the client, the format of the statement changes completely. This is just how the boss wants it, I have no control. I still want to generate all statements at one time (not separate reports).
I currently have this working using multiple subreports, and changing their Visibility property, only one being visible at any one time. This works, but it is very slow, I assume because I am basically making Access do triple the work (I have 3 subreports).
I pretty confident this is not the intended use of subreports. Is there a "correct" way to do what I am doing? I know I could just manipulate everything with VBA, but having subreports makes editing the different formats very easy.
View 10 Replies
View Related
Jan 13, 2014
I am running a report based on a query for a bunch of people who take medication. It runs the report and puts page numbers in the bottom right. Works perfect but I want it to do page numbers per person rather than the whole report. Is that possible to do or would I need to run some code to do them all individually?
View 1 Replies
View Related
Mar 19, 2007
Hey
I need to find Customers who have not had a meeting for over three months and i need to list them in a query, please could someone help me as this is getting very frustrating
View 14 Replies
View Related
Oct 12, 2007
Ok let me explainI have a list of 50 people who may have accessed events A,B,C at different times but what I want to do is to put a date in my form, run the quaery which will list all people who have Attended A,B or CWhen I do my normal query, I put the fields in, but if I put a date filter in Event A, it will not show up any person who may have attended event B but not Event A.I thought about running an append query but this would mean running the data several times with possible duplication of client names.Ideally I would like to have my report to showDate: 31/07/2006Name A B CK.Brown Y YJ Blogg Y YK Smith YK Ellum Y Ythen I can just use a count at the bottom :)The methods I have tired will filter event date A, but will not show J BloggIf I create another append for Event B, and append that data to a table, then I will have duplicaion of K Brown.. Any Help Appreciated.Last thing - when I run the append queriy and get my duplications, the fields where I had a Y/N option - if it was YES then I get a -1 instead of 1 ??? Why? and how do I make sure that when appending that the Y answer is a 1 not a -1
View 14 Replies
View Related
Nov 27, 2012
I have a table of data which includes a date field and also various other fields which may or may not be filled with data.I'm trying to populate a listbox with the months for which this data is missing (a separate macro will then loop through these months to fill the missing data) But I only want each distinct month to populate the listbox - not each individual date.
Code:
strSQL = "SELECT DISTINCT month([EntryDate]), year([EntryDate]) FROM [SampleTable] WHERE [ValueField] Is Null"
Me.lstSampleListBox.RowSource = strSQL
I want to return the month in <mmm yyyy> format.
View 2 Replies
View Related
May 7, 2013
I am trying to calculate the age on the day the report is printed in Years and months ( and display it thus) in a report.e.g. 6 years 4 months
View 2 Replies
View Related
May 18, 2014
I have a small clinic database. I've got tblAppointments to show AppointmentID and AppointmentDate among various other data, but only the dates matter for what I'm trying to achieve.
I'm trying to show a chart on a report that shows the number of appointments by months for this year and previous year. How can I do this...without using SQL, hopefully?
Here's what I tried: I made a cross-tab query to successfully show the years 2013 and 2014 in the rows, months in the columns, and number of appointments as values. But then, I didn't know how to graph it to compare the number of appointments for the two years by months.
View 5 Replies
View Related
Mar 22, 2013
I currently have a form where users can enter an "End Date", click a button, and it queries the data from 12 months prior to "End Date" entered.
Is there a way to force the query to show all 12 months, even if there are no records for a particular month? For example: if the user enters February 2013 in the date field, I would like the query to return:
March 2012
April 2012
May 2012
June 2012
July 2012
August 2012
September 2012
October 2012
November 2012
December 2012
January 2013
February 2013
...so even if June 2012 has no records, it is included in the query with a value of zero.
This is what I have so far:
WHERE (((Qry_Tbl_Assets.Dte) Between DateAdd("m",-12,[Forms]![Main_screen]![End_Date]) And [Forms]![Main_screen]![End_Date]))
View 2 Replies
View Related
Jul 15, 2013
I have created my tables and form and am now trying to run reports to organize the data. I have figured out how to group the individuals by group and treatment, but can't figure out how exclude individuals from the final list if they are already in another. As background, some of the individuals are eventually excluded from the experiment, though I keep the initial data. When the exclude individual checkbox is checked ("True" on the report) I do not want the individual to be listed in the "False" list, even though there are entries for that individual when that checkbox wasn't checked (when it was "True"). This is so I can get an idea of the current totals in each group.
Implant Period
>Treatment
>>Exclude individual?
>>>Individual ID
I feel like this should be a fairly simple task, but I cannot figure it out... Maybe there is coding to exclude individuals from the "False" list if they are already listed in the "True" list?
View 4 Replies
View Related
Jul 11, 2013
I am still trying to get a hang of development in access 2010.
I would like to design a form with a listbox or a combobox which holds all 8 of my reports (a table has all the reports), with a Print and a Preview view buttons. In addition, the user must be able to select if they want to view the report by month, quarter and the year in question.
How do i have a specific report print or previewed based on the value selected in the listbox or combobox and the date criteria.
View 4 Replies
View Related
Apr 22, 2015
I have a dropdown list of reports that is supplied by table. I also have listbox that are populate by another table. I want to be able to run the reports from the dropdown and from the listbox ....whatever the selection is to pull that info from the report and display.
View 3 Replies
View Related
Feb 9, 2015
I do not have the privileges to write code, but can use macros and Expression builder. I have written a bunch of queries and made reports for each of them. What I want to do is to be able to choose from the list of reports and view the chosen one. If that cannot be done without code, then to choose from a list of queries and then view the report made from the query. The user needs to be able to choose.
View 2 Replies
View Related
Apr 10, 2005
I am working on my DB for work and would like to display all of the report names that are listed in the Reports-Objects of my DB.
I would like to display a Form with two boxes. The left box would display the names of the reports as listed in the Objects-Reports. When a report is highlighted in the left box it would give a description of the report in the right box. When you double click on the report name it would open the report.
Since I am fairly new to Access could you give me an example of what I would like to accomplish? Your assistance is appreciated.
Thanks,
View 6 Replies
View Related
Dec 25, 2006
I have a table -'client' with 5 fields,
id
name
address
email
mobile no
I want to see only those clients who has email id.
I know its simple but not so simple to a layman like me :)
Please help
View 3 Replies
View Related
Jul 29, 2005
I have a database with a list of clients, their enter and exit dates. I need to do a query that will count how many clients we have on a specific date. Keeping in mind that clients leave. I can do a query that will prompt user to enter a date. However, how would I count the total number of clients on that date? thanks a lot for any help!
View 3 Replies
View Related
May 2, 2007
Hello, I am in the process of trying to get my database so i can access over the web.
Currently over the Lan I have the main database located on the server, and have created a replication for the two clients. This works over the lan where i can open the original and sync etc etc. How can i get this to work over the web so I have the same functionability as the lan method but over the web. I thought of some kind of interface but im not sure on how to do this.
Thanks in advance
Craig Clarke
View 3 Replies
View Related
Nov 18, 2007
Since I left school I have had to jump into application design on my own because I am finding it difficult to find work without 1 to 3 years of experience typically required by the current job market. This leaves me without any gut feeling for how to charge my customers. Charging by the hour is difficult to do fairly because I am not as efficient as more experienced programmers. Charging by the form, query, report, macro, or module, however, doesn't accurately capture the time variable associated with extensive scripting. Any guidelines offered in regards to charging and possibly ballpark rates would be hugely appreciated!!!
View 7 Replies
View Related
Jan 7, 2006
Hi, based on previous advice I had merged my customers and owners into one table and selected whether a client was a customer, owner or both via a lookup table.
This is working fine, however I must assign a rep to each owner, I'm not quite sure how to do this, I need to be able to assign a rep to an owner in the clients table, I must also make sure than a rep can not be assigned to someone who is just a customer obviously.
I have attached the database (http://jonroberts.redirectme.net/database.zip), hopefully that will make things more clear.
Thanks in advance.
View 2 Replies
View Related
Jul 3, 2013
I have a table that contains clients. There are a few select times when there will be one client organization but I will need to have two entries--say when one department has a separate contact & record of events than another department.
In most cases these separate departments can be treated as entirely separate clients however in some ways they should be counted as one--such as when counting the number of clients that we serve.
What is the best way that I can accomplish this?
My current idea is that when this situation arrises I have some type of command button that will create a new client record in the table, copy the information from the first record, then I can change it as need be.
I don't know if this is possible, how to do it, and how to set up queries that will recognize the two entries as one record. I don't even know if this is the best way to do it
View 2 Replies
View Related
Apr 9, 2013
I'm creating this database for a claims management company. Yesterday I learnt about making a many to one relationship between the claims and clients tables.
Turns out its more complicated than that. Since I'm dealing with claims, a person may add their partner as the second claimant. (usually the case in mortgages)
So where parties A and B are in a relationship:
person A may claim on their own
Person B may claim on their own
Person A may be the primary claimant and add person B
or conversely person B may be the primary claimant, adding person A.
The end goal is to automatically fill in a financial form (making use of a mail merge). So from what I know I think this means:
that grouping them as one contact is not going to work as I need them to be able to do a solo claim, and also I will not be able to easily change around who is the primary or secondary claimant, which will then carry over onto the mail merge.
So I think this means I need them as two separate contacts. However, I don't know how to create a link between them to easily add their partner to a claim.
Would this also mean I need a many to many relationship between clients and cases.
View 4 Replies
View Related
Jul 4, 2006
Hi all,I've been struggling with this for a few hours... I sure hope that the solution to my problem is complicated so I don't feel really dumb!I'm trying to design a database to eliminate the HOURS that my mom spends trying to format mailing labels in Word. She deals with about 50 clients at a time, and sometimes needs to print labels for just a selection of them, but not all. I'd like to design a form where she can select (using an option button or similar) the clients for whom she wants to print a label, and print them all at the same time (rather than printing one at a time, which I can handle on my own). Creating mailing labels will be the only function of the db so the info stored in it will be relatively simple.I have the Northwinds db installed, and I've found the sample Macro controlling the Where condition for the labels report. I understand how to make it work for a single selection from a combo box (ie: only print labels for customers from a specific country)I have my report set up so it shows a label for each client, pulling data directly from the main table. No troubles with formatting. **knocks on wood**My problems:1. I searched this site and found a link to the MS KB file that outlines how to print multiples of one label or skip used labels before starting to print (Q95806 - "How to Skip Used Mailing Labels and Print Duplicates"). I'd like to use this, but can't make it work.2. I can't figure out how to "link" the option button to the client name as it's listed in the form, and then tell the report to only print labels for the selected clients.I have very little coding experience but I can make some simple code work and am OK at customizing pre-written code to my own needs if it's well commented, so VB isn't entirely out as an option.I have a possible solution in mind that involves creating a query that makes a new table with only the selected clients, which is then used to make the labels report. I think I could make that work if I could just figure out how to select the clients in the first place.I apologize in advance if this has been taken care of elsewhere. I tried to find it, but had trouble coming up with the right search string!Thanks in advance,~ Mel ~p.s. I'm using Access 2000.
View 3 Replies
View Related
Sep 14, 2014
find only some clients that has only 4 types of products, but no other type of products.
Just to put it in a much easier way to understand. If I have to find only the client that did buy only 1 or more from the 4 products. Fridge, tv, dvd player, mobile phone. But I'm not interested in the clients that also did buy for example, laptop, pc, video cameras, etc. So if the client has only one of the 4 products, I want to list them all, did they buy another type of product too, then not.
How could I create a query that will show me only those clients?
View 4 Replies
View Related
Sep 10, 2013
I am using Access 2010. I've calculated the age of clients by creating a new field with Age: Year(Now())-Year([D O B]) but I cannot figure out how to use a parameter query to return the age of the clients between age 20 and 30, 30 and 40, 40 and 50 etc.
View 14 Replies
View Related
Aug 30, 2013
Ok, not sure if this is even possible or where to even start..
I've got a form that has all the info for a client, eg..
Client First Name
Client Last Name
Client Hours
Client WE/CS/EE
Client Day And Times
Client Phone
Client Comments
Begin Date & End Date (2 boxs) on there for date input..
and what i'm trying to do is limit that any date entered between begin date and end date, it'll check and make sure there is only ever 20 clients on any 1 day..
i thought Datediff would be the way to go, but then again how do you get it to search each day and make sure theres only 20 clients on there..
View 1 Replies
View Related
