Joining Tables, Group By Name, Select On...
Nov 18, 2006
Hi I have 2 tables(tblA and tblB) and I want a query result(tblC) where ‘group by’ only one record per individual, with the total added together and the type starts with ‘01’. Below(tblA and tblB) are examples of the two table. TblC is the query result that I want.
tblA
Name---Type---Amount---Status
Joe---01FAE---$10---New
Joe---01FBE---$10---New
Lee---01FBE---$10---Old
Bob---30FBE---$30---New
tblB
Name---Type----Amount---Status
Joe----01FBE---$5------New
Lee----01NAE---$20-------Old
Lee----30FBE--$30------New
tblC
Name---Type---Amount
Joe---01FAE---$25---New
Lee---01FBE--$30---Old
I have tried linking/joining, adding SUM function, First, Group By, etc. but all the results came out to be all different. Also, am I support to add a ‘join’ of tblA to tblB on BOTH the NAME and TYPE? I am so confused! Please help.
Thank you very much.
Joe
View Replies
ADVERTISEMENT
Apr 19, 2007
I have some experience doing 'Update Query' using two different tables but I'm having a hard time doing an 'Update Query' using 3 tables.
I have my source table TP05XY with the fields 'Mark' 'Date' 'UTM_Edig' and 'UTM_Ndig'. Mark and Date are my primary keys (they together uniquely ID each record). I have my Observations table with the fields 'Mark' 'Date' and 'Obs_ID'. The last table is Locations with 'Obs_ID' 'UTM_E' and 'UTM_N'.
I want to update my fields UTM_E and UTM_N from UTM_Edig and UTM_Ndig. However, to do so, I have to go from my TP05XY table, through Observations table to update Locations table. Table TP05XY is joined to Observations through 'Mark' and 'Date' and Observations table is linked to Locations through 'Obs_ID' field.
I have tried a few options without success ... anyone knows how to do it?
Thanks,
Josée
View 1 Replies
View Related
Oct 27, 2014
I have a db with 2 tables one with 10 fields and the other with 11 fields. In these tables there are 4 and 5 non common fields in tables respectively.
How can I join these tables in a 3rd table with all the fields from both tables.?
View 1 Replies
View Related
Oct 25, 2006
Hi
I have an Access database that contains 2 tables. Both of these tables have the same structure and have the same field names. So:
Table 1 Fields :
Name
Address
Telephone
Fax
Table 2 Fields :
Name
Address
Telephone
Fax
Is there a way to write a query, that will show the results of both tables in one go. (None of the information in table 1 is duplicated in table 2 - I want to show all records from both tables in one new table). So, if both tables had 3 records each, the query would return:
Name - Address - Telephone - Fax
jon-uk-12345-54321 (from Table 1)
julie-uk-21451-41541 (from Table 1)
paul-ir-98545-11241 (from Table 1)
pat-uk-99585-63362 (from Table 2)
phil-uk-99985-99631 (from Table 2)
ted-uk-44444-55555 (from Table 2)
These 2 tables need to be kept separate, so I can't copy and paste the records from Table 1 into the bottom of table 2.
The field names are the same in both tables.
Can this be done?
Many Thanks
View 1 Replies
View Related
Jan 2, 2007
Hi All!
I have two tables. Table Heffalump which has field "DealGroup" and other fields as well... and table 8-YTD Principal Investments whic has field "book" and other fields as well... I need to use BOOK and DEAL Group as a combination primary key to compare against table 8-YTD Principal Investments. If there is a new DEAL GROUP then append into table "8 - YTD Principal Investments"
THANKS IN ADVANCE,
B
View 2 Replies
View Related
Feb 16, 2007
I am building a simple task tracker to keep a record of tasks being passed back and forth between our team. The schema is fairly simple as illustrated in the image below.http://img2.freeimagehosting.net/uploads/f4f3a05c21.gifI want a recordset as follows out of this database (+/- few columns. But these are the most essential ones needed) -ixProject | Project.sTitle | ixTask | Task.sTitle | TaskHistory.dtEntry | sStatus | sEmployeeAssignor | sEmployeeAssigneeThis is basically a recordset of the history of the task (who assigned task to whom) and the status (assigned, resolved, closed). I can get the query to return the user id (ixEmployee) without any problems. But to display this in the interface I will need the employees name (sUsername).I tried the following query -SELECT Employee.sUsername AS sEmployeeAssignor, Employee.sUsername AS sEmployeeAssignee, Project.sTitle AS sProjectTitle, Task.sTitle, TaskHistory.dtEntry, TaskHistory.sDescription FROM(((Project INNER JOIN Task ON Project.ixProject = Task.ixProject) INNER JOIN (Employee INNER JOIN TaskHistory ON Employee.ixEmployee = TaskHistory.ixEmployeeAssignor)ON Task.ixTask = TaskHistory.ixTask)INNER JOIN Employee ON Employee.ixEmployee = TaskHistory.ixEmployeeAssignee <<< THIS IS THE PROBLEM LINE)WHERE (SELECT LAST(TaskHistory.ixEmployeeAssignee) FROM TaskHistory) = 1ORDER BY TaskHistory.ixTask, TaskHistory.ixTaskHistoryThis is the error I get -Join expression not supported.State:S1000,Native:-3530,Origin:[Microsoft][ODBC Microsoft Access Driver]Can someone explain how I can solve this problem? I am currently running two queries - one to retrieve the usernames, and the other to retrieve the history with the user id's - and replacing the user id's with usernames when displaying the records, but would like to have a cleaner solution.
View 2 Replies
View Related
Jan 30, 2008
Hello! I need to join 2 tables, 1 table with all of the fields, and another with just 2.
The link between the 2 tables is an ID field in field 2 wich is long integer.
The problem is, the ID field in table 1 that correlates to the ID field in table 2, is in the datatype text.
I cant change the datatype of the field in table 1 because it is a linked table to a txt file. And some of the values in there are dashes, indicating no ID. (not sure why).
So, is there ANY way to link the2 tables with the 2 different datatypes
Thanks in advance!
View 5 Replies
View Related
Dec 15, 2005
Hi whats the syntax for left joining more than 2 tables if each of the tables has a common column???
View 4 Replies
View Related
Feb 10, 2006
I need to do a join on two tables, one is in the current database, but the onther one is an another database. I am struggling with the syntax. Can anyone advise where I am going wrong?
This is the code, but it says the usual 'Syntax error in FROM clause'
SELECT za06_report_2006.wbs, za06_report_2006.cost_cntr, wbslist.description
FROM za06_report_2006 LEFT JOIN wbslist in '\ukdewgs002gdatapcdatafinancedocumentwbslis t.mdb' on za06_report_2006.wbs= wbslist.[wbs element];
All table names , variable names, and paths are correct.
Thanks for any suggestions
(I am very new to SQL)
View 1 Replies
View Related
Oct 25, 2006
Hey everyone. I have this situation: I work for a veterinary company and I have a project which includes 4 tables.The first one has a list of all of our clients.The other 3 are : a table for the year 2004, one for 2005 and one for 2006. Not every client visits us every year and I need a query which will make certain action (different sorts of calculations) with the number of animals that we take care of.
To make it more clear:
During 2004 one client with ID 12345 visited us with 3 animals.
He didn't show up in 2005 but he came back in 2006 with 4 animals.
Trying to do so I joined the tables properly (in my opinion one-to many) and I only get the sum if a client has visited us for all the 3 years. If he appears in just two tables or one I don't get a result.
This is the problem. Has anyone got a clue?
Thanks in advance
Kyriakos
View 3 Replies
View Related
Feb 10, 2007
Hi all,I am having problems joining two tables together.
Table1
ID Name
1 A
2 B
3 C
Table2
ID name
1 D
2 E
3 F
I would like to make the query same
ID name
1 A
1 D
2 B
2 E
3 C
3 F
Can anyone help please? It would be much appreciated.
Many Thanks, mimic
View 2 Replies
View Related
Nov 21, 2006
I am new to access and am having some problems. Here is what I have and what I want to do. I have one table that contains the ID number for a particular facility, facility name, and the sample location id number(s). Sometimes there is only one sample location id number and sometimes there are multiple. I have another table that has results of samples taken - it includes the facility id number, sample location id number, and the corresponding sample result. What I would like to do is join the data in the two tables so I have one table that shows takes the sample results from table 2 and combines it with the information in table 1.
Any help is appreciated
View 1 Replies
View Related
May 7, 2014
simple join my Main Data Compiling Table and Secondary Table Delete from Main Data table (Located Under main Content Tables). Basically I want it to run like a flat data table which I cannot do because I have too many fields. I want ability to further expand it in the future.Somehow I need a one to one relationship with Referential Integrity enforced with cascade Update Related fields and Cascade Delete Related fields. Problem lies in it tries to create a one too many relationship.Both Main1ID and Main2ID are AutoNumber with Main One being primary key.
I have created a tabbed Navigation Form to show what is currently happening the first Tab is from Client Demographic Form generated by fields from Main Data Compiling Table and Second from Floral Information Form generated by fields from Secondary Table Delete From Main Data Table.
View 3 Replies
View Related
Oct 30, 2004
How do I join 3 tables in Access using INNER JOIN query. I have a product tabel which is related to a subcategory table, which in turn is related to category table. I want to display the product along with the main category (not the sub category) it belongs to. My "SELECT * FROM products p INNER JOIN subcategory s ON p.subcatID = s.subcatID INNER JOIN category c ON s.catID = c.catID" query is returning error in ASP.
Could someone please help?
View 2 Replies
View Related
Dec 5, 2004
Hi,
This is my first question... please guide me
I have two tables say Daily and Master, and now I want to
update the Daily table and add two additional fields in this table
based on the data from the Master table.
Please suggest me..
View 10 Replies
View Related
Sep 18, 2015
How would I go about joining tables with similar fields? I currently have three tables that show standings of teams (East, Central, and West). They all have the same fields:
East
Team Name
Wins
Losses
Ties
Points For
Points Against
Central
Team Name
Wins
Losses
Ties
Points For
Points Against
West
Team Name
Wins
Losses
Ties
Points For
Points Against
How would I combine them in a query so that all of the data from the tables are compiled into one table:
Overall Standings (East/Central/West)
Team Name
Wins
Losses
Ties
Points For
Points Against
View 1 Replies
View Related
Feb 3, 2014
I have a number of tables with different metrics. However, they all have a "Date" field in common, broken out at the daily level. Since some of my tables have different start and end dates, I'd like missing values to simply appear as "N/A". How I would do this with SQL?
I've attached a screenshot of the layout I'm going for. Each metric field (i.e. "Metric1", etc.) would be pulled in from a different table.
View 5 Replies
View Related
Jul 28, 2014
Searching for this information is difficult, because if it has a name I am wholly unaware of what to call this.
Say I have two tables.
Table1:
'Serial Number', 'Product Name', 'Size', 'Weight', etc.
Now I need to add a new table that I have to start inputting storage locations.
Table2:
'Serial Number', 'Warehouse', 'Bin Number'
I need to keep these separate, because the first table is also a transaction log.I created a query that pulls:'Serial Number' From Table1 and 'Warehouse' , 'Bin Number' from Table2. I also joined the tables by 'Serial Number' with Include All from Table1.
I made a form that gets all of the information from the query and adds combo boxes to allow users to enter data with less error. But the 'Serial Number' field of Table2 is never populated. Is there a better way? I do want to say that I have somehow accomplished this before but I can't figure out how I did it before.
I would like to have Table2 auto populate one row at a time as I enter locations into the form. I'm dealing with half a million records in Table1 and not all 'Serial Number' are valid/exist now.
View 3 Replies
View Related
Oct 23, 2013
I have 5 years of data in tables that I need to arrange into 1 table.
The format I seek is the following:
Center # Identifier of what is being calculated Jan 09 Feb 09 Mar 09 etc
View 2 Replies
View Related
Feb 17, 2006
Let's say that a table has been broken in two and I'd like to rejoin them with a query. Let's say 100 records in table1, 20 records in table 2. Here's a random example, I just made the names up off the top of my head:
PersonTable (100 records)
Person # (unique key)
Name
Address
SpouseTable (20 records)
Person # (lookup wizard to Table1, cascading edit/delete relationship, 1 to 1)
WeightOfSpouse
HeightOfSpouse
Many people aren't "married" and so have no corresponding value in the Spouse table. If I do an inner join on the two tables, on Person#, then it will cut the total # or returned records in the query to 20, to match the total in SpouseTable. Essentially, I want to append the results of SpouseTable to the end of the corresponding rows in PersonTable.
View 1 Replies
View Related
Jan 5, 2005
(I know I posted this question before, but I fear it may have gotten lost in a thread so I am starting a new one)
I have created a multi-field pkey with the ModelDescription and VersionNumber fields. I did it the way that didn't require code as I could not find any online and am not versed enough in VB to do it on my own. I just highlighted both the fields and pressed the "Key" button.
My question is linking the tables. Should I just join them via the ModelDescription field? It won't let me join on both fields. Is it going to matter which field (ModelDescription vs. VersionNumber) I use to join the tables? I've never run into the need to have a multi-field pkey before.
View 1 Replies
View Related
May 31, 2013
I have a query joining two tables. TableInvoice in the query is Product number and Date purchased. TablePrices contains the same fields and contains prices (quarterly) over the past five years.
Joined by PONumber, I want the query to find the price charged for that date. Right now, if the Product number (TableInvoice) is not listed in TablePrices, it won't show. So, 100 Records might return only 80 if TablePrices does not have all of the Product numbers.
Is there a better way to return all 100 records in TableInvoice and show (Blank) price data if the item is not in TablePrices?
View 1 Replies
View Related
Jun 4, 2014
I have 2 tables: one for repairs and the other for the billing for those repairs. There is a foreign key(record_num) in the billing table to match the primary key(prikey) in the repairs table. This works fine as long as the unit repair has been completed.
Now an employee wants to see records even if they are not completed and wants the rate to be $0.00 if the unit has not been completed. But by this method there is no record in the billing table.
My problem is if I have the 2 tables joined then I only see records that match both tables. Here is my SQL for the query:
SELECT DISTINCTROW tbl_module_repairs.end_user, tbl_module_repairs.pickup_date, tbl_module_repairs.complete_date, IIf([pickup_entity]="Storm","APS Storm","APS Field Tech") AS [Repair Pickup], tbl_module_repairs.mfg_part_num, tbl_module_repairs.manufacturer, tbl_module_repairs.module_type, tbl_module_repairs.incoming_module_sn,
[Code] ....
View 2 Replies
View Related
Apr 2, 2013
I am trying to use a join query to join two tables in order to create a form. I have done this on two other occasions in my database with no issues. Now it is only pulling the ID and I want it to pull the description. I have looked at the SQL view for the other queries that are similar as well as the design view and all of them are set up the same way with the exception that this new join query is pulling CertID rather than the CertDescription.
Is there any reason why it would be doing that? Is there an error that I could possibly be doing? It seems according to my notes that I have created all the queries the same but this last one does not pull the same info as the other queries.
View 2 Replies
View Related
Jun 29, 2013
I've got a query that uses a key from one table to pull back records from another.
My question is...
On the table with the key there is another field that I want to use to pull back data from the same table from above.
A bit more info...
Table one holds colleagues details
Table two holds records
Within table one there are 2 different fields that hold different ID's for the same colleague, currently I run two different queries to pull all data - I want to know if I can simply run one query that will return every thing?
Schedule an import or export operation
View 11 Replies
View Related
Jan 17, 2007
SELECT contractid, min(RTRbalance) AS minRB
FROM ACHACT
GROUP BY contractid;
will have two culumn
What if I want to select all columns of ACHACT associated with minEB in each contractid group?
THX.
Jeff
View 1 Replies
View Related
