multi record query

I am taking 4 fields from a linked AS 400 database, the 4 fields are
IDAREA = T
IDAISL = RA
IDBAY = IL1
IDLEVL = 9
I need the word "TRAIL19" to be my parameter for the information I need  for 
a query to fill in the form and report 
Is there any way to combine these 4 feilds?
0
Utf
1/12/2010 4:09:01 AM
access.queries 6343 articles. 1 followers. Follow

11 Replies
624 Views

Similar Articles

[PageSpeed] 19

On Mon, 11 Jan 2010 20:09:01 -0800, Norm
<Norm@discussions.microsoft.com> wrote:

select IDAREA & IDAISL & IDBAY & IDLEVL as myCombinedField
  from myTable
(of course you change myObjectNames to yours)

-Tom.
Microsoft Access MVP


>I am taking 4 fields from a linked AS 400 database, the 4 fields are
>IDAREA = T
>IDAISL = RA
>IDBAY = IL1
>IDLEVL = 9
>I need the word "TRAIL19" to be my parameter for the information I need  for 
>a query to fill in the form and report 
>Is there any way to combine these 4 feilds?
0
Tom
1/12/2010 4:48:18 AM
Thank you for your reply
my combined field...where do I find this?

"Tom van Stiphout" wrote:

> On Mon, 11 Jan 2010 20:09:01 -0800, Norm
> <Norm@discussions.microsoft.com> wrote:
> 
> select IDAREA & IDAISL & IDBAY & IDLEVL as myCombinedField
>   from myTable
> (of course you change myObjectNames to yours)
> 
> -Tom.
> Microsoft Access MVP
> 
> 
> >I am taking 4 fields from a linked AS 400 database, the 4 fields are
> >IDAREA = T
> >IDAISL = RA
> >IDBAY = IL1
> >IDLEVL = 9
> >I need the word "TRAIL19" to be my parameter for the information I need  for 
> >a query to fill in the form and report 
> >Is there any way to combine these 4 feilds?
> .
> 
0
Utf
1/13/2010 2:46:01 AM
>> my combined field...where do I find this?
It is whatever name you will use to show the results.

-- 
Build a little, test a little.


"Norm" wrote:

> Thank you for your reply
> my combined field...where do I find this?
> 
> "Tom van Stiphout" wrote:
> 
> > On Mon, 11 Jan 2010 20:09:01 -0800, Norm
> > <Norm@discussions.microsoft.com> wrote:
> > 
> > select IDAREA & IDAISL & IDBAY & IDLEVL as myCombinedField
> >   from myTable
> > (of course you change myObjectNames to yours)
> > 
> > -Tom.
> > Microsoft Access MVP
> > 
> > 
> > >I am taking 4 fields from a linked AS 400 database, the 4 fields are
> > >IDAREA = T
> > >IDAISL = RA
> > >IDBAY = IL1
> > >IDLEVL = 9
> > >I need the word "TRAIL19" to be my parameter for the information I need  for 
> > >a query to fill in the form and report 
> > >Is there any way to combine these 4 feilds?
> > .
> > 
0
Utf
1/13/2010 3:48:01 AM
Greetings from left field
For some reason I just dont get this one
How can I combine those 4 fields into one that I can use as a parameter query?

"KARL DEWEY" wrote:

> >> my combined field...where do I find this?
> It is whatever name you will use to show the results.
> 
> -- 
> Build a little, test a little.
> 
> 
> "Norm" wrote:
> 
> > Thank you for your reply
> > my combined field...where do I find this?
> > 
> > "Tom van Stiphout" wrote:
> > 
> > > On Mon, 11 Jan 2010 20:09:01 -0800, Norm
> > > <Norm@discussions.microsoft.com> wrote:
> > > 
> > > select IDAREA & IDAISL & IDBAY & IDLEVL as myCombinedField
> > >   from myTable
> > > (of course you change myObjectNames to yours)
> > > 
> > > -Tom.
> > > Microsoft Access MVP
> > > 
> > > 
> > > >I am taking 4 fields from a linked AS 400 database, the 4 fields are
> > > >IDAREA = T
> > > >IDAISL = RA
> > > >IDBAY = IL1
> > > >IDLEVL = 9
> > > >I need the word "TRAIL19" to be my parameter for the information I need  for 
> > > >a query to fill in the form and report 
> > > >Is there any way to combine these 4 feilds?
> > > .
> > > 
0
Utf
1/13/2010 4:36:01 AM
On Tue, 12 Jan 2010 18:46:01 -0800, Norm <Norm@discussions.microsoft.com>
wrote:

