1 Query Pulling Info From Multiple Identicle Tables
Feb 14, 2007
Hi guys.
For my coursework I was told I need complex queries to gian high grades.
This is my problem:
I have many many tables all identicle. They have the same field names just different information on them.
I wanna be able ot query ALL those tables to bring 1 result which I will then make a report out of.
I have tried everything, I fidled with the relationship but can't understand it.
Oh, and the tables have been linked form excel. I know i can't edit it then, but i dont need to.
I would really really apretiate all responses as this needs to be done asap.
Thanks in advanced.
View Replies
ADVERTISEMENT
Nov 8, 2004
I am trying to get an email address from a table by choosing a employee number from a combo box...it is almost working,
however, it is returning the employee number as opposed to the email address...here is the code I am trying to use:
Private Sub Combo0_AfterUpdate()
Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = myConnection
Dim mySQL As String
mySQL = "SELECT EmailName .*,[Employees] FROM EmployeeNumber"
myRecordSet.Open mySQL, , adOpenStatic, adLockOptimistic
myRecordSet.MoveFirst
myRecordSet.MoveNext
myRecordSet.Close
Set myRecordSet = Nothing
Set myConnection = Nothing
End Sub
Then, in the section for the command button and the Outlook code, I have:
eMailAddress = Me![Combo0].Value
like I said, it is returning the same value that is in the combo box...if anyone could help, it would be greatly appreciated
View 1 Replies
View Related
May 7, 2013
I have DB used for inventory for many different categories. I have a table and form for the following: Location, Printers, Pc's and many more.
What i am trying to accomplish is to have a advanced search form that will display how many pc's and there makes and model from selecting the location name or Cost center from a combo box.
So an example would be I want to select MPP-WDF from the combo box click a button and it will return the number of PC and there makes of model's and some other information in a list of records.
View 9 Replies
View Related
Dec 26, 2012
I need to pull data from multiple tables in order to show a "financial summary"..Currently I have: Company; BalanceSheet; Debt; Liabilities; Income..All tables have a lot of information (which is why I built them in multiple tables).
I need to build a form where I can use a combo box to select a company from a list.Once selected - I need to the form to pull selected information from each of the above tables. (As well as perform some math functions).I've been struggling with the relationships (They don't seem to make a difference) and I believe I am above and beyond what the wizards will accomplish.
I have read thread after thread but cannot seem to find a specific answer on how to accomplish this.To make matters more complex - Once finished I want to be able to select multiple companies and create a report from the fields mentioned above (IE: pick company A, B, and C and have all of there "current Assets" add up on one report)
This task was originally achieved using an Excel spreadsheet but it has become to confusing for users and difficult to save information for future use.I believe all of my fields are constructed correctly. how to compile the data from multiple tables into one form/report.
View 5 Replies
View Related
May 17, 2006
Hello,
New to access :eek:
Trying to create a simple data entry form to get information and populate fields from another table. i.e. type in info in one field and the other fields are displayed in the form with information from another table.
Any help apprecated :)
View 3 Replies
View Related
Apr 7, 2015
I have now been asked to create 'something' (form, query, whatever) that will:
Allow the engineers to change the date in multiple tables for parts; and,Change (in)activity status information in those tables; and,Delete the same obsolete part(s) from two tables and add that information (one entry) into another table.
View 1 Replies
View Related
Mar 6, 2014
So I have two tables:
users
primary key = user_id (AutoNumber)
surname (Text)
forename (Text)
...
user_change
primary key = user_change_id (AutoNumber)
user_id (Number) which relates to the user_id from "users" table
change_type (Text)
action_date (Date/Time)
...
In user_change I record any changes made to the users table.
What I am wanting to do, within a query, is pull basic details from the "users" table (forename, surname, etc.) which is working fine, but also add in SPECIFIC data from the user_change table if it exists.
I want to pull ALL rows from the users table, not just specific rows, and not just rows where my criteria for the user_change table match.
This is the data I want to pull from user_change...
The MOST RECENT action_date WHERE change_type is LIKE "*issued*".
However there won't be a change_type LIKE "*issued*" for everyone - I want it to be included only if it exists.
At the moment my query is ignoring any users who don't have a user_change record with "*issued*" in the change_type value. I'm also getting duplicate user rows where people have more than one value for "*issued*" - I only want the most recent one...
View 7 Replies
View Related
Sep 25, 2005
Hi there,
I just can't figure out how to solve my next problem. What I need to do is to make a query showing all the country's next to germany(nr.5) with their capitals. There are three tables which I can use, nr.1 showing the country_id's with their "neighbour country's", nr.2 showing the name of the country's with their capital_id's and nr.3 showing the name of the capital with their corresponding country-id.
Is there anybody who could help me with this problem, or has some suggestions? If you find this message unclear, i would love to e-mail you the tables.
As i am dutch my english isn't that good.
Thanks in advance
1:
country_1 country_2
12
13
14
15
24
25
26
210
35
45
57
512
612
89
1011
1314
2:
idname capital_id
1Belgium 1
2France 4
3Netherlands 9
4Luxembourg 14
5Germany 6
6Italy 12
7Denmark 15
8United Kingdom 11
9Ireland 16
10Spain 17
11Portugal 18
12Austria 19
13Sweden 20
14Finland 21
15Greece 22
3:
idname peoplecountry_id
1Brussel 1370001
2Antwerpen 4970001
3Gent 2357001
4Parijs 21762432
5Lyon 4184762
6Berlijn 34000005
7Bonn 2930005
8Munchen 12670005
9Amsterdam 10380003
10Rotterdam 5760003
11Londen 66780008
12Rome 28260006
13Milaan 15150006
14Luxemburg 790004
15Kopenhagen 4820007
16Dublin 5260009
17Madrid 320000010
18Lissabon 80700011
19Wenen 151200012
20Stockholm 65100013
21Helsinki 48400014
22Athene 100000015
View 3 Replies
View Related
Aug 24, 2012
I am importing the updated Employee Roster information from Excel to a table called "Weekly Roster Check" (contains new info). I have a table called "Current Chit Board" that has an Employee Roster that I update every week (contains old info). I want a query to find employees who are promoted and their title has changed. then I want to run an update query to update these results to the Current Chit Board table.
Every method I try, returns either no information or information that is not what I want.
View 3 Replies
View Related
Nov 29, 2007
Can a Append Query move all my data stored in multiple tables to another database with a identical table structure?
Because as I try to work the query, I keep getting prompted to "Select a table" I want to append to, and I don't want to append to just one table...
View 4 Replies
View Related
May 2, 2014
I have datasets in tables for some price data, which is monthly. Each month there is a list of unique ID codes for the products ("CAP Codes") and then a price for that product ("LM Retail").The table names are simple dates (2010 01, 2010 02 etc) I want to have a query which would create a table which follows the price of each product through the months. All of that would be simple except not all of the products appear every month, so when I set up a simple link between them all it only outputs products which appear every month. Is there a way that it'll keep the products with just some prices - leaving a blank in the table created when there isn't a price that month?
View 3 Replies
View Related
Apr 29, 2014
Ok, I have 3 tables. One lists a contact in conjunction with the branch of the company and the trips that contact takes. The second lists a contact in conjunction with the branch of the company and which team they work with (may work with many). The third should list their email address and their phone number. Is it possible to pull the contact name and branch of company from tables 1 and 2 into 3 automatically, such that all I have to input into table 3 is additional contact information? If that is possible, is it also possible to only pull each contact/branch of company pairing once (I don't want four entries for Joe Shmoe/Sales, even if he's taken 4 trips)?
Table 1
Branch of Company
Trip Dates
Trip Location
Contact
Sales
4/1/14-4/12/14
Chicago
Joe Shmoe
HR
6/2/13-6/4/13
New York
Jane Doe
Table 2
Branch of Company
Company Team
Contact
Sales
Blue Devils
John Deere
Sales
Jets
John Deere
Sales
Jets
Joe Shmoe
HR
Sharks
Jane Doe
Table 3Contact
Branch of Company
Email
Phone
Jane Doe
HR
jane.doe@company.com
800-555-1234
Joe Shmoe
Sales
joe.shmoe@company.com
800-555-1235
John Deere
Sales
john.deere@company.com
800-555-1236
View 4 Replies
View Related
Apr 12, 2013
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
View 1 Replies
View Related
Dec 13, 2007
I'm attempting to build a simple database for a martial arts tournament registration. The weight division (Fin, Fly, Heavy, etc.) depends on the age, the sex and the weight. I've tried this several different ways, most recently by using one table called "competitors" with all the entrant's information, and several other tables based on the age class/sex (i.e. I have a table called Age 8-9 Male with the weights and divisions in it, and other tables for the other ages/sex).
So, basically, what I'd like to have happen is a report that will lookup in the correct table the division, based on the weight entered. In other words, for a record where the age is 8-9 and the sex is Male, the report compares the weight to the values in the 8-9 Male table, or if the record is a 12-13 Female, it looks in the 12-13 Female table for the weight, and places the correct division in the Division field of the report.
Probably going about this wrong.....but any help would be appreciated.
View 5 Replies
View Related
Jun 13, 2012
I'm a bit new to Access but have managed to build a very simple database.
I have a main table which I need to add 4 columns onto the end of, the data for which comes from 4 tables linked to it. Below is how they are linked.
Main table------> Table 1
Main table------> Table 2
Main table------> Table 3
Main table------> Table 4
All 4 relationships have a join type of 2 (Include ALL records from 'Main table' and only those records from 'Table1/2/3/4' where the joined fields are equal)
However, 3 of them pull back the correct data when I refresh the Main table, but the 4th one doesn't - which appears to be to be set up in the exact same way. It does bring back data, but it's the wrong data or in the wrong order.
For example, if the related field in the Main table is "Sarah" - It needs to bring back "Programme", but some Sarah's pull through Programme, some are blank and some are another option altogether. Table 4 has no duplicates or typos etc and I've tried deleting it, loading it in again and creating a new relationship but nothing seems to work.
View 5 Replies
View Related
Feb 9, 2007
I am really new at access 2003 and queries s forgive me if this question is really dumb/simple/confusing...here goes
I created a query where I ask the user to provide the info for the date range..<[Before date]
it worked fine.
I then added something to the database and ran it again and it now asks me for my date not once, but twice..i then copy and paste query with a new name and it asks me for the same info 4 times
Help me please..Thanks
View 2 Replies
View Related
Feb 5, 2014
I have an access database and I plan on splitting it so the FE will be on each users workstation and the backend will be in a folder on the server. But is there anything else I need to do to allow for the possibility of multiple users using the database and changing info at the same time?
View 4 Replies
View Related
Mar 11, 2007
Hi, I was wondering if someone could check this code over. I am trying to pull a query (titled: Codes_+_Conduct:_General)which was already created in Access. Here's the code: (the red is where the error message I get is)
-----------------------
<%
Set objCon = Server.CreateObject("ADODB.Connection")
objCon.Open connectionstring
Set objRS = Server.CreateObject("ADODB.Recordset")
strQuery = "SELECT * from Codes_+_Conduct:_General order by subject asc"
objRS.Open strQuery, objCon
IF rs.EOF Then
Response.Write("<tr><td colspan = 3>No Tips</td></tr>")
Else
Do While not rs.EOF
x = x + 1
%>
<strong><%=rs("subject")%></strong><br>
<strong>Q</strong> <%=replace(rs("question"),vbcrlf,"<BR>")%>
<br><br>
<strong>A</strong> <%=replace(rs("answer"),vbcrlf,"<BR>")%>
<br><br>
<%
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
Set objCon = Nothing
%>
------------------
The error message I receive is:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
/faqs2.asp, line 13
If anyone could offer me any help, I would greatly appreciate it. Thank you.
View 4 Replies
View Related
May 16, 2005
It seems that there are a few different ways to get info from the tables when developing an applications that i know of:
1)queries
2)dao
3)ado
4)selects without any of the above
I lean towards the last 2 but I need to know this: is there any advantages in using ado as opposed just sql?
For instance i ran this sql:
Private Sub Command0_Click()
Dim SQL As String
Dim strCriteria As String
strCriteria = Forms![form2]![Text1]
SQL = "SELECT * FROM Table1 WHERE (((Table1.clinic)='" & strCriteria & "'))"
DoCmd.OpenForm "frmClinic"
Forms![frmClinic].RecordSource = SQL
End Sub
it worked fine. I could see how a beginner would prefer to use queries over sql but I would prefer to use sql in vba. I've read that dao is older and I should use ado instead. So that leaves ado vs. the way I displayed it in the above code. So which way should I go? If I'm over looking queries or dao in favour of ado/sql, just point out their advantages if you don't mind.
Thanks,
scratch
View 4 Replies
View Related
Jun 7, 2006
************** edit: Fixed!!!! *************
The database I'm working on is coming on nicely, thanks to some valuable help from this forum. But I've got a couple of new problems that I just can't seem to get my head around. Really hope someone out there can help!
So...
I have two tables (well, there are more.. but there are two main ones with the important data on them). The first is a list of Customers (you know, the usual Name, contact, telephone etc...). The second is a list of jobs for each customer.
After starting from scratch, I created a nice looking form wth control tabs that on one page shows you the customer information and on the second; the job history for that customer. I then have a third tab which lets you add new jobs. So I'm all chuffed because that is the basics of what I wanted it to do.
However, I need a hard copy of the job report to print out and give to an engineer to fill in or to print in future should a customer wish to see it.
Try as I might, the reports function didn't look as if it was something that could be "designed" the way I wanted it to look. So, I figured another form was in order.
I started by building a query which included all the fields from the two tables mentioned above in it; I.e. So it would pull up a Job Printout by a workorderID number. That way it would show all the company info PLUS the detail of that one particular job.
Problem is this: I can see from the query in table view that the Jobs are listed; but alongside them is a straight listing for all the companies in the database. Basically, the Company who received the job in question is not being shown by the query.... if you follow me. (Apologies if I'm explaining this like a fool).
Question 1 then... is how do I fix this? Is this something to do with these arcane relationships things?
Question 2: How do I create a button to print that one "form" by workorder ID.
Question 3: Did I do the right thing by using a "form" or is there a better way to create a "report" for printing that can be formatted the way I want (with logos and stuff)
Thanks again peeps!
View 3 Replies
View Related
Sep 29, 2007
I'm a genealogy nut and not so great at computers. I've got a report on cemeteries that gets things like a cemetery number, peoples names and tombstone information from one table. Got that working pretty well. I've got it to print the cemetery number in the detail header (there are 300+ cemeteries each with 1-100s of people in each). I want to take that number and use it in a different table to get travel directions and GPS for each cemetery.
View 3 Replies
View Related
Apr 2, 2012
I am very new to access and I am trying to create two tables called Customers and Orders.
The fields in Customers are:
CustomerID (Primary Key), First Name, Last Name, Address.
The fields in Orders are:
Order# (Primary Key), CustomerID, Product Purchased, Address.
There is a One to many relationship between CustomerID in the table Customers, to the CustomerID in Orders.
The problem I am having now is with the Address field.
GOAL: What I am trying to do is have the Orders table automatically get the Address from the Customers Table.
If that doesnt make sense, whenever I create a new Order, I want to enter the customerID, and have it get the Address data from the Customers table automatically put into the proper field in the Orders table.
View 5 Replies
View Related
Apr 13, 2007
Hello Everyone,
I've been trying to figure out why my query only pulls record Object ID 10011399 and not the other one, but I can't figure it out. Can anyone please help me see what's wrong?
Thanks,
Pablo
View 13 Replies
View Related
Aug 10, 2007
I am having an issue pulling information from a form into a query. In my criteria column in the query i have:
[Forms]![frm_createcsv]![txtCriteria]
it pulls from a valid string from a textbox txtCriteria.
My vba code is:
Dim query as string
query = "pkg_not_registered_email"
filename = "C:querytester" & Format(Date, "YYYY_MMDD") & "_" & cboCountry.Value & "_" & ".csv"
DoCmd.TransferText acExportDelim, "", query, filename
DoCmd.Open query
____________________
This exact code worked previously with another function and a different query so I do not understand why it is not working now! Help! :(
View 7 Replies
View Related
Sep 9, 2004
Hi
Apologies if this is really basic - I am trying to write a query to show all records from table A linked to table B so that if a field is blank in table B, it will show. At the moment, every time I run the query, it only shows me records which have entries in both tables. (They are linked by ID no).
ie there are 77 records in table A. Only 23 in table B but I want a query showing which ones in table A do not have something in field "x" on table B. Does that make any sense ? As ever, I'd be ever so grateful for help.
While I'm at it, does anyone know anything about getting a database to produce reminders ie based on date entered, highlight when 3 month review is due ?
Thank you thank you
View 5 Replies
View Related
Oct 30, 2014
I created the database in the attachment. I have three tables which are connected. I am trying to create a form to show the connection of the tables. As you can see, I have inserted the 'Item Code' in the form 'Products'. I am trying to add 'Cndl Codes' and the 'Cndl Quantities' of each 'Item Code' showing above 'Item Code' in the form, but I don't know how to do it.
View 1 Replies
View Related
