Combining Tables To Make One Big Table?
Oct 11, 2006Hi
I have several small tables that I want to make combine into a big table.
How do I do this - is the only way by using a query?
Maria
ADVERTISEMENT
General :: Combining Make Table With Union Queries
Aug 13, 2013Wondering whether it is possible to use the 'Make Table' function in conjunction with a union query. My early attempts have failed and I am relatively new to Access.
I am using Access 2010.
Combining Rows From Multiple Tables Into A New Table
Nov 21, 2006I have three tables, with the following data (fields separated by "-" here):
Unit - Customer Number - Customer Name - Type of A/R - Total A/R
Unit - Customer Number - Customer Name - Type of A/R - Total Overdue
Unit - Customer Number - Customer Name - Type of A/R - > 90 Day Balance
I would like to combine these tables into one table with the following field names:
Unit - Customer Number - Customer Name - Type of A/R - Balance
When I append the data, it comes out like this:
Unit - Customer Number - Customer Name - Type of A/R - Total A/R - Type of A/R - Total Overdue - Type of A/R - > 90 Day Balance
I would like this new table to include all rows from the original tables, sandwiched on top of each other. As far as I can work it, the Append Query only adds the data as add'l columns; not as add'l rows. I tried changing the field names entirely, so that the final field in each table is called "Balance." However, I still get the same result as above - the columns are added, instead of rows. I swear there's a way to do this, I just can't remember it!
Tables :: Combining Two Text Columns Into One In A Table?
Aug 16, 2013My Approach database contains records relating to nearly 800 sites in London. There are radically different amounts of data held about each site. The database contains a lot of different tables, each containing a different class of information. Not all the sites listed in the database have information in all of the tables.
The unique thing that holds it all together is the unique site reference number. Unfortunately this is split into two separate columns in the Approach database, "Reference" and "Suffix". Not all sites have a suffix. (The purpose of the suffix is to identify sub-sites which are subordinate to the main site reference, but need to have their own individual records.) Because many sites have no suffix, most of the fields in the second column are blank.
In order to link all the tables together in access I need a Primary Key which is unique to each site. In this case the reference/suffix number is the obvious (only) candidate. There is no problem using two different columns to create a primary key. The problem I face is that it doesn't like the fact many of the fields in the second column are blank.
My solution to this is to combine the two columns into one. That would give every site a unique reference, and none of the fields in the combined column would be blank. Can I work out how to do it? All I want is a new column that displays the reference and suffix (if any) in a single field, no spaces.
Tables :: Combining Several Tables Into One Table
Aug 23, 2013I have 10 tables. None of them have any entries in them. Just field names. Is there a way to combine them all into one table.
View 14 Replies View RelatedTables :: Make Calculated Field In One Table That Calculates Values From Another Table Linked By Order ID
May 13, 2015I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
View 3 Replies View RelatedTables :: Make Table Queries - Indexing?
Jan 2, 2014I have a complex series of calculations to perform, and I have found the best way to achieve my end is to create new tables by the Make Table queries.
The trouble is I have many tables to create and the downstream tables refer to the previously created tables; I have recently discovered that queries perform much quicker if they are indexed (duplicates OK). I've looked at my tables created by the MakeTable queries, and they all seem not to be indexed.
How do I make them indexed automatically?
Tables :: Make Query Temp Table
Oct 9, 2013All using access 2010. I have a multiuser database that I feel would benefit from splitting into a backend with multiple user frontends. My problem is that there are tables from make table queries processed every two weeks that all users need access to. As far as I know; you can not put a table in the backend that you will delete and remake or a temporary table and link to it in the front end. Is there any other way I would be able to split the database and have temporary tables linked from backend to frontend that I am not aware of? Is there another way to creating temporary tables and tables from make queries? This database has 9 users and counting and really needs to be split. r
View 14 Replies View RelatedTables :: Replica Trying To Make A Column Addition To Table
Sep 11, 2014I have a replica at work, 1 copy on the network, 1 local.. I need to add a column to the main table, getting "Operation is not supported for this type of object".... I have users that use the database but was given a change to the form/report which requires a column addition. What is the simplest way to get around this??? I'm trying not to get into major problems.
View 1 Replies View RelatedTables :: Make Table Returns Multiple Records
May 11, 2013I have two tables with compatible data. Table 1 has 23 records and table 2 has 5 records. I am trying to make a "make Table". But instead of a table with a combined 28 results, I get 115.
View 1 Replies View RelatedGeneral :: How To Combine 2 Tables With Same Primary Key To Make One Table
Oct 18, 2012How to I combine 2 tables with the same primary key to make one table? I have seen several thigns but none have worked. I seen inner and outer joins. All 4 of my tables have the same primary key. I just want to combine all the tables to make one table. I have a form with subforms but if I can combine all the tables and work off one rather then 4 I will be happier. Every week to 2 weeks I upload new data from excel. How would I after I add the new data combine all tables to one super table?
View 7 Replies View RelatedTables :: How To Make Table Not To Fill Entire Screen
Nov 11, 2013No matter how much I maximize or minimize Access (2010) my tables fill the entire screen. This happened once before but I don't recall the fix. It was something very simple. How to make the table not fill the entire screen?
View 9 Replies View RelatedTables :: How To Make Constraint When Putting Records Into Access Table
Sep 29, 2014I am developing a simple database for students to register courses using Microsoft Access 2010. I have 4 tables: Student, Register, Course, and Section (1 Course can have many Sections).
Then, there is a criteria that a student can only take up to 6 courses. Hence, may I know that where and how can I include this constraint? I believe that it should be in the Macro (Before Change) of the table Register...
My relationship is: Student -> Register <- Section <- Course
Tables :: Password Protect A Linked Table Or Make It Read Only
Nov 14, 2012I have a table that I imported from another database. I used import Access - Link to the table source by creating a linked table. - then downloaded in my database.
I want to use the linked table to create relationships and forms but do not want to even accidently edit it. The table gets updated in the database on a weekly bases and I am hoping it gets updated automatically in my database as well. does it automatically get updated or do i have to set up something?
Also, I want to protect the data in that particular table and either password protect it or make it read only. Can I do that? If so, how?
Combining Tables
Feb 19, 2006I have just started using MS Access to enter customers into a database. I constructed the database and delegated the .mdb file to my coworkers so that they can enter their customers as well. Now I would like to combine all the customers entered by all the employees into one general table/database containing all of the customers entered by all of the employees. If anyone knows how I would go about doing that I would really appreciate the knowledge. Thank you.
View 3 Replies View RelatedCombining Tables
Feb 1, 2005Hi,
I want to join (combine) two tables that have only identical column fields in common into one master table. There is no relation between the two tables. This master table I will use afterwards as basis for a excel pivot table. How can I do this?
Suppose I do not have identical column fields. Is it still possible to combine the tables and that in the new table the missings are filled with eg zero's.
Thx,
Agamemnon
Combining Tables... HELP!
Feb 22, 2007:confused: I need help desperately. I have two tables I am trying to comine in a query. Seems easy enough right? Yet the simple task has become impossible. I have two tables with the same feild for data, both containing 8 coulmns. Ex:
Table A-January
Columns
A B C
Data: 1,2,3
Table B-February
Columns
A B C
Data: 4,5,6
I'm trying to create a query that will contain the data 1,2,3,4,5,6 but am getting 11111111111,222222222,33333333333,4444444444,55555 55555,6666666, or getting 1,3,5. I've tried refining the data in many ways, even tried just created a table. Hand keying it in seems rediculous due to the end numbers being over 10,000. Any advice??? Please??? :(
Combining Data From Two Tables
Jul 5, 2005As the title suggests I would like to combine the data from two tables.
The first table records customer orders in fields - Part No / Quantity / Reqd Date Of Delivery
The second table is an output from a scanning s/w package we recently installed.
This provides the following data - Pack Qty / Part Number / Date Scanned.
The first thing I did was create a simply summing query to add up all the pack qtys for a specific part on a specific day.
Now comes the hard part.
What I want to do is sort and combine both tables in chronological order.
Where the part number & date for an order and a delivery match, I require the record to be combined to one record (in this query).
Where it does not match, then it should remain as two seperate records.
So for example if you ordered 100 footballs for delivery on 11/11, and we delivered 110 footballs on 11/11, the query would return:
Footballs 11/11 100 110
If the delivery was early / late we would get:
Footballs 11/11 100 0
Footballs 12/11 0 110
Now this last bit is where my queries (simple cross table queries) falls down as I only have three options when creating the join properties, ie both match, all from left table or all from right table, whereas I want all from both tables even if they do not match.
Maybe a query is the wrong way to go, but as the table from the scanner s/w is updated on a daily basis it would seem to me to be the best way forward.
Can anyone help?
Combining Tables In A Query
Sep 27, 2006I am rather new to access and only have a public education, so be gentle. My problem is I have made two tables within access. The first one is names and other information (5800 accounts). The second one is names and phone numbers (2300 accounts). The names are in the system exactly the same for both tables. All the names on the second list are contained within the first list. I need to create a table or query that will match the names with the phone numbers and also show me the names that I do not have the phone numbers for. Basically I need a list that has 5800 accounts, shows who has a phone number and who does not have a phone number. I have made a relationship between the two tables based on the person’s name. I used the code below and put the code in the “criteria” and “name” block. However, I received no results. When I remove the code from the query I receive only the results from list 2 (2300 accounts) and no data from list 1. Any Ideas?
FROM [5480 Master Data] INNER JOIN [5480 Meal Collection] ON [5480 Master Data].name=[5480 Meal Collection].name
Combining 2 Tables Into 1 Using Queries
Nov 13, 2006I've been asked to make a DB for our production. We have 2 sources, of which have similar field names and collect completely different data, but can not be linked. I don't want them to be linked either, I don't think. The only thing I want to do is to take the 2 tables I have from my 2 sources and combine them into one. I'm using the first table in a make table query and then creating blank fields in the query also, so I can append the data from the second source in the "all production" table. The only problem I'm running into is that I can only get to 26 fields in the make table query, I need 31 fields to get everything. I tried creating a new query and not using any tables, and I was able to get to 16 fields. Is there something I'm doing wrong, or is there another way I can get a table created will all fields I need and append the data from both sources? I would need to be able to re-run this daily which is why I was trying to do it through a query. Thanks for any help/ideas. It's greatly appreciated!
View 5 Replies View RelatedCombining Records From 2 Tables
Jan 3, 2008Hello all,
I have two MS Access tables; table1 contains installing records (fields = serial, installdate) and table2 contains de-installing records (fields = serial, deinstalldate). Tables are structured as followed:
Table1:
serialX installdate1
serialX installdate2
serialX installdate3
Table2:
serialX deinstalldate1
serialX deinstalldate2
I'd like to combine the above two tables to get:
Table3:
serialX installdate1 deinstalldate1
serialX installdate2 deinstalldate2
serialX installdate3 null
Is it possible? Right now, whenever I joined the two table on serialX I got 2 deinstalldate's for each installdate, e.g.:
serialX installdate1 deinstalldate1
serialX installdate1 deinstalldate2
serialX installdate2 deinstalldate1
serialX installdate2 deinstalldate2
..........
Thanks in advance for your time and help! I'd appreciate it!
Best regards,
Andy
Tables :: Combining Fields In Different Tables
May 29, 2014I'm creating a database which would automatically assign a unique workorder number in the "WorkorderNumber" field of the "Workorder" table. Note: this will not be the Primary number for the work order.The WorkorderNumber will be developed by combining fields from the "System" table. Fields used to create the WorkorderNumber from the "System" table are:
1) Location [currently in the field is "MAX"]
2) CalendarYear [currently in the field is "2014"]
3) NextWorkorderNumber [currently in the field is "1"]
I need the following to happen to the "WorkorderNumber" field of the "Workorder" table:
a. I need the field to read as followed: Max-2014-00001
b. I need the number 00001 to autonumber to 00002 on the next entry of a new request. [MAX-2014-00002].
c. I need to be able to control how the "WorkorderNumber" field populates by changing the "CalendarYear" and "NextWorkorderNumber" fields within the "System" table without messing up prior workorder numbers already populated.
Combining Info In Tables For A Query
Sep 25, 2005Hi 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
Combining 2 Different Tables To Create A Query
Sep 26, 2006I am rather new to access and only have a public education, so be gentle. My problem is I have made two tables within access. The first one is names and other information (5800 accounts). The second one is names and phone numbers (2300 accounts). The names are in the system exactly the same for both tables. All the names on the second list are contained within the first list. I need to create a table or query that will match the names with the phone numbers and also show me the names that I do not have the phone numbers for. I would really like to have one massive table that contains all the information from both lists. I have made a relationship between the two tables based on the person’s name. Any ideas? Thanks.
View 4 Replies View RelatedQueries :: Combining Tables In A Query
Jul 9, 2014I have an invoice form that is from tblInvoice, and on that form i have 5 subforms all tied to 5 tables, tblRepair1, tblRepairs2, etc. When we get service done at a shop the user can enter up to 5 repair types, which all five tblRepairs are joined to the InvoiceID of the tblInvoice.
the issues is one user might put oil change in repair 1 and tire rotation in repair 2 and the other 3 repairs not used. another user may use all 5 repairs and put oil change in the repair 5 field. What i want to do is query a repair type, Oil change, and get every InvoiceID record that has oil change in it regardless of what repair table it was stored in, or in other words regardless of if it was entered in repair1 or repair5.
i have tried to "step down" the criteria referencing the cbx on the search form but if queries blank. I only want the records that have an oil change in them but still want to see all the repairs that were done with it.
Combining Multiple Columns Into One Longer Column (without Combining Fields)
Jul 17, 2013I trying to combine three columns that I have into one column without combining fields.
Currently what I have:
(see image below)
What I want:
ID-----MOC
##----name1
##----name2
##----name3
##----name4
##----name5
etc
The list I have will be much longer and will be changing frequently, which is why I can't just go on excel and manually do this.