>Thank you for your reply
>my combined field...where do I find this?
>
>"Tom van Stiphout" wrote:
>
>> On Mon, 11 Jan 2010 20:09:01 -0800, Norm
>> <Norm@discussions.microsoft.com> wrote:
>> 
>> select IDAREA & IDAISL & IDBAY & IDLEVL as myCombinedField
>>   from myTable
>> (of course you change myObjectNames to yours)

In this query it would be given the name myCombinedField. You can put any
meaningful name you wish after the "AS" keyword.

-- 

             John W. Vinson [MVP]
0
John
1/13/2010 4:39:20 AM
On Tue, 12 Jan 2010 20:36:01 -0800, Norm <Norm@discussions.microsoft.com>
wrote:

>Greetings from left field
>For some reason I just dont get this one
>How can I combine those 4 fields into one that I can use as a parameter query?

Exactly how Tom told you. 

To do it in the grid instead of in SQL view...

Create a new Query in the query grid based on your AS400 table.
In a vacant Field cell in the grid type

SomeFieldName: IDAREA & IDAISL & IDBAY & IDLEVL

on the Criteria line under this field type

TRAIL19

or put a parameter and enter TRAIL19 into the parameter textbox or prompt.

-- 

             John W. Vinson [MVP]
0
John
1/13/2010 5:41:44 AM
Success!!!

One problem though when I enter TRAIL19 the query returns nothing
When I enter T* I get all TRAIL records the 19 is the key to what I need
When I look at the query there are blanks so it reads ...T RA IL1 9
is this the reason
Thank you very much for your patience on this

"John W. Vinson" wrote:

> On Tue, 12 Jan 2010 20:36:01 -0800, Norm <Norm@discussions.microsoft.com>
> wrote:
> 
> >Greetings from left field
> >For some reason I just dont get this one
> >How can I combine those 4 fields into one that I can use as a parameter query?
> 
> Exactly how Tom told you. 
> 
> To do it in the grid instead of in SQL view...
> 
> Create a new Query in the query grid based on your AS400 table.
> In a vacant Field cell in the grid type
> 
> SomeFieldName: IDAREA & IDAISL & IDBAY & IDLEVL
> 
> on the Criteria line under this field type
> 
> TRAIL19
> 
> or put a parameter and enter TRAIL19 into the parameter textbox or prompt.
> 
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
1/13/2010 7:04:01 AM
On Tue, 12 Jan 2010 23:04:01 -0800, Norm <Norm@discussions.microsoft.com>
wrote:

>One problem though when I enter TRAIL19 the query returns nothing
>When I enter T* I get all TRAIL records the 19 is the key to what I need
>When I look at the query there are blanks so it reads ...T RA IL1 9
>is this the reason

Please open the query in SQL view and copy and paste the entire SQL to a
message here.

Do the individual fields have leading or trailing blanks in the AS400 table?
-- 

             John W. Vinson [MVP]
0
John
1/13/2010 7:24:08 AM
I believe they do have trailing blanks in AS400 but in Access there are no 
leading blanks

"John W. Vinson" wrote:

> On Tue, 12 Jan 2010 23:04:01 -0800, Norm <Norm@discussions.microsoft.com>
> wrote:
> 
> >One problem though when I enter TRAIL19 the query returns nothing
> >When I enter T* I get all TRAIL records the 19 is the key to what I need
> >When I look at the query there are blanks so it reads ...T RA IL1 9
> >is this the reason
> 
> Please open the query in SQL view and copy and paste the entire SQL to a
> message here.
> 
> Do the individual fields have leading or trailing blanks in the AS400 table?
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
1/14/2010 2:36:01 AM
On Wed, 13 Jan 2010 18:36:01 -0800, Norm <Norm@discussions.microsoft.com>
wrote:

>I believe they do have trailing blanks in AS400 but in Access there are no 
>leading blanks
>

You can use the Trim() function to strip off the blanks:

SomeFieldName: Trim(IDAREA) & Trim(IDAISL) & Trim(IDBAY) & Trim(IDLEVL)

