Combining Data From Two Tables
Jul 5, 2005
As 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?
View Replies
ADVERTISEMENT
Aug 17, 2012
I'm trying to retrieve information from 3 tables (Order, Receive and Issue) to one table as Inventory Table/Query. it looks like as follows
Order Table:
OrderID
ProductID
OrderDate
OrderQTY
1
1
3/1/2012
30
2
1
4/1/2012
10
3
2
4/1/2012
20
Receive Table:
ReceiveID
ProductID
ReceiveDate
ReceiveQTY
1
1
3/3/2012
30
2
1
4/3/2012
10
3
2
4/2/2012
15
Issue Table:
IssuedID
ProductID
IssuedDate
IssueQTY
1
1
3/5/2012
20
2
1
4/6/2012
10
3
2
4/5/2012
5
I'm looking for Inventory table/query which will give all of the data per date, which is like the following
Inventory:
Date
ProductID
OrderQTY
ReceiveQTY
IssueQTY
3/1/2012
1
30
3/3/2012
1
30
3/5/2012
1
20
4/1/2012
1
10
4/1/2012
2
20
4/2/2012
2
15
4/3/2012
1
10
4/5/2012
2
5
4/6/2012
1
10
View 3 Replies
View Related
Feb 19, 2006
I 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 Related
Feb 1, 2005
Hi,
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
View 9 Replies
View Related
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??? :(
View 14 Replies
View Related
Sep 27, 2006
I 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
View 3 Replies
View Related
Nov 13, 2006
I'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 Related
Jan 3, 2008
Hello 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
View 6 Replies
View Related
Aug 23, 2013
I 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 Related
May 29, 2014
I'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.
View 1 Replies
View Related
May 15, 2013
I currently have an access database for tracking company assets and performing maintenance tasks on them.We also have a number of Stores Stock Items that are controlled under a separate finance package.We currently have forms that create individually numbered "Job Cards" in the Job Cards Table..we input the data and manually type into a Text Box the we have used 'X' number of Store Stock 'Item Y'
I have then use a query to export the data i need (Job number, dates, location and Store Stock Items).But I have to manually re-construct the data and items into separate lines in an excel sheet for a paste operation into the finance package (lots of typing of 16 digit stock codes)
What i want is to use a second table (linked via the "Job Card#" key).It will be called "Stock Items" which has a separate field for each of our stock items.the user then just opens second form while creating the job that immediately creates a new record.The user then simply has to put the number of each item used in the relevant text box on the form and close.
example of datasheet view
Job# __ ItemX __ ItemY
2417_____3_______7
Now, I have all of that working as I want, But i nee to be able to combine the data from each field into a single field (either in access or excel).What I want to see is a field containing 3x ItemX, 7x ItemY, etc
if there are none of one or more items, I don't want anything displayed for that item (blank space is ok)I have 30 or more stock items, so there will be 30 or more columns in the table. the actual data stored in the table is simply the quantity of each item used.
this data is used twofold,on export to Excel I can split the data into new cells and it's used on a despatch note that is returned with the item so the end user can see the items that were used in the repair.So it needs to be done upon saving the record, record by record.
View 5 Replies
View Related
Oct 11, 2006
Hi
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
View 2 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
Sep 26, 2006
I 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 Related
Jul 9, 2014
I 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.
View 4 Replies
View Related
Jul 17, 2013
I 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.
View 14 Replies
View Related
Jun 12, 2007
I just had to pull a large amount of data from an off-site SQL server. Due to speed issues, I broke up the data into 3 pulls and exported the data as text.
I have now imported the data into Access and wish to combine the three tables into one. All of the fields are the same, the only difference is that I broke up the pulls by date ranges.
Would this be done via an Update or Append query? AND, how would that query look? Sorry, still a bit new to this. :)
View 7 Replies
View Related
Nov 21, 2006
I 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!
View 1 Replies
View Related
Aug 16, 2013
My 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.
View 2 Replies
View Related
Nov 14, 2013
How i would best combine values in a table to produce a 'primary key id number.'
For example: the first letter of a city in the ID and the next number available/auto number - Portsmouth -> P233
I know i can create this in a query however i want it as the unique ID for that record entry in a table. If that doesn't make sense i can try to elaborate some more.
View 14 Replies
View Related
Dec 13, 2012
I have a database that uses a field for the year (but I'm using a fiscal year that will end June 30, 2013) and another field as an autonumber. I use the year and autonumber as my reference number (i.e 2013-0001). I'd like to be able to combine these fields to generate the entire number. Also, I'd like for the year to add 1 beginning July 1st of each calendar year, and the autonumber start over at 0001 (i.e. 2014-0001 on July 1, 2013). Is this possible, and if so, how can I do it?
View 4 Replies
View Related
Mar 15, 2007
Hi Everyone,
I hope I'm posting in the right place, I've been trying to solve my problem using queries so I thought it might be appropriate here.
I have a database for a health care service which contains among others the following four tables...
Table 1 contains client details, primary key [ClientID] plus other client details.
Table 2 contains Episode of care details, primary key [EpisodeofcareID], [episodeofcareDate] etc...
Table 3 contains all test results for assessment 1, primary key [CoreID], [CoreDate] plus numerous scores for individual tests.
Table 4 contains all test results for assessment 2, primary key [HonosID], [HonosDate] plus numerous scores for individual tests.
Table 1 is linked to Table 2, and Table 2 is linked to both Table 3 & 4.
I have set up a search procedure whereby the user enters a clients name which then opens a list box of all clients with that name. When the client selects from the listbox I want a second listbox to open which has three visible columns. One giving the date of each episode of care for the client, the second giving the date of each assessment completed and the third giving the name of the assessment carried out.
I'm managing to get the date of the Episode of Care to display on the first visible column but I'm getting stuck on the next two. This would seem to involve somehow combining data from different tables into individual columns on the listbox. For example I need the second visible column on this listbox to list all the dates of assessments for the chosen client from tables 3 & 4. (e.g. [CoreDate] and [HonosDate]). Column three would then give the title of the assessment next to the date in column two. None of the tables have a field listing titles as this is determined by which table the data is entered into. I've been trying to solve this by queries without much success.
Could anyone give some ideas as to how I could solve these problems?
Many Thanks
John
View 9 Replies
View Related
Jul 17, 2014
I have a database which is importing several Excel workbooks, each with multiple worksheets. Every workbook has 20 worksheets, with the same 20 worksheet names. When they are imported they come in as one table for each worksheet, named tblWorkSheetName_X with X starting at 1 and increasing for each worksheet brought in with the same name. So if the worksheet names are A-T I have tblA_1 through tblA_6 and likewise for B - T.
I would like to combine all of the tables which come from similar worksheets into one table (one table per name).
I.e. I want to combine the data in tblA_1 through tblA_6 into a singular tbl_A and likewise for tables B through T. So in the end I will have one table for each worksheet name A-T. t how to code this successfully?
View 14 Replies
View Related
Mar 17, 2007
REPOST from Design Area
Hello all,
I hope that the following explination of my problem in clear...
I have a table with a series of dates in individual fields: Day1, Day2, etc.
What I would like to do now is to combine those into another query/table where the dates are all in one field.
ie:
Original Table
[Day1] March 1
[Day2] March 2
[Day3] March 3
New Table
[Date] March 1, March 2, March 3.
Can you help me figure out how to accomplish this? I greatly appreciate your time and sharing of any thoughts that you have on this situation.
t
View 4 Replies
View Related
Feb 10, 2008
I need to combine data from a Yes/No field such that for any instance of a Yes/True the query will show Yes (checked). Only if there are no instances of Yes should the query return No (unchecked) for the field. In addition, this rule must apply for each individual account number in a [separate] table of accounts.
Tables: (only relevant fields shown)
SavingsClub - AccountNumber (PK), CustomerID, FlagAccountClosed, FlagDelete
SavingsClubAccounts - Account, Reservation, Redeemed (PK=another field)
SavingsClubReservations - Reservation (PK), FlagSpecialOrder
The following query works fine except that it returns two entries for John Doe if his account has entries for both FlagSpecialOrder=True and FlagSpecialOrder=False. You can see in the attached image that accounts 8 and 9 both have two entries each. Because these customers have a special order I want just one one row for each showing Yes (checked).
I want the query to return a single row for each unredeemed account. If there are no special orders then that field will be No (unchecked), but if there is at least one instance of a special order the field show be Yes (checked).
(To put this into some kind of context, customers can place orders which may be stock items or special orders, in which case I need to record the supplier, quotes, delivery dates, etc. In some cases they save for an item and there may or may not be a special order involved. Once the order is fulfilled it is redeemed.)
SELECT DISTINCT Customers.Name, SavingsClub.AccountNumber, SavingsClubReservations.FlagSpecialOrder,
Suppliers.ShortName, SavingsClub.FlagAccountClosed, SavingsClub.FlagDelete
FROM Suppliers
INNER JOIN (OrderDetails
INNER JOIN (((Customers
INNER JOIN SavingsClub ON Customers.Index = SavingsClub.CustomerID)
INNER JOIN SavingsClubAccounts ON SavingsClub.AccountNumber = SavingsClubAccounts.Account)
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation)
ON OrderDetails.Index = SavingsClubReservations.OrderID)
ON Suppliers.Index = OrderDetails.Supplier
WHERE (SavingsClubAccounts.Redeemed=False)
ORDER BY Customers.Name
I've spent a week trying to solve this and thought I was on the right tack with the following query, which does return a single instance of each relevant account, but I can't manage to combine it into the query above to obtain the complete query.
SELECT DISTINCT SavingsClubAccounts.Account FROM SavingsClubAccounts
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation
WHERE (SavingsClubReservations.FlagSpecialOrder=False) And (SavingsClubAccounts.Redeemed=False)
And SavingsClubAccounts.Account Not IN
(SELECT SavingsClubAccounts.Account FROM SavingsClubAccounts
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation
WHERE (SavingsClubReservations.FlagSpecialOrder=True) And (SavingsClubAccounts.Redeemed=False))
UNION
(SELECT SavingsClubAccounts.Account FROM SavingsClubAccounts
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation
WHERE (SavingsClubReservations.FlagSpecialOrder=True) And (SavingsClubAccounts.Redeemed=False));
Appreciative of any help, otherwise I think I'll end up manipilating the data in code and use a temp table, etc...
View 2 Replies
View Related
Jun 29, 2005
I have a table with all repair history in it. Unfortunately, the entry system is such that people can initiate a new record without typing in the correct customer name. So if my customer was "Fry's" it could be entered into the system as "FRYS" "Frys electronics"... etc. You wouldn't believe the ways people enter the same name. Anway, the only thing that links any of these records together is the item serial number. Though one serial number could be under a different name than the other.
Does anyone have any ideas as to how to create a temp table which associates related serial numbers to a single customer name? Even if the user had to select the customer names he want's associated that would be fine.
Note: I should mention that no there is no way to get this changed on the back end. I'm stuck with the tables I have.
Thanks.
View 2 Replies
View Related
