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 Replies
ADVERTISEMENT
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
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
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 5 Replies
View Related
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
Jun 1, 2007
Hi Everyone,
I am trying to compare/relate two columns in two tables to each other in access, where the results shoud return similarities among both colums from both tables.Even if one column has some parts of it.
Example;
T1 T2
Name Members
John Johnson.kay
mike mike
Daniel Danielson.mic
Richard Richardson
I tried; "like[T1.Name]*" in the criteria section of Members.Need Help pls.
View 13 Replies
View Related
Aug 22, 2014
The problem I'm having is that i need to combine 12 tables with multiple columns and not all columns have the same headers. So if a column is present it should be updated, if not it should be added.
The end result should be one column with all records and all columns that exists in all 12 tables.
The tables have an identifier that is the same for all columns (supplier reference).
Table 1 has 56 columns
Table 2 has 42 columns (of which some have the same header)
....
(--doing every column manually is something i would like to avoid if possible --)
View 5 Replies
View Related
Nov 8, 2012
I am working with a large database of 8,000+ entries. We have five tables in this database, but mainly use three of them. The main table is "OAKE Main Table" which is basically a list of all members our organization has had. Each member has accounting data linked to their member ID number which has been generated by ACCESS, and can be accessed or edited by clicking on a sub table next to each member. The accounting data that has been manually entered in the OAKE Main Table can be found in the Accounting table.
Here's my issue. I need to be able to extract a list of transactions from this accounting table since January 1st. I can do this no problem, but all I get is the member ID and the accounting data. How do I get ACCESS to include the member ID's first and last name? or maybe even the first, last name, address, city, state, zip, etc so that I can get a list of people who have made donations, for example?
I'm assuming this will have something to do with the relationships we have set up in our table. Here's a picture of it. I have no clue how to make this work.
View 8 Replies
View Related
Apr 17, 2014
I have a combo box with 2 columns, Item # and the Item Description. As of right now I have to type the Item # and it will autofill the text. I was wondering if there is a way if I don't have the Item # that I can search/type the description in the combo box field and it will autofill?
View 2 Replies
View Related
Aug 23, 2011
I have a requirement of searching the database based on 3 columns. I have attached the search form and the code.
Private Sub cmdSearch_Click()
Dim strSQLHead As String
Dim strSQLWhere As String
Dim strSQLOrderBy As String
Dim strSQL As String
Dim strJoin As String
[Code] .....
View 12 Replies
View Related
May 14, 2014
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
View 2 Replies
View Related
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
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
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
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
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
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
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
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
Jul 31, 2013
I have three tables. Risk, Names and RiskAndNamesJunction table. I have the junction table because I have many to many relation (meaning many people can be connected to one risk and many risks can be connected to one people).
The problem is that If I make a query to show the people related to the risks, if there are many people for one risk then it will put the people in different rows. Meaning that for risk 2 I will have three rows, because there are three people connected to this rows. See the attached file!
What I would like to do is to have a query which (in case there are more than one risk owners) puts the second name in another column, the third name in another column and so on. So I will have only one row per risks.
The attached file is a dummy file, so there are only maximum three names per risk. In the real file the maximum is five names per risk. So I am talking about no more then five extra columns. (So I am talking about a query which would put the first finding in the first extra column, then the second item in the second and so on till five. It there is no third or fourth or fifht item then the columns remain blank).
Unfortunately I have to do this because our mother company works with excel and they are sticking to this format in excel.
See the attached file ....
View 2 Replies
View Related
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
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
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
Aug 17, 2007
I am trying to build a database for my extensive DVD collection and I want something that shows more than one record per line (I ultimately want something with just an image and title for each record with maybe 4 records per line). This is my first venture into using MS Access, but I've gotten the basics down and have already set up the table & form. The only time I have found something that would display the way I want things is in a report, but it will only display it with multiple columns in print preview mode (which won't work because the titles are going to be hyperlinks and need to be clickable). If anyone has any ideas, they would be greatly appreciated. If I'm not on the right track and this can be done from within a form, then please set me straight. Thanks again in advance.
View 3 Replies
View Related
Jul 21, 2005
I am trying to display five columns from three seperate tables. I thought the GROUP BY operator would fix this, but I have more than one column to select. The query is fine but I have redundant fields showing. Oh, I'm not worried about the calculation "[employee.salary]*0.1 AS Salary_After_Raise." I just want to concentrate on narrowing the fields down first.
SELECT ID, FirstName, LastName, Salary, [employee.salary]*0.1 AS Salary_After_Raise, City
FROM worksat INNER JOIN store ON worksat.store = store.storenumber, employee
WHERE worksat.store = store.storenumber
GROUP BY city, id, firstname, lastname, salary
ORDER BY city;
View 1 Replies
View Related
Dec 13, 2005
hi
is it possible to have multiple columns within a crosstab query??
if so please specify how. at the moment i am not allowed to specify column heading for more than one item
thanks
View 1 Replies
View Related