-- 

             John W. Vinson [MVP]
0
John
1/14/2010 4:57:54 AM

"John W. Vinson" wrote:

> On Wed, 13 Jan 2010 18:36:01 -0800, Norm <Norm@discussions.microsoft.com>
> wrote:
> 
> >I believe they do have trailing blanks in AS400 but in Access there are no 
> >leading blanks
> >
> 
> You can use the Trim() function to strip off the blanks:
> 
> SomeFieldName: Trim(IDAREA) & Trim(IDAISL) & Trim(IDBAY) & Trim(IDLEVL)
> 
> -- 
> 
>              John W. Vinson [MVP]
> .
> Success!!!
Thanks for all your help John
0
Utf
1/15/2010 12:10:01 AM
Reply:

Similar Artilces:

Make query headings just contain field names?
I've created Query2 based on Query1. When I select individual fields from Query1, the datasheet view displays the original query name in the column heading along with the field name: Query1.FirstName Is there a way to just display the field name: FirstName I'm using Access 2007. hi Sara, On 10.02.2010 21:46, SaraD wrote: > Is there a way to just display the field name: > FirstName Open your query in the designer and change the field selection to FirstName: Query1.FirstName mfG --> stefan <-- ...

Adding columns to Look Up Records window
In certain places in CRM (specifically highlight a Lead, click New Email, and press the looking glass lookup button to the right of the To field) when I do a Lookup for contacts or accounts, the window "Look Up Records" appears with a list of entities at the top, a search field, and two lists below. The left list is available records and the right is selected records. The Available Records list only displays a single column, the name of the account or contact. I want to display more columns, and scollbars are OK. My specific issue is that I have 4 "Rick Johnson" c...

Query Based Distribution Lists (URGENT)
Hi I've started experimenting with creating the above group types and I want to create a group using a filter that searches for members in a certain security group. My exchange and active directory environment is up to standard and working well.. The steps I follow for this are: 1. Customise Filter 2. Select only "Users with Exchange mailbox" 3. Type in the relevant exchange server under storage 4. On the field button, select User, Member of and under condition I select "Starts with" and then I type the group name or the first couple of distinguishing letters forming ...

record and print amounts in multiple boxes on 1099 forms
We have had several clients that have been very disappointed to learn that they can only do 1 amount box per 1099. For instance, they might need an amount for rents and one for non-employee compensation. I think it would need to be settable at time of transaction entry. One of our clients said they could do that in Quickbooks and was very surprised to learn that GP won't do it. -- BrianB ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in th...

Multiple detail/record per break to reduce pages
I'm creating a report of 18 fields into a report that I'd like to organize into stacked boxes/grids about 2 1/2" by 2 1/2" In the report when creating the detail section, I'm able to organize into the stacked box/grid that I'd like, unfortunately each record appears on a new line. With upwards of 25 records, the report ends up to be over 10 pages, but could be compressed into 2 pages if more records were able to be displayed on a line. I'd like to have 3 boxes/grids lined up horizontally. To visually represent it [ ] will represent a record org...

auto numbering in a query
Hi all, I need to build a query that have in a field auto numbering and in a certain formating for example: 0001 0002 ....... 0012 ....... is it posible? Is this to produce data that will be viewed in a report, by any chance? "thread" <yaniv.dg@gmail.com> wrote in message news:1175794888.467011.108490@w1g2000hsg.googlegroups.com... > Hi all, > I need to build a query that have in a field auto numbering and in a > certain formating > for example: > 0001 > 0002 > ...... > 0012 > ...... > is it posible? > On Apr 5, 2:42 pm, "BruceM&q...

Records not being saved
Hi In MSCRM3.0 we have been updating contact address details. In certain instances the records are being saved and at other times it has not been saved. No error message appears. Please can someone assist with this strange behaviour? Thanks Clinton ...

Post Exp Date in Master Rcd (row) from trans record
Hi, I'm using Access 2003 via Xp Office Pro. I'm working on a annual membership database. Each member has to renew each year if they want to stay a member. I want to keep a history of their membership. I have a membership table and will have a renewal table where I create a record / row each time a member renews. Key to both tables are automatic generated numbers. When I post the renewal transactions, I would like to change the expiration date that is on the master record. I realize that I would have the same data in both records, but for simplicity of the r...

