Hello, I have been working on my database for sometime - searching this forum for answers, thanks to all the experienced people for their sharing of knowledge.
My question is this: My database will be over several years of data, on my splash screen (switchboard) I would like to have a combo box of with choices for years. When a user chooses a year, the entire database is filtered. Say if the choice was for 2005, then anything with a binderdate of 01/01/05 to 12/31/05 will be filtered.
This will need to be done across several tables, queries, forms, and reports.
Once the choice is made for that year - any form the user is on will be filtered for that specific year.
I am still fairly new at access programming, so any code snips or working examples would be greatly appreciated.
I have a table that collects training information for our company. Some training is limited to one or two employees and that is easily entered by using a form linked to the table. However, some training is mandatory, company-wide training that all employees must attend. How can I add a record for each employee reflecting this training most efficiently? Is there a way to do a mass update?
I'm trying to update this movie database, and I need something to save me time, not to mention my finger. One field in this db tracks if a movie is colour or black/white. In the old/existing db, that was done with text - "color" and "black & white". I want to replace that with a yes/no field [Color]. But the db has a few thousand entries. What I would like to do is either change all of the [Color] fields in the new db to yes, and then manually uncheck the ones that are b/w. But I guess it should also be possible to do the whole operation automatically. Any ideas as to how to accomplish either of these would be appreciated.
I recently posted a question in the REPORTS section asking if it was possible to generate a report that contains only the email addresses of my contacts to generate and send a mass email. There were no responses to that...so that makes me ask - Is there anyway in Access to generate mass emails using only the email address field in a table or query?
I am aware of other programs you can buy to do that - but was hoping to stay within Access.
I am sending out multiple emails to my customers in my customer table manually. As it is time consuming to send to about 100-200 customers daily, I am wondering if it is possible to automate the process in access in such a way that I can load all files from a particular folder and the access application will be able to sort the files in the folder for each company based on their name in the field(e.g.CompanyABC_DailyReport,CompanyDEF_DailyR eport) before sending out.
I've been asked to come up with a way (one-off) of renaming over 100 hundred tables. The table are all called "STUD_ADMIN_blah". We need to remove the "STUD_ADMIN_" part of the table name.
Is there a nice easy way of doing this without having to physically rename each table individually?
I have several thousand hyperlinks that need to be changed due to our network server being changed. I have tried to change the hyperlink field to a text field and do a find and replace. (See mandaman post 3/13/06) The problem that I have is that all the hyperlinked fields have a different text comment in the field to identify the hyperlink to the user. When I change the hyperlink field to text then it thinks that the text in the field is the hyperlink and in fact it has nothing to do with the actual hyperlink. Any suggestions?
Hi, can any help : ) i am trying to mass update one field in a table. Basically i have created a new check box (Yes/No Value) in my customers table. Basically i will tick this box if a customers placed any order with our company.
At present all our existing customers have placed orders with us. and i want to add value Yes to this field.
What method can i use to make this Mass update all customers records.
I've read through many of the threads relating to sending Email from Access but can't seem to find quite what I'm looking for. I work as a Tutor Coordinator at a local college and I've set up Access to handle a lot of the day-to-day chores that our administrative assistant has to deal with.
One feature that would be absolutely wonderful would be to create and send Emails to students at the click of a button. I have several types of Email I'd like to be able to set up, but the one most needed (and probably most complex) is this:
Students come in to the office and request a tutor. If we have a tutor available, we assign them to an available tutor. If there is no tutor availabe, then every Friday we send out an Email to each individual student saying something like this:
"Dear John Doe - We currently do not have a tutor for MATH 101 at this time, but when one becomes available we will let you know... blah, blah, blah..."
I'd like to set it up so that the body of the message comes from a template - such as a text file (that is easily edited by a user) and populated with fields from a query.
Probably do something like this:
Query the Student_First_Name_field, Student_Last_Name_field, Department_Name_field, Course_Number_field, and Student_Email_field. Send an Email to each Email address pulled from the Student_Email_field and tell them the following - "Dear <first name, last name> we currently do not have a tutor for <department name, course number> at this time... blah, blah, blah..."
Currently the way the administrative assistant handles this is to send each student an Email individually - manually creating each Email with the specific data needed. As you can guess, this is quite time-consuming.
I'm rather new to Access but have been a RDMS programmer (in PICK) for several years. I know what it is I want to do, but don't know how to do it in Access.
Any help?
Would the best way be to use text files for templates?
Can text files be set up with field codes (Access recognizes "<LAST NAME>" to be Student_Master_Table.Last_Name_field)?
I'm guessing this will likely have to be a Visual Basic loop that works with data from a query.
I have a database for clients and have set up a form and code to run a query for different types of clients and to send emails to the group.
Everything works fine until I try to send and then I get a Microsoft Outlook pop-up which states:
"A program is trying to automatically send e-mail on your behalf. Do you want to allow this? If this is unexpected, it may be a virus and you should choose "No."
This message stays for 5 seconds and then I can click on "YES" and it will send the email, and start all over for the next recipient.
I am assuming this is an Outlook Spam Blocker, but is there any way to stop it?
I have 300++ Excel files that contain data in the format of column1 = textual identifier, column2 = numerical value. Each file can have up to 1300 rows. The text identifier will be 95% the same in all files with slight variations.
I want to create a database to store the numerical value of each identifier from every excel spreadsheet and average the value.
This should be ~1300 instances, each with 300++ value assigned to it. Then run a report to pull the average value of every instance/identifier.
My (many) questions as follows..
-Is Access a good way to go about this? -Is there an efficient way to bulk import from excel ( I just saw a script to do this below )? -How to set up tables/queries/relationships?
So I am building a database to track PTO. I already have a form set up with an append query but what I want to do it use a list of check boxes to create mass appends incase a large group of people leave, Holidays, etc. Instead of having to do them individually.
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code: 'Purpose: This module illustrates how to create a search form, _ where the user can enter as many or few criteria as they wish, _ and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
I have a continuous form based on table "INCOMES" that shows all the payments received, which mediums can be (field "PMNT_MEDIUM"):
- check - transfer - taxes - cash
Table "INCOMES" is filled using another form, but in this particular form I just want to show "check", "transfer" and "cash" (not "taxes") so that I can track all the cash incomes.
Note: taxes are loaded because they appear in my invoices and I need them there to reach the invoice total amount.So my form has a search bar which allows me to search by PMNT_MEDIUM listing all "checks", all "cash" or all "transfer". I can also search by payment number (meaning: check number). To that end I have a "search" button that applies the filter. And I have another button that "cleans" the filtering by "putting a "" in the search-bar and then calling the "on click" of the search button".
What I need is, no matter if I click over the "search" or "clean" button, it NEVER shows me the "taxes".Search button, on click code:
If IsNumeric(Me.busq_chq_med) Then Me.Filter = "[PMNT_MEDIUM_NUMB] =" & Me.SEARCH_BAR Else Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*'" Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*' or [INVOICE] like'" & Me.SEARCH_BAR & "*'" End If Me.FilterOn = True
I have a form that I use a filter on to make my where statement for the query I have. The form has 3 drop downs: County, City, and State. Everything works great except when you only choose a state you should get the totals for just the state and instead you get a report by city. Here is my sql:
SELECT Sum(IIf([Homeless]="yes",1,0)) AS HomelessCnt, Sum(IIf([NFresident]="yes",1,0)) AS NFresidentCnt, Sum(IIf([Self]="yes",1,0)) AS SelfCnt, Sum(IIf([StaffBoard]="yes",1,0)) AS StaffBoardCnt, Sum(IIf([FamilyFriend]="yes",1,0)) AS FamilyFriendCnt, Sum(IIf([CommLarge]="yes",1,0)) AS CommLargeCnt, Sum(IIf([ServeProvide]="yes",1,0)) AS ServeProvideCnt, Sum(IIf([Physical]="yes",1,0)) AS PhysicalCnt, Sum(IIf([MentalEmo]="yes",1,0)) AS MentalEmoCnt, Sum(IIf([Cognitive]="yes",1,0)) AS CognitiveCnt, Sum(IIf([Vision]="yes",1,0)) AS VisionCnt, Sum(IIf([Hearing]="yes",1,0)) AS HearingCnt, Sum(IIf([Multiple]="yes",1,0)) AS MultipleCnt, Sum(IIf([AdvocacyServe]="yes",1,0)) AS AdvocacyServeCnt, Sum(IIf([AssistDevServe]="yes",1,0)) AS AssistDevServeCnt, Sum(IIf([ChildServe]="yes",1,0)) AS ChildServeCnt, Sum(IIf([CommServ]="yes",1,0)) AS CommServCnt, Sum(IIf([FamilyServe]="yes",1,0)) AS FamilyServeCnt, Sum(IIf([HousingServe]="yes",1,0)) AS HousingServeCnt, Sum(IIf([ILSkillServe]="yes",1,0)) AS ILSkillServeCnt, Sum(IIf([InformationServe]="yes",1,0)) AS InformationServeCnt, Sum(IIf([MentalRest]="yes",1,0)) AS MentalRestCnt, Sum(IIf([MobilityServe]="yes",1,0)) AS MobilityServeCnt, Sum(IIf([PeerServe]="yes",1,0)) AS PeerServeCnt, Sum(IIf([PersonalServe]="yes",1,0)) AS PersonalServeCnt, Sum(IIf([PhysicalRest]="yes",1,0)) AS PhysicalRestCnt, Sum(IIf([PreventiveServe]="yes",1,0)) AS PreventiveServeCnt, Sum(IIf([Prostheses]="yes",1,0)) AS ProsthesesCnt, Sum(IIf([RecServe]="yes",1,0)) AS RecServeCnt, Sum(IIf([RehabTech]="yes",1,0)) AS RehabTechCnt, Sum(IIf([CounselServe]="yes",1,0)) AS CounselServeCnt, Sum(IIf([Therapeutic]="yes",1,0)) AS TherapeuticCnt, Sum(IIf([TransportServe]="yes",1,0)) AS TransportServeCnt, Sum(IIf([YouthServe]="yes",1,0)) AS YouthServeCnt, Sum(IIf([VocationServe]="yes",1,0)) AS VocationServeCnt, Sum(IIf([OtherServe]="yes",1,0)) AS OtherServeCnt, Sum(IIf([Newsletter]="yes",1,0)) AS NewsletterCnt, Sum(IIf([EventFlyer]="yes",1,0)) AS EventFlyerCnt, Sum(IIf([VFIBrochure]="yes",1,0)) AS VFIBrochureCnt, Sum(IIf([WaiverInfo]="yes",1,0)) AS WaiverInfoCnt, Sum(IIf([Survey]="yes",1,0)) AS SurveyCnt, Sum(IIf([Other]="yes",1,0)) AS OtherCnt, Sum(IIf([Advocacy]="yes",1,0)) AS AdvocacyCnt, Sum(IIf([SkillTraining]="yes",1,0)) AS SkillTrainingCnt, Sum(IIf([PeerSupport]="yes",1,0)) AS PeerSupportCnt, Sum(IIf([Deinstitutionalization]="yes",1,0)) AS DeinstitutionalizationCnt, Sum(IIf([ReferralPAS]="yes",1,0)) AS ReferralPASCnt, Sum(IIf([ReferralVFI]="yes",1,0)) AS ReferralVFICnt, Avg(PersonalInfo.MinSpent) AS MinSpentAvg, Avg(PersonalInfo.CallerAge) AS CallerAgeAvg, PersonalInfo.CallerCounty, PersonalInfo.CallerCity, PersonalInfo.CallerState FROM PersonalInfo GROUP BY PersonalInfo.CallerCounty, PersonalInfo.CallerCity, PersonalInfo.CallerState;
I have tried to take out the group by and PersonalInfo.CallerCounty, ect. Any ideas would be greatly appreciated. Thanks
I have two seperate tables that display a part number (Tables A and B). Both tables have part numbers while Table A has a segment of the part number and Table B has the full part number (ex. (A) 2501 ; (B) PUTCO-SYSTEM-2501 ). I need table A to have the full descriptions just like table B has. Is there anyway I can link the tables and run a query to get this? Thanks.
I have asked previous questions and searched extensively on this topic and I am still without a solution.
Im trying to filter a range in a queries field. Here is the way my query is organized.
All this data deals with forecasting for my company...
Version - The current version of the forecast (represents a 2 year period techniqually) Plant - One of our cement plants CK Type - Clinker Type, main ingredant for cement
All of these are for place keeping, it is the way they need to be inputed into another tool.
Layout: Version / Plant / CK Type / *Version (current) / *Version (next) / *Verison(after next) / etc.
* - Actual numbers eg 200909 for september, 2009
The other verison fields are for the actual data entry through a keystroke. They continue out to 2014 for the time being.
What I need to do is dynamically filter a range starting with the current version and ending with the version 2 years out. So I would be filtering the field.
Is this possible? Everything I have tried leads me to believe that it is not. Perhaps its in my design?
I know a formula that will get me the proper output for the version:
Version = 200711,200712,200801, etc.
iif(Version(right,2)=12,Version+89,Version+1) , but I cannot get this to populate the fields.
I have a query which lists data for 86 different units. I need to get the query to return the last entry for each position on each unit, based on turn date. There should be 16 positions for each unit. Then I need to be able to create a report based on the query for just one unit or for all 86 units. I have attached a zip file of how far I have got. I've been stuck on this for ages, so I'm hoping someone can help!
I have a form that is showing data from 1 table. That table has 12 different fields on it and I want to be able to filter based on selections I make in a combo box in the header of the form. The filter string must be dynamic enough to allow filtering based on 1 criteria selected, or multiple criteria selected. For example:
If I have values in filter fields 3, 5, and 9 I'd want the filter string to be created as follows:
"...WHERE field3 = field3filter.value AND field5 = field5filter.value AND field9 = field9filter.value"
If I have values in only field 7, I'd want th efilter string to be created as follows:
"...WHERE field7 = field7filter.value"
And so on and so on.
I have created some filters before but all of the different VBA syntaxes I'm using seem to come up short.
How can I create a "Filter Button" on a form and filter my records? I create a textbox on a form and a filter button on the right. Then I click the filter futton, the filter function will search/match the content in the box through the datasheet. And then the results of the filtering will be pop up on the split form datasheet.
i have a table with 20,000 records. one column includes names. another column includes multiple dates the person visited a doctor, and another column includes dates the person visited a hospital. i am only interested in the last date the person visited a doctor and the last date the person visited a hospital (if they had any visits to the doctor or hospital). I am also interested in members who have had no visits to the doctor or hospital. any suggestions on how i can filter the data to only return these records? thanks
Name Date Visited DoctorDate Visited Hospital Nigel01/01/1980012/31/1995 05/20/199906/06/2005 03//02/200405/06/2004
For example, I only want to return
Nigel, 03/02/2004 (Date Visited Doctor), and 06/06/2005 (Date Visited Hospital). thank you. i really appreciate any help you can offer.
Hi have a query which looks up a bunch of values across 2 tables and i have a parameter which prompts user to input a date and i then want all records matching that date to be returned but it just returns all records: The sql for the query is a follows:
SELECT Tbl_Customers.Customer, Tbl_Customers.[Telephone Number], Tbl_Customers.DateCalled, Tbl_Products.Sales_ISP, Tbl_Products.Sales_IBP, Tbl_Products.Sales_ITSM, Tbl_Products.Sales_Datasure, Tbl_Products.Sales_BusEmailPlus, Tbl_Products.Sales_IntTrade, Tbl_Products.Sales_IntTradePay, Tbl_Products.Sales_Mobile, Tbl_Products.Sales_Fusion, Tbl_Products.Sales_BBV, Tbl_Products.Sales_BBVPlus, Tbl_Products.Sales_Other, Tbl_Products.Sales_OtherCheck FROM Tbl_Customers LEFT JOIN Tbl_Products ON Tbl_Customers.ID = Tbl_Products.ID_Custref WHERE (((Tbl_Customers.DateCalled)=[Please select date]) AND ((Tbl_Products.Sales_ISP)=Yes)) OR (((Tbl_Products.Sales_IBP)=Yes)) OR (((Tbl_Products.Sales_ITSM)=Yes)) OR (((Tbl_Products.Sales_Datasure)=Yes)) OR (((Tbl_Products.Sales_BusEmailPlus)=Yes)) OR (((Tbl_Products.Sales_IntTrade)=Yes)) OR (((Tbl_Products.Sales_IntTradePay)=Yes)) OR (((Tbl_Products.Sales_Mobile)=Yes)) OR (((Tbl_Products.Sales_Fusion)=Yes)) OR (((Tbl_Products.Sales_BBV)=Yes)) OR (((Tbl_Products.Sales_BBVPlus)=Yes)) OR (((Tbl_Products.Sales_OtherCheck)=Yes));
As you can see it filters by looking for any check box with a value of yes and then hopefully by date -
This sounds simple, but I'm not sure what the coding is in SQL. Perhaps someone can help..
I have a query which has criteria set by a form.
The First sorts the data in the query by SalesPerson from the selection made in a Forms combo box.
The second sets the date the query filter starts at and finishes.
So far so good.
However, I have an entry in the combo box for "ALL" sales people so that the user can see all the results for all sales people.
SELECT tblSalesinfo.Sales_reference_autoID, tblSalesinfo.CustomerID, tblSalesinfo.Completed, tblSalesinfo.[Sales CO], tblSalesinfo.Date, tblSalesinfo.Cust_Ref, tblSalesinfo.Customer_Name, tblSalesinfo.Description, tblSalesinfo.CTN, tblSalesinfo.Sold_£_Phone, tblSalesinfo.Sold_£_Accessories, tblSalesinfo.Sold_£_Other, tblSalesinfo.Labour_£_Charge, tblSalesinfo.Total_Sales, tblSalesinfo.Invoice_Number, tblSalesinfo.Delivery_Note, tblSalesinfo.Tariff, tblSalesinfo.Commission_due_amount, tblSalesinfo.Other_value, tblSalesinfo.Total_deal_Value, tblSalesinfo.Cost_A, tblSalesinfo.Cost_B, tblSalesinfo.Cost_C, tblSalesinfo.Cost_D, tblSalesinfo.Total_Cost, tblSalesinfo.Total_Deal_Balance, tblSalesinfo.[Margin%], tblSalesinfo.Sales_Commission_Due, tblSalesinfo.[Recovered Stock] FROM tblSalespersons INNER JOIN tblSalesinfo ON tblSalespersons.SalesID = tblSalesinfo.[Sales CO] WHERE (((tblSalesinfo.[Sales CO])=[Forms]![frmDisplayResults]![SalesCO]) AND ((tblSalesinfo.Date) Between [Forms]![frmDisplayResults]![StartDate] And [Forms]![frmDisplayResults]![EndDate]));
Can someone help with the code to say that if [SalesCO] = "ALL" it does not filter the results by Salesperson and shows the query for the date range only.
