Hi...Well I have 2 table with same characteristic [ Id_no(Autonumber (primary key)), Name(text), Address(text)]...is there a way I can combine this 2 table into one...is there any issue regarding the uniqueness of the primary key..
View RepliesHey guys I have a problem on creating the append-query and the new table
I want to join couple different fields from 2 COMPLETELY different table with TOTALLY DIFFERENT fields.
>>What I have right now are the following:
Table 1
Fields: meta_theme, theme, sub_theme, root, count
Table 2
Fields: meta_AG, AG, AG_description
>>What I want at the end:
A new Table 3 that contains the following information AND is a form, which when you open will have :
1.meta_theme: automatically populated with the records from table 1
2.theme: automatically populated with the records from table 2
3.AG: This column is to be filled when someone run the form, and it will be a combo box with value from the records in Table 2-AG field
4. Comments - completely new
>>could you guys help? My brain is totally in disfunction mode... :(
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.
I'm new to access. Basically I've put this formula in a field within my query(Query A):
PERIOD: [YEAR] & "-" & [Month Number]
And I want to use this newly created field "Period" to join another table (Table A) without having to create another query.
PERIOD(Query A) -> PERIOD(Table A) = Month Name (Table A)
I need the month Name from Table A but because Period(Query A) is a formula I created, I don't know any way of joining it to Table A without creating another query. Is there another way...
I have tried to combine data from 3 different tables (unrelated) to make a new one. However, as I understood I can not do this because these 3 tables are unrelated. In my case my 3 tables are for 3 labs' material requirements. I am planning that each lab will fill a form which directly related to there field and direct to me. That is why I intend to have separate tables for each. However, I want to create a table where I will be able to see all the requirements of different Labs altogether. This new table will be kind of Orders.
View 7 Replies View RelatedAll; using 2010. I have a table that I need to update some data from another table. I want to use the SSN but one of the SSN fields in the table has letters at the end of it and doesnt return any records. How can I join fields?
View 1 Replies View RelatedI'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
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.?
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
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
Hi Guys,
I have a problem which I cannot figure out.
I have a table which has an ID field and also a field with data. The ID field is 'not' unique.
I need to join all of the data fields which have the same ID field into one field. So it would be ID; Data 1, Data 2...
Any ideas? I would appreciate any help you could offer!
Seems like a simple question but I am stuck.
I need to join two strings from different records in a table
table eg
ID Desc
1 blah blah
1 more blah
2 blahring
2 blah
3 and last one
I want to join all the "Desc" strings that have the same ID to get
1 blah blah more blah
2 blahring blah
3 and last one
Can anyone help?? :o
Thanks
Paul
I'm trying to create a Purchase Order Database that requires a monthly summary of all the purchase orders in that month.
Examples of the fields i'm using are: Date, Vendor, Description, Item Number, Unit Price, Quantity, Sub-total, Total, etc.
Each table is a single purchase order, and there are around 3 or so every month. What I am trying to do is create a query in which all related fields are joined, and can be viewed as a single field.
For example:
Field 1 is DATE for Purchase Order (PO) 1, 2, and 3.
What I want to do is join all entries in DATE from PO1, PO2, and PO3 in a single query.
I want it to look like:
[DATE]
PO1......
PO2......
PO3......
I've tried several approaches to this problem, but they all have the same end result. It creates a seperate field for each table:
[PO1-DATE][PO2-DATE][PO3-DATE]
Any suggestions?
I have two tables. One stores details of all money travelling from A to B, one from B to A.
I have created queries 'qry_A_to_B' and 'qry_B_to_A' to get each set of information.
Since there are User IDs in each table that don't appear in the other, I'm using the following two queries to return all the TO and FROM values. By using both LEFT joins, I appear to be getting all entries.
'A to B data
'------------
SELECT
[qry_A_to_B Yearly].ABUserID,
[qry_A to B Yearly].ABMonth_of_Year,
[qry_A to B Yearly].ABYearly_Sum,
[qry_A to B Yearly].ABMonthly_Sum,
[qry_A to B].ABMonth
FROM
[qry_A to B Yearly]
LEFT JOIN [qry_B to A]
ON [qry_A to B Yearly].[ABUserID] = [qry_B to A Yearly].BAUserID
GROUP BY
[qry_A to B Yearly].ABUserID,
[qry_A to B Yearly].ABMonth_of_Year,
[qry_A to B Yearly].ABYearly_Sum,
[qry_A to B Yearly].ABMonthly_Sum,
[qry_A to B].ABMonth;
'B to A data
'------------
SELECT
[qry_B to A Yearly].BAUserID,
[qry_B to A Yearly].BAMonth_of_Year,
[qry_B to A Yearly].BAYearly_Sum,
[qry_B to A Yearly].BAMonthly_Sum,
[qry_B to A].BAMonth
FROM
[qry_B to A Yearly]
LEFT JOIN [qry_A to B]
ON [qry_A to B Yearly].[ABUserID] = [qry_B to A Yearly].BAUserID
GROUP BY
[qry_B to A Yearly].BAUserID,
[qry_B to A Yearly].BAMonth_of_Year,
[qry_B to A Yearly].BAYearly_Sum,
[qry_B to A Yearly].BAMonthly_Sum,
[qry_B to A].BAMonth;
How can I join these two, to provide a list of ALL information, from all ten columns, regardless of whether or not someone has both a TO and FROM value? i.e.
If they only have a TO entry, I want to see it; the other five columns would be blank.
If they only have a FROM entry I want to see it; the other five columns would be blank.
If they have both, I want them to line up on one line.
However I try to phrase the query, I seem to miss at least some of the information from one or both tables.
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 RelatedThis gets even more confusing as I go.
Two tables again needing to be joined. But only need to fill the data in the first line.... yes, Pain in the ARSE> here's a sample.
PAYLINES TABLE
EMPLOYEE --------- DATE -------- Paytype
121614000 -------- 12/31/2007 --- 300
121614000 -------- 12/31/2007 --- 122
121614000 -------- 12/31/2007 --- 100
121614000 -------- 12/23/2007 --- 100
TAXTABLE
EMPLOYEE --------- DATE -------- CPP ---- EI
121614000 -------- 12/31/2007 --- 1.23 --- 2.35
121614000 -------- 12/23/2007 --- 2.32 --- 1.35
The end Query I need is
EMPLOYEE --------- DATE -------- Paytype --- CPP ---- EI
121614000 -------- 12/31/2007 --- 300 ------- 1.23 ---- 2.35
121614000 -------- 12/31/2007 --- 122 ------- NULL ---- NULL
121614000 -------- 12/31/2007 --- 100 ------- NULL ---- NULL
121614000 -------- 12/23/2007 --- 100 ------- 2.32 ---- 1.35
The TAXTABLE only needs to assign it's data to the FIRST row of the resulting query
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!
I have two tables in my database. one is linked to a Client.txt and the other is tblNewClient.
the idea is that this database is a portable version of the actual database, meaning that it will be operating on its own without a connection to the actual back end.
Now the problem is when a new customer is added, i want it to add itself to the tblNewClient.
but when i go into my Find CUSTOMERS FORM i need to be able to search threw both tables.
i want to creat somthing like a union query but in vba.
I figure i need to have 2 recordsets in vba. one for each table and then i need to have a 3rd recordset wich will hold all the info.
im not sure how to copy a whole recordset and set to another table.
Im thinking of somthing like
set RsAll = RsClient & RsNewClient
This obviously does not work but it is what i want to accomplish ,and i want the Rsall to be an actual table and not a public Recordset
I hope the title isn't misleading. I just need some advise. I'm trying to bend my mind around how to accomplish this.
I'm putting together a database for a trucking company. The employees are paid according to the loads they haul. I have a setup right now where the manager can put in all the information (including driver, $$ rate, date, etc), and the invoice will be calculated and created in a report. Further, it also creates paystubs for each driver. This is where I am having trouble. You see, I can use a query and detail section in a report to add up the amount earned for each driver from each load. No problem there. My problem is that deductions are made to these paychecks for various things which change every week. I haven't quite figured out yet what the best solution would be for inputting this deduction information and then joining it to the payment information.
I hope this is somewhat clear what I am doing. If not, please let me know and I'll clarify. I can try to strip a database, but it still might be too big.
Thanks in advance for any help.
-opiv6ix
PS - If anyone has an eloquent way of choosing a pay period, I'd love to know about it. Right now I'm building the report off of undefined fields [Startdate] and [Enddate] in the query, prompting the user when s/he opens the report.
Hi whats the syntax for left joining more than 2 tables if each of the tables has a common column???
View 4 Replies View RelatedI 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)
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
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
TABLE 1(x)RN|Flat | Init 238NKCR1243238NKCR1243238NKCR1243238NKCR1243238NKCR1243238NKCR1243238NKCR1243238NKCR1246TABLE 2(xx)RN|Eq |Nr|Rpt 21RSFLC1055SHFLDML 238NKCR1243CHSTNRH238NKCR1246CHSTNRHI'm trying to make a query that Joins FLat to Eq and Init to Nr. The reason I am making this join is to COUNT all instances of Init, so it should count 7. Then I want that 7 to correspond to the Rpt CHSTNRH in table 2. So my finalized query should look like...RN Rpt Count238 CHSTNRH 7When I create a Join in a SELECT query it says "Type Mismatch expression". Is there a special query I need to use? Here is my SQL.SELECT x.RN, Count(x.[Init]), xx.[Rpt]FROM x INNER JOIN xx ON (x.[Init] = xx.[Nr]) AND (x.[Flat] = xx.[Eq])GROUP BY x.RN, xx.[Rpt];
View 6 Replies View RelatedHi all,
In a table, I want to join the texts in two fields to a new field by a backslash.
eg. Canon(Field1) and Digital Cameras(Field2) = Canon/Digital Cameras(Field3).
Can anyone please help me with a solution?
Cheers
Bud
If the question is not clear please refer to the excel sheet.
is it possible to join these two queries together, so that the 2nd query appears at the bottom of the first?
Code:SELECT OEEModelMCTotals.Machine, OEEModelMCTotals.EventCode, OEEModelMCTotals.CodeDescription, OEEModelMCTotals.SumOfTotalTime, OEEModelMCTotals.Occur, OEEModelMCTotals.PlannedTime, [OEEModelMCTotals]![SumOfTotalTime]/[OEEModelMCTotals]![PlannedTime] AS [%Schedule], [OEEModelMCTotals]![SumOfTotalTime]/[OEEModelMCTotals]![Occur] AS Avg, OEEModelMCTotals.Output, OEEModelMCTotals.Throughput, OEEModelMCTotals.ValueAddedimeFROM OEEModelMCTotalsWHERE (((OEEModelMCTotals.EventCode) Not Like "n204" And (OEEModelMCTotals.EventCode)<>"n301" And (OEEModelMCTotals.EventCode)<>"n303" And (OEEModelMCTotals.EventCode)<>"x104" And (OEEModelMCTotals.EventCode)<>"x117"));
and
Code:SELECT OEEModelOtherTotals.EventCode, OEEModelOtherTotals.CodeDescription, OEEModelOtherTotals.Machine, Sum(OEEModelOtherTotals.TotalTime) AS SumOfTotalTime, Sum(OEEModelOtherTotals.Occur) AS SumOfOccurFROM OEEModelOtherTotalsGROUP BY OEEModelOtherTotals.EventCode, OEEModelOtherTotals.CodeDescription, OEEModelOtherTotals.Machine;
i have attached a spreadsheet with the outcome i am after....i dont want it in excel but have used this for my demonstration.
you will see at the bottom of the sheet i have highlighted the info added in blue.
can this be achieved ?
if so how please.
cheers
Andy