Starting with no records for a filter
Hi, i have a subform that i filter based on some combo boxes at the top of the main form that the user can type into. i have a search method that is called using the following: call searchMethod(Nz(box1.value, ""), Nz(boz2.value,"") , ..... ) this search method then builds up an sql string to filter the records. this works well. when i initially load the form up, i have a minor problem. all of the records are shown, because all of the box values are "", and so there is effectively no filter. i actually want the opposite of this. i.e if the user doesnt se...

Update table with Multi-select list box
I have a database with a tab control that has several pages in it (my boss loves tabs for navigation). Each page has a list box based on a category of training events that members of my office attend and the box is based on a query that selects the training events for the tab page's respective category. I select an item from the list, click a button, and go to a form with a text box showing the name of the training event I previously selected. On that form, I want to select mutiple names of office members from a list box (already created and source is the Office Roster table) and then c...

Query is making a nuts
Hi Using Access 2007 I have a table and two of the fields (Status and OrigStatus) have, among other possible entries, the words "Member," "Customer" or "Request." I am trying to create a query that only displays records which do NOT have "Member," "Customer" or "Request" in either field. But it won't work! I have tried putting each word in its own Criteria column in each field using syntax such as: <>"Member" with no success. I have also tried <>"Member" OR "Customer" OR "...

queries which pull data in multiple "rows" in one table and compar
I have a project to complete where the end user wants me to write several queries which pull data in multiple "rows" in one table and compare them to data in another using access. He also needs a report written based on the findings of which data meets certain criteria within those queries. I think the report part will be pretty easy, but can someone give me some suggestions on what would be the best way to create the queries in access? Thanks Da Chosen One On Sun, 10 Jan 2010 19:22:01 -0800, Da Chosen One <Da Chosen One@discussions.microsoft.com> wrote: You...

CMap query
Hi, I have a text file and i read the complete file and build a FILELIST - Files & GROUPLIST - groups like odbc,,jet40, jet35 The Group list in the file consists of values like version | odbc | 430 version | Jet40 | 430 version | Jet35 | 430 version | oledb | 430 version | odbc | 440 version | Jet40 | 440 version | Jet35 | 440 version | oledb | 440 The File List consists of values like FILE | odbc | <COMPLETE path of odbc file like odbc32.dll> FILE | odbc | <COMPLETE path of odbc file like odbcji32.dll> FILE | oledb | <COMPLETE path of oledb dll1> FILE | ole...

Use query for Row Source?
I attempted to use a combo-box to look-up records on a form. The RowSource query that is generated by the wizard included 2 data fields. I wanted to show only a distinct list of the second, non-key data element (col1). When I changed the RowSource query to SELECT distinct Col1, the combo-box displays nothing. I then created a stored query with the SELECT distinct Col1. They query runs correctly, but when I put in in the RowSource, it displays nothing. Any clue what I could be doing wrong? On Fri, 26 Oct 2007 12:17:03 -0700, JHC wrote: > I attempted to use a combo-box to look-u...

code for moving to new record in datasheet view
Access 2003 What's the vba code for moving to a new record when in datsheet view. Here's what I've tried but is errors saying the subform is not opened and yet this code is on an event in the subform: If cmbInvLU <> 289 Then DoCmd.GoToRecord acForm, "Forms!frmWOTRH![frmWOTRL subform].Form", acNewRec Thanks for your help. SAC, Since your already in the subform... try... DoCmd.GoToRecord , , acNewRec -- hth Al Campagna Microsoft Access MVP 2006-2009 http://home.comcast.net/~cccsolutions/index.html &quo...

text conversion to number on select query
Hi, In a Select Query I'm joining 2 tables by Item ID (unique value, similar to Social Security Number) but 1 table created by IT has Item ID as a "number" value and the other table has it as a "text" value. How can I in a Select Query, create a formula that can either have the text as a number value and vice versa so i can link the 2 without getting "type mismatch in expression." I think I can use Cdbl Value or something like that in the formula but not sure. Thanks! "inspirz" wrote:subed going to jail > Hi, > ...

