Searching For Certain Field Name From Multiple Tables

Dec 22, 2011

I have around twenty shipment tables for different days and one main store check table.In the shipment tables i have bill numbers that correspond with shipping id's and in the store table i only have bill numbers.

I would like to check if the bill number in the store table appears in all 20 tables.after doing that i want the corresponding shipping ID to appear in the store table. Is this possible?

View Replies


ADVERTISEMENT

Query Searching Multiple Tables' Field Names (not Values)

Jan 22, 2007

Hi. Please do not lecture me on database normalization, as this truly is not in my control.

I have 2 dozen tables, each with 13 fields. All of the field names exist in at least 12 of the tables. And all tables share a ssn field where values are common.(Confused yet? Sorry, if you are...)

If I design a query showing all 2 dozen tables and their fields...
Can I set up a query criteria where I enter the requested ssn and then the required field name and have the result show the values of all the fields with that name, among the ones of the 2 dozen tables where that field name exists? (for that specified ssn)

If I am only as clear as mud, please let me know, and I will try again.
Or, by asking if I can search for field values querying by field name, am I any clearer?

Russ

View 14 Replies View Related

Please Help:Searching Multiple Tables!

Jan 3, 2005

Is there a way to search multiple tables for a record. I have 4 tables, each containing a different product. In each table are lists of serial numbers (primary key) and other information (ship_to, sales_order_number,etc.) for the product. I want to be able to enter a serial number and display the information related to that serial number. Not all fields are the same for each table. Is this possible? I would greatly appreciate any help!!

View 1 Replies View Related

Searching Multiple Tables...

Jan 25, 2008

Ok folks I have what is proving to be a doozy for me.

I have a multiple table database related fields in each one are:
Procedure
Description
Payable Amount

I am trying to find a way to search particular tables for particular Procedures. My output must include fields: Procedure, Description, Payable Amount. I have tried a form but I would have to included way too many text boxes to accompish what I am looking for. If possible I would like to have three text boxes if a form is possible. I would start the search and my output would end up in the text boxes below:
Procedure, Description, Payable Amount

With of course some sort of Identifier of the table that was selected to search in.


Can anyone help me?


Sean

View 5 Replies View Related

Searching Across Multiple Tables With Same Columns....

Dec 2, 2005

Ok,

I have three tables that are named different, but have the same column names, but different data in them. The data in each table is linked by a master table that has a primary key.


What I need is a way to make a form with a box where I can search on a value that could be in any of the three tables and then the associted information from the site will display in the above boxes.


(Site Master Table contains all site information and the primary key)
(The three tables are sales order information. Stage Only table, Install table, and srop ship table)

Any ideas?

View 1 Replies View Related

Tables :: Linking Multiple Field Values To A Field Selected From Combo Box

Feb 16, 2014

I'm pretty good with setting up a very simple database such as inventory, profiles, etc.. However I'm creating a database to keep track of a football (soccer) team's players and match statistics.What I have so farsample attached)

