I am currently creating a database for lift inspections. The output is a mail-merge report. What I am trying to do is get the multiple Lift Name (child) and Floors Served (child) combined into a string so that each Lift Group (parent) inspection has only one row. This then means that each lift group inspection will be shown as one record in mail merge.
Current
Lift Group ID - Date of Inspection - Lift Name - Floors Served
34 - 14/01/14 - Lift 1 - G-1
34 - 14/01/14 - Lift 2 - 1-3
35 - 14/01/14 - Lift 3 - G-8
34 - 14/01/13 - Lift 1 - G-1
34 - 10/10/13 - Lift 2 - 1-3
Desired:
Lift Group ID - Date of Inspection - Floors Served
34 - 14/01/14 - Lift 1 G-13; Lift 2 1-3
35 - 14/01/14 - Lift 3 G-8
34 - 10/10/13 - Lift 1 G-13; Lift 2 1-3
way to merge various fields from a table into just one using a query.The purpose will be for easier copying and pasting in to an email.So we have a table to stores information such as Site Contact, Address 1, Address 2, Address 3, Postcode etc.
I want to be able to run a query that will put the address in to just one box, either in the query or on a report then it's much easier to just highlight the full address and copy and paste it into an email. Rather than copying each field individually.
I have done this by linking the relationship between a person and a company (company-personID) with the table "alternates". In a subform for the information about the company itself, it shows the people associated with the company, as well as their alternates (assuming they're a director). It works just fine when I view a director's alternate, as John Tong here has his alternate as the person with relationship number 7. However, the whole thing is made useless when I view it from an alternate director's point of view, like Peter Chan, where he's displayed as the director, and the alternate is null. After poking around for a bit, it turns out that the child field is ALWAYS director.
What I want is for it to show the alternate or director based on who's being shown in the subform, i.e. Peter Chan is filled as alternate with the director being shown as "1". I've tried setting up expressions and putting up multiple master and child fields, but they just mess up Access.how I could display the names instead of the company-personID in the "alternates" subform,.
I have a field in the parent table which indicates how many child tables there should be at the most. I'm uncertain about how to enforce this. I"m using access as the database to a Vb.net app. Should I do it in access or Vb.net or both.
I have a form and a subform in MS Access 2003. I have made some changes to database structure, so I decided to change the subform also. When I changed the Link child and link master fields, the controls of the subdatasheet dissapear- they show only in design view. If I clear the contents of Link child and link master fields they appear again, but the records are not binded. Is there a setting on the parent form that also has to be changed, to make the new binding?
In a listbox I have a one or more modules selected and will construct the SQL in the AfterUpdate, presumably.
How to get a query to list only those procedures that belong to all the selected modules? To get into the result list a procedure must belong to all the modules that were selected. The code below gives me all the procedures that belong to at least one of the modules selected. I need ALL.
Code:
strIN = Left(strIN, Len(strIN) - 1) ' list of ModID's of selected in listbox strSQL = SELECT tblModuleProcs.ProcID FROM (tblModules INNER JOIN tblModuleProcs ON tblModules.ModID = tblModuleProcs.ModID) INNER JOIN tblProcedures ON tblModuleProcs.ProcID = tblProcedures.ProcID WHERE (((tblModules.ModID) IN(" & strIN & "))) " ORDER BY tblProcedures.ProcedureTag;
I'm trying to create a capacity report for my database. Originally, I only had the one table, which summed the number of Packs and number of Images, worked out percentage capacity used and then put it in a report, showing for each week of the year. However since then, I've added a child table to this, allowing me to create subrecords. The reason we did this was so we could easily group together multiple mailings under one single master record, so to speak.
Going back to the capacity, I've managed to work it so the Pack capacity is worked out from the mailing quantity in the subrecord. However, the number of Images is only entered onto the Parent record. Now my capacity query is summing the same number of images as there are subrecords, where in reality I only want it to sum the Images in the main record. I know I could add an Images field to the child table and work it out the same way, but I'd rather not do that (because that's not how our business works).
So essentially the question is, how can I sum the Images from the parent table without repeating the sum and massively overexagerating the sum?
I want to create an expression in a query, which is a combination of one or more fields in a table. e.g., Concatenate function in excel. Is there any such function in access. Please help me.
I am trying to determine the best method for how to handle this query using Access 2013. I have a clients table that contains the following:
clientID fName lName admissionDate dischargeDate 1 John Doe 05/06/2014 06/27/2014 2 Jane Doe 04/24/2014 05/15/2014 3 Steven Smith 05/15/2014 NULL/Empty 4 Chris Davis 06/12/2014 NULL/Empty
Then there is a WeeklyProgressNotes table that is there for the person that is responsible for auditing the clients charts. It does not contain the actual weeklyprogressnotes, it only contains a Yes/No field and a date field for the date the weeklyprogressnote was completed. Like below:
I am creating a form that the auditor can open to determine what weeks she needs to check for each client to see if they have their weeklyprogressnotes completed that week. The weeks run Mon - Sun and there will be no record in the WeeklyProgressNotes table if she has not yet checked and confirmed for that week. So the form would basically look like this:
fName lName week completed date clientID(hidden) John Doe 5/19/14-5/25/14 Checkbox Null 1 John Doe 5/26/14-6/1/14 Checkbox Null 1 John Doe 6/2/14-6/8/14 Checkbox Null 1 John Doe 6/9/14-6/15/14 Checkbox Null 1 John Doe 6/16/14-6/22/14 Checkbox Null 1 John Doe 6/23/14-6/29/14 Checkbox Null 1 Jane Doe 4/28/14-5/4/14 Checkbox Null 2 and so on.......
I have thought about creating an SQL statement to select all of the clients and then creating a function that determines their admission date within the specific week and their discharge date withing the specific week and then create a loop with another SQL statement with a BETWEEN clause for all the weeks and determine if there is an entry in the WeeklyProgressNotes table or not. If not then I would display out the above info. I'm not sure if there is an easier, less search intensive way of doing it. Maybe an SQL query that can cut done on some of the looping.
I'm in need of some help again. I have two tables, Table1 and Table2. I have one field in each table that I want to have the same data. I don't want blanks to be merged, but I want all the data to be the same
for example
Table1 --------- 1
3
5
Table2 --------- 1 2
4
6
I would like both tables to be merged to show
Table1 ------- 1 2 3 4 5 6
Table2 ------ 1 2 3 4 5 6
But I want to just update both tables and not create anything new.
Is there anyway that I can merge two fields together in a report? For example instead of having first name and surname as separate fields I want to put them together so that I don't have a big gap between them. The report is based on a query if that's any help!
I have an old database for generating service reports which has: Report No Date Customer Site
In separate fields which is fine
but also
wrk1 wrk2 wrk3 etc.
with a separate field for each printed line on the page I would like to merge all these into one memo field while retaining the previous data which goes back 15 years (it came from a MSWorks 2.0 DOS db which is the reason for the weird structure I think)
Is there any way to write a macro or code to selectively merge some but not all fields in a table?
I've already copied the db and modified it after clearing all the old data, it works fine but I'd like to have access to the old data in the new format
I am developing a basic Fault Logging system so I have a main form that allows ref to be input, date fault raised, priority, etc... I also have a sub form running that allows notes to be input. There is no set limit on the number of notes that can be added as this needs to be flexible. A relationship is set up so that the Fault reference will be tagged to each of the notes. What I need is a query (I'm guessing that is the best way to do it) that will gather all of the standard fields fromthe main form and also all of the note fields (it will need to return null values in notes fields). Preferably I would like all of the notes fields to concatenate into one field with a line break between each note. The concatenation process would have to be flexible and dynamic as there is no predetermined limit to the number of notes that will have to be joined together.
I'm relatively new to Access and having searched the forums and tried a few ideas can't find anything that I can adapt to my situation. Any ideas/guidance would be greatly appreciated!
I think the title describes best what I want to do. I need to send a letter to some lets say receivers, who fulfill certain criteria, and who appear in a data field in an access database. The letter is common for everyone with the difference of course of their address and name. I am sure this is an easy one for the most of you....
Thanx in advance, Kyriakos p.s. sorry for misspelling some words...
I am using mail merge to produce reports for lift inspections and it is all going well apart from the fields that are multi value do not quite transfer. For instance the "inspections completed" field has a list of the months Jan-Dec and therefore a visit may have been completed in Jan, Mar & Jul for example.In word the first two letter of the last month are transferred across followed by Chinese symbols.
We are a non-profit that does blind mailings for our membership drive. The company who we buy names and addresses from sends us a delimited file that has these fields as the headings
" ID, FULLNAME,COMPANY, ADR1, ADR2, CITY, STATE, ZIP, FIPS"
Once they send out the mailings, people then send in back a remit slip with a contribution that gets scanned through a program that creates a file that gives us these titles
"ALT ID, AMOUNT PAID, RUN DATE, TENDER, FUND, PURPOSE, SOLICITATION, MEMBERSHIP QUESTION, MEMBER TYPE, CONSTITUENT TYPE, SEGMENT". The "ALT ID" and "ID" are the same in both tables.
I need to find a way to merge the tables and combine the fields that have the same ID # , and then have it create a csv file that reads like this (see below) for only the files of the people that responded so that I can import it into our membership software.
I do have another problem with my "Membership Monster" After designing the base with only registential Addess information in the data entry Form. I had to include fields for a separate postal address. This only applies to maybe 10 of 400 entries is there a simple clever way I can copy this data over to the new postal detail fields from the Residential detail feilds ? I thought it may be possible to set thee residential fields as source data. But I am unsure if it is then possible to enter different data over the top if the postal address is different.
I also then need to be able to mail merge the postal details of current members so as print out address labels for the magazine.
I want to be able to merge the two records together if the field chr matches between the two files plus if there is an overlap between the start number and end number from each file. For instance the first record from each file would match because the range from 1000 to 2000 of file 1 has numbers consisting of 500 numbers (1500-2000) that are also present and overlap in file 2 (1500-3000). I possibly cannot use < or > since the ranges from each file will vary to different degrees. Perhaps there is a between function that might work...
I'm working on a Word mail merge feature that links to an Access Database and would like to know if you can insert specific fields into the mail merge to refine the terms? example below:
<<Customer Name, [field 2]>> - Or something to that effect.
^The mail merge would know where to obtain the customer name from anyway, but will it select information from the field that is being referenced?