MS paramater Query on ODBC Table
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C38C4D.5F11AC30 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is it possible to and if so how can I run a parameter driven MS query on = an ODBC database table? I want to be able to enter some info into a cell on a spreadsheet and = have a query run to pull back other info relating to my entry. --=20 Regards Dean=20 dkso@ntlworld.com=20 http://homepage.ntlworld.com/dkso ------=_NextPart_000_0008_01C38C4D.5F11AC30 Content-Type: text/html; charset="iso-885...

MS Query not installed for New Database Query
I have Excel 2000 SP3 & Windows XP Home When I go to Data | Get External Data | Run Saved Query I can imort data from an Access database into an Excel worksheet. Wheh I go to Data | Get External Data | New Database Query after a delay a message appears to tell me that MS Query is not installed. MSQRY32.EXE is in the Office folder & I have reinstalled it by renaming it to .old & running the Add/Remove facility in the Office installer to no avail. I have searched the MSKB & found an article at: http://support.microsoft.com/default.aspx?scid=kb;en-us;179686 that refers to...

Check boxes to run queries
Hi, I want to use check boxes to choose criteria. I was to return a table for the following data: Invoice number, Customer number country, part number, qty, value etc. There are 5 countries, I want to have a check box for each so we can choose which ones to include, and then click GO. How do I do this? I am new to this. Thanks Natalie On Fri, 28 May 2010 06:25:01 -0700, Natalie <Natalie@discussions.microsoft.com> wrote: I'm not sure I understand. Typically an invoice is for a customer, and that customer has a billing address in some country. Why would you...

selecting a query from a combo box
HELP! Need to design a DB for my boss and I am lost! I have a database which lists students who have went on exchange over the last 17 years to over 20 countries and numerous institutions.. I have set up 3 queries/reports using parameters so the user can enter: 1) the year 2) the country or 3) the insitution. Now the problem is the insitution query as the name of the institution can get spelt various ways so I would prefer the user to select the institution from the drop down box which they use to enter the data into the table under the field "institution". My f...

Database query
I am running a stored procedure. When I use the database query option in import external data and run the stored proc - I get the expected number of rows (18990). But when I run the stored proc in my macro. Below is the code I am using ' Assign the Connection object. .ActiveConnection = cnRap ' Extract the required records. .Open sqlStmt ' Copy the records into on Sheet. SheetName.Range(CellNum).CopyFromRecordset rsRap It only returns 900 rows. Any ideas? ...

Use main form and subform to create single record
Hello, I hope someone out there can help, or can tell me if what I want is impossible. I have a form and 2 subforms that use the same table as the record source. What I want to do is have the data entered into these forms combined into a single record on the source table. The way it is currently linked (with a standard master/child field indicated) is resulting in multiple records. This is for an audit evaluation, used for call center employees. The reason for the subforms is that depending on the call type (sale or non-sale) there are different audit criteria. I have my main form set so it ...

query sort on date
I've got a table where applicant data is entered (via a form) everytime an application is submitted. Each time a person submits an application it is enteed as a new submission, thus I can have duplicate records on a person, except for the submission date and maybe the address if it changed but it is still a new record. My questions are how can I query the table data to only show the most recent applications without having duplicate records on a person? Another field in the table is the SSN. The other question pertaining to the same query is say that an application was submit...

Query Problem in Test.
I keep getting errors on the WHERE part of my query. I'm sure it's something simple. I don't know if you will need the whole code to see what the problem is. It is lengthy, so I will start with just where the problem is. Set rs = DBEngine(0)(0).OpenRecordset("SELECT T2.Distance, T1.* FROM (Church AS T1 INNER JOIN qryChurchZip1 ON T1.ChurchID = qryChurchZip1.ChurchID) INNER JOIN DistanceQuery AS T2 ON qryChurchZip1.Zip5 = T2.ZIPCode WHERE " & strWhere) ' See if found none If rs.RecordCount = 0 Then MsgBox "No Churches meet your crite...

Monthly outstanding query
HI, I have a Receivable table with CustID, DueDate and AmountDue. Another Receipts Table with CustID, RctDate, AmountPaid. How can I make a query to display What was collectable each month and What was actually collected? No problem with getting monthly collections part. Where i am getting stuck is that I need to add the outstanding of the previous month for each month. Any help please. Thanks Ramesh Hi Ramesh, try this: PrevDue: dSum("AmountPaid", "[Receivable Table]", "CustID=" & [tablename].CustID & " AND DueDate <=#" &...