Tables:
* Players - PlayerID, Fname, Lname, position, goals, assists, etc (all details regarding a player)
* Position - Positons (Table containing positions eg: defender. Data is selected in player's form as a combo-box)
* Competition - Competition types (Cup, League, Friendly. Data is selected in Match's form as a combo-box
* Venue - similar to Competition table
* Opponent - Similar to above two tables
* Match - MatchID, Competition, Venue, etc (form corresponding to table attached)

Forms:
* Player form
* Match form

Now as shown in the sample, I choose players using the combo-box. Then whatever stats they had during the match are entered on the fields provided. How to link the player (selected using combo box) to the stat fields (goals, assist, YC, etc).

View 1 Replies View Related

Searching For Multiple Data

Jul 10, 2007

Hi,
I currently have an employee database - all info is currently in one table.
I am wanting to create a search box in my form which allows me to search for multiple bits of information. ie. I would like to be able to type in either a first name, surname, full name, or employee id number (for example), press enter, and for it to display the results (either by going to the first that it finds, and me then clicking find next, or by displaying all found in an index so that you double click the one that you want - i am not too fussed... the main issue is getting the search box to look in multiple fields).

I currently have the following code:

Private Sub cmdFind_Click()

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Surname")
DoCmd.FindRecord Me!txtIDFind
'If FindRecord = True Then
'Exit Sub
'End If
DoCmd.GoToControl ("EIN")
DoCmd.FindRecord Me!txtIDFind
'If FindRecord = True Then
'Exit Sub
'End If
DoCmd.GoToControl ("forename")
DoCmd.FindRecord Me!txtIDFind

End Sub

(ignore the notes - they were trial and error for something else).

Currently, when i press find, this code will find the first of anything - ie i time in Frank and the first Frank will appear (unless someones surname begins with Frank). What i can't seem to do is get it to progress to the next record if i press a find next button.

I have also tried the following code, but it only works if i put in an EIN number:

Private Sub Findall_Click()

DoCmd.ShowAllRecords
DoCmd.GoToControl ("ein")
DoCmd.FindRecord Me!txtIDFind, acStart, acAll, True


Any thoughts? I think i'm probably looking at this in completely the wrong way.

Oh, also i am quite new to VB, so please forgive any silly questions that i am likely to ask!

Kind regards,

Fergus.

View 3 Replies View Related

Searching Using Multiple Criteria

Feb 23, 2005

I have a button with this code attached

Dim SearchStr2 As String
SearchStr = "[PROTECHNIC_NUMBER] = " & "'" & Forms![FRMPATIENT]![Frm_ICP_Select].Form![Protechnic_Number] & "'" _
And "[ICP_Code]" = Forms![FRMPATIENT]![Frm_ICP_Select].Form![ICP_Code]
DoCmd.OpenForm "FRMASSESMENTHEAD", acNormal
Forms!FRMASSESMENTHEAD.Filter = SearchStr
Forms!FRMASSESMENTHEAD.FilterOn = True

If the button is clicked it should open the FRMASSESMENTHEAD form where the Protechnic Number (which is a text field) and the ICP Code(which is numeric) is the same as the ones on the current subform.

It works great if just using one of the criteria but when i put the and command in and then add the second criteria i get an erroer message saying

Run-time error '13':

Type mismatch

hope this makes sense and can anyone help :confused:

View 1 Replies View Related

Searching On Multiple Criteria Problem

Sep 19, 2007

i have created a form for my database where i intend to give the user the option of searhing my more than one criteria but not all.
i have used a system of combo boxes for the user to enter thier choises leaving the combobox blank for any criteria they dont know.
the user then clicks th button to open the query

in the query i have set the criteria for the fields to equall the related combo bo in the form.

however if the comboboy is left blank (0 or null) the query looks for a record where that field is also null. as a result it wont return any records.

an example

on the table tblFault there is the record

FaultID; 7
Computer id; 2
Name; c
Room-ID; it1
Date Of Fault; 05/06/07

on the form the user enters the following values into the combobox

FaultID; 7
ComputerID;
Nme; c
Room-ID; it1
Date of Fault 05/06/07

the query returns no records insead of the record above.
this is because it is looking for a record in the table where the Computer-ID field contains a null value.

is ther a way to ignore the chriteria if the combobox contains a null value????

i have tried to use vb to convert all null values to be "Like "*"" but it keeps returning a type missmatch error but still changes to combo box value to ; Like .

View 6 Replies View Related

Searching For Record By Multiple Fields

Sep 9, 2006

Hi,

Not sure where to put this, but as I want the information to be shown in form view, I guess this is the bext place to start.

In breif, I have created a database, in which infomation on machinery is held.

The problem I am having (atm), is I have to create a function, which I want to be presented in form form, where by each item can be searched by it's RequestID (unique) or the customer who owns it, so that its current location can be attached (or viewed).

I'm not sure whether i've worded this well, so to sumerise:

I need a form with 3 fields, RequestID, Customer and Location.

Where by I input the RequestID, which brings up the Customer and Location, or input Customer which brings up the RequestID and Location (obviously there will be mulitple results from Customer search).

Again, I hope I haven't confused anyone because it's pretty simple what i'm trying to say, but....

Thanks, Ben

View 1 Replies View Related

Searching For A Persons Name Which Gives Multiple Answers

Jan 31, 2006

Please help me !

I am in the process of creating a database in access to search for a persons name. However, the results will give many ansers. For example if I search for the srname 'jones' I get many names. I eed then to be able to select one of these jones's with the information relavent information.

It has many years since I have done this although i did have quite a good knowledge at the time. Plesae can you make any suggestions and answers as basic as possible !

Many thanks in advance http://forums.aspfree.com/newthread.php?do=newthread&f=18#
Mad

View 7 Replies View Related

Searching For Multiple Terms In Query.

Feb 1, 2006

OK - I'm kinda new to all this stuff (as you're about to find out from my question).

I've got a page with a search form, where visitors enter a search term. That page uses GET to pass the variable to the results page. The SQL on the results page is pretty simple, it just takes the variable from the URL and filters for records:

WHERE columnname CONTAINS
Request.QueryString("passedvariable")

So that all works fine - except that it does not allow for complex searches. If someone searches for 'fish' then that works fine. If someone searches for 'fish and food' then the search looks for "fish and food" somewhere in the record, rather than looking for both of those terms in a record.

Now, I'm guessing that there's no simple solution for who to get this to work, but I'm hoping that someone can help me out and at least point me in the right direction. I'd be happy to post additional code if you need it - but I don't even know what else to post!

Thanks in advance.

View 2 Replies View Related

Show Multiple Fields As One, For Searching Purposes.

Dec 8, 2006

Hello everyone,

Im trying to set up a combobox on my Customer Contact Information form, where you can enter a phone/fax/cell/pager or any misc number, and it will bring up the appropriate customer.

Is it possible to make a query that will show the different phone number fields as one field?

Thank you so much for your help, and please let me know if I can clarify anything.

-Ben Bolduc

View 3 Replies View Related

Modules & VBA :: Searching For Record Using Multiple Criteria

Aug 2, 2013

I have a form where a user enters data. One of the things the user enters is a "lot size". I need this lot size field to be checked against a query in the database to determine if the quantity is acceptable or not.

The order number in this query is in certain cases missing a leading zero, so I need to truncate this from the user entered form field.Finally I want the user to be notified if they are trying to exceed the lot size in the query.My code is as follows:

Code:
Public Function RemoveFirstChar(RemFstChar As String) As String
Dim TempString As String
TempString = RemFstChar
If Left(RemFstChar, 1) = "0" Then
If Len(RemFstChar) > 1 Then
TempString = Right(RemFstChar, Len(RemFstChar) - 1)
End If
End If

[code]....

View 14 Replies View Related

Searching Multipal Tables

Sep 13, 2006

Hello..

I am using the following code to run a query off one table to print a report. Here is the sql statement:

SELECT tblMain.EntryNumber, tblMain.EntryDate, tblMain.EmpNumber, tblMain.DriversLicNumber, tblMain.LName, tblMain.FName, tblMain.MInitial, tblMain.Address, tblMain.StreetName, tblMain.City, tblMain.State, tblMain.ZipCode, tblMain.EmployeeStatus, tblMain.Note, tblMain.LastUpdated, [LName] & ", " & [FName] & " " & [MInitial] AS FullName, [Address] & " " & [StreetName] & ", " & [tblCities.CityName] & " " & [tblStates.StateAbbreviation] & ". " & [ZipCode] AS FullAddress, tblStatus.Status
FROM ((tblMain INNER JOIN tblStates ON tblMain.State = tblStates.ID) INNER JOIN tblCities ON tblMain.City = tblCities.CityID) INNER JOIN tblStatus ON tblMain.EmployeeStatus = tblStatus.StatusID
WHERE (((tblMain.DriversLicNumber)=[Forms]![frmPrint]![txtLicNumber]));


Here is the code I am using on the command button:

Private Sub cmdViewLic_Click()
On Error GoTo Err_cmdViewLic_Click

Dim stDocName As String

stDocName = "rptLicNum"
DoCmd.OpenReport "rptLicNum", acViewPreview

Exit_cmdViewLic_Click:
Me.txtLicNumber = Null
Exit Sub

Err_cmdViewLic_Click:
'MsgBox Err.Description
Resume Exit_cmdViewLic_Click
End Sub

Here is where I am running into the problems.

Problem #1

When I add the tables Spouses and Children to the main table in the query view I get the following erro: more then one table has LName etc.. Which is true. Is there anyway around this problem with out renaming table headers?

Problem #2

If I get problem #1 fixed how can I setup the search function I am using to look in the other two tables to pull information from them? I can get it to pull from one table how can I get it to pull information from all of my tables. I have been trying different things but nothing is working.

I don't know if it has to do with my query not working correctly when I add the other tables etc...

Thanks
R~

View 2 Replies View Related

Searching Fields From Two Tables

Mar 4, 2007

Hi, Im new to Access and VB so please be patient with me. Do know a little about sql queries though.

I have two tables. The first (clones) has another table (BLAST Nucleotide) which is dependent on it (ie: one clone can have many BLAST records)

I want to create a search where I can search by either clone name or BLAST name, and return the results in a list box, and then select an entry from the list & view

Any help appreciated

My zip file of my database can be found HERE

View 1 Replies View Related

Tables :: Multiple Entries In One Field?

Feb 19, 2013

Let's say if I am building a database for a library and in a table the book titles are the primary key and there is a field "authors" to record the authors' names.

But sometimes a book may have several writers and if I type all of them into one field, Access only recognise as one person. So how can I format/set up so that when I use query or filter, each one of names can be identified?

If multiple entries are not feasible, how else should I design my tables?

View 4 Replies View Related

Searching Records From Various Non-linked Tables

Jan 29, 2008

Hi all,
I've got one question again.
Let's say. I've created several tables to store data for several categories.Note: there is no relationship between each table.In each table, there is the date field in which the record is created.
Then I want to make another daily record form that is to find records from all categories which is created at the current date.
Is this possible to search records from various tables and combine them to show in one form or report?
Can anyone help me in this case?
thanks in advance.

View 3 Replies View Related

Listing All Records For Same Field In Multiple Tables

Oct 27, 2006

Hi,

I want to list all the records of a particular field from all tables in my database. The field has the same name in all the tables. Ideally I would like one long list of the records. How can I do this?

Thanks,

Richard

View 6 Replies View Related

Search Multiple Tables From One Form Field

Feb 18, 2006

Hello,

Stuck on a problem and was wondering if anybody has a possible solution. I currently have the beginnings of a database setup that uses a search field on a form to find a reference listing using a part number input by the user. It finds the part number, and then displays all the information about it on another part of the form. My question/problem is, I now want to expand this database to multiple specialties, all with different parts in their respective listings, so they would have different tables. The tables would be the same format, just different information. No one table would ever contain the same info.

Q: How would I set up a form to search for a unique part number over multiple tables, and return that parts info?

It works great for one table, but I am stuck on the multiple tables.

Thanks in advance!

View 5 Replies View Related

1 Form, 2 Tables, 1 Field To Multiple Fields

Jul 1, 2005

Please be kind, i have little VB Knowledge, and wish to expand my learning on this topic.

I have a form that updates information on one table, and has a subform displaying info from another table.

the subform is filtered, and only shows data from what is specified from the filter of the main form.

If I update information on the main form for instance,
field 1, (the data on the subform has the same data so there is the relation), how do i update the subform by only updating the mainform? can this be done through some VB or something?

if you folks out there can give a Smidget of info on which VB codes i can use i can pretty much figure it out.

like Docmd.write something?

View 2 Replies View Related

Tables :: How To Export Multiple Value Field To Excel

Nov 2, 2012

I'm learning Access by myself and i have some problem with exporting a multiple value field to an excel.

I have a field name "Users" in a table wherein this field is a multiple value field and looku up the value from the other table. When I export the table to excel, the data in the multiple value field does not export properly and it just shows some symbol in the excel.

Is there something wrong with my field?

View 2 Replies View Related

Adding A Field To A Form Using Multiple Tables (And A Combo Box)

Jun 15, 2006

Hello!

It's been a while since I've asked a question here, but I can't seem to figure this problem out.

I am setting up a form using data from 4 different tables, all related, and I can't figure out how to add fields that I didn't initially add during the wizard set-up. I used the wizard because it's easier for me to do that and then go in and make the changes I want to make.


When I get data from just one table, all of the available fields from that table are in the "Field List" no matter what fields I chose to include in the wizard process. That's good. But when I get data from more than one table, only the fields I initially chose in the Wizard process are showing up in the field list. I can't figure out how to add a new and different field from one of those tables.

The way I "solved" this problem the first time was to start over, creating my form from scratch--but now that I've done a lot of work, it just occurred to me that I will need to add 2 more fields. (I actually don't even have those fields in a table yet, but I will add them later.) So, this will be an ongoing problem. Since this form will get much use, by several people, I don't want to have to re-create it every time I want to change something.

So, is this possible? How can I add another field?

Also, while I am at it... A combo box was working beautifully to look up specific records when I was gathering information from only one table. It isn't working anymore.

Thanks in advance!

-Siena

View 8 Replies View Related

Tables :: Update Single Field From Multiple Fields

Nov 6, 2014

I wrote a database several years ago and recently pulled it out to give to a friend. The problem is, back then (not knowing better) I set the Employee table up with as a single field "NAME". Now, in order to make it effective, I need the Employee's name in four (4) parts (First, Middle, Last, Suffix).

I have several queries based on the "NAME" field and and would like to avoid changing all of them. I have a simple form "frmUpdateEmployees" that populates the Employee table and Name field. I was hoping to change the form and/or add a query that would be easier and more simple.

Table: Employee
Field: Name
Form: frmUpdateEmployees
Queries: 16 that depend on the table and field above.

View 5 Replies View Related

Tables :: Training Database - Multiple Numbers On 1 Field

Dec 4, 2012

I am creating a training database and i have a courses table. And I need to somehow record all of the different employees that attended a course?

What i can do to make this happen? My employees have an employee number so is there a way of putting all of the employees together in one field? what field would the attendees be? currently is numeric which doesn't seem to work.

View 2 Replies View Related

Tables :: Field For Multiple Sales On Single Record

May 7, 2013

I have a tblCustomer table to track customer information. One field is for flights the customer books. I expect that a customer might book more than one flight, which should affect what he owes. (My tblFlights table includes fields for the cost of each flight.) How should I make the Flight field in tblCustomer so it accepts multiple flights and reflects on the customer's final bill and my own accounting tables that I haven't even created, yet?

I'm probably overthinking everything. This is just an Intro to Access class, and I only need 3 tables with 1 relationship. So far, I have 9 primary tables, 4 duplicate tables (for M:N relationships) and 11 relationships. I have to have tables for financial data, customer info and product/service info. I wasn't sure what I would need for my fictional airline, so I created tables to track everything I could think of, hoping to cover all 3 required types of data in the process.

View 2 Replies View Related







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