Queries :: Merge Two Child Fields As String

Jan 14, 2014

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

View Replies


ADVERTISEMENT

Queries :: Creating A Query That Will Merge Various Fields Into One Field

Jun 9, 2015

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.

View 3 Replies View Related

Forms :: Different Child Fields Within Same Subform

Dec 16, 2013

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,.

View 1 Replies View Related

Limiting Number Of Child Fields Per Parent

Sep 23, 2005

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.

View 6 Replies View Related

Forms :: Date Ranges - Using Child / Master Fields For Creating Filter

Oct 7, 2013

I have a form which I am using child/master fields to create a filter (see attached pic)

For the date ranges I need these to be

>= Start Date and <= Actual Finish Date

How can I set the criteria for these two fields, so that when someone picks a date it knows it's from or before the date entered.

View 14 Replies View Related

Subform In A Form Doesn Show Controls When Link Child And Master Fields Derfined

Jun 13, 2005

Hello,

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?

Thanks,
Aleksander

View 1 Replies View Related

Queries :: Child Records With Multiple Parents

Aug 3, 2014

I have modules and procedures. A module can consist of one or more procedures. A procedure can belong to one or more modules.

tblModules
--------
ModID
ModName

tblProcedures
----------
ProcID
ProcName

ModProcs
--------
ModProcID
ModID (FK)
ProcID (FK)

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;

View 3 Replies View Related

Queries :: Parent Child Query - Sum Images In Main Record

Oct 15, 2014

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?

View 6 Replies View Related

Merge Fields

Apr 1, 2008

How can I merge 4 fields from 1 table into new field which will look like:

field1,firld2,field3,field4 ....

View 3 Replies View Related

Merge One Or More Fields

Oct 3, 2006

Hi,

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.

Thanks & Regards

View 2 Replies View Related

Merge Fields?

Dec 16, 2007

Howdy, a newbie here, and new to access, but pretty strong computer skills..

need you're help please....

trying to "merge" fields - i think merge is the right name for it...

here's an example of the data...

100 ___
200 200
300 ___
___ 400
500 500
600 ___
___ 700

where the ___ = empty

I need it to look like this, a single field..

100
200
300
400
500
600
700

the values in the two fields are always the same if the exist in both fields..

thanks in advance!!!

Happy Holidays!

View 13 Replies View Related

How Can I Merge 2 Fields ?

Jan 4, 2008

I want to turn this set of fields returned from a query :-

'Stage'-'Engineer'-'BDE'
A-a-e
A-b
B-c-f
C-d

Into this result by merging the fields 'Engineer' and 'BDE' :-

'Stage'-'NewField'
A-a
A-e
A-b
B-c
B-f
C-d

Something like [Engineer]&[BDE] just joins up the text which isn't what I want.
A-ae
A-b
B-cf
C-d

I hope this makes sense.

View 2 Replies View Related

Queries :: Query Multiple Records Between (Weeks) Where No Record In Child Table?

Jul 1, 2014

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:

noteID completed dateCompleted clientID
1 yes 05/08/2014 1
2 yes 05/14/2014 1
3 yes 04/25/2014 2

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.

View 5 Replies View Related

Merge Fields Into One Field

Aug 14, 2005

I have a table and would like to merge all the fields into one text field of the table after the insert. I'm using adp (access/sql server).

When using standard sql the null values give a problem and i don't want to use the if clause as it will give too much coding.....

View 1 Replies View Related

Query To Merge Fields?

Feb 5, 2007

Hey guys,

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.

Sorry if this is unclear

View 2 Replies View Related

Merge 2 Fields In A Report

Apr 19, 2005

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!

View 3 Replies View Related

Merge Two Fields At One In Report

Mar 28, 2013

How to merge two fields at one in report

View 4 Replies View Related

Forms :: Display Some Fields From Master Table And Only Last Entry From Child Table

Jul 1, 2013

I have 2 tables, master & child. with a one to many relationship.

On one of my forms I want to display some of the fields from the master table and only the last entry from my child table.

How would i accomplish this?

View 5 Replies View Related

Can I Merge 2 Txt Fields To 1 Memo Field

Apr 6, 2006

Hi,

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

TIA

Kim

View 3 Replies View Related

Export To Excel And Merge Fields

Aug 5, 2005

Hello folks

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!

Thanks!

View 3 Replies View Related

Mail Merge Using Access Database Fields?

Feb 28, 2006

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...

View 1 Replies View Related

Reports :: Mail Merge - Multiple Value Fields

Jan 14, 2014

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.

View 1 Replies View Related

Modules & VBA :: Find A Way To Merge Tables And Combine Fields That Have Same ID

Feb 27, 2014

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.

"Alt ID","Title","First Name","Middle Name","Last Name","Suffix","Address1","City","State","ZIP","ct y_code","Amount Paid","Run Date","Tender","Fund","Purpose","Solicitation","Me mbership Question","Member Type","Constituent Type","Segment"

View 4 Replies View Related

Forms :: New Postal Address Fields And Mail Merge?

Jul 2, 2014

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.

View 5 Replies View Related

General :: Merge Two Fields From Two Databases That Have Matching Two Data Ranges

Mar 25, 2014

I have two databases (files). Each file has a format like the one below.

File 1:
chr start end
chr1 1000 2000
chr2 3000 4000

File 2:
chr start end
chr1 1500 3000
chr2 3750 4000

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...

View 7 Replies View Related

Insert Specific Fields Into Mail Merge To Refine Terms?

Nov 19, 2013

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?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved