I'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 Count
238 CHSTNRH 7
When 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];
I have a database and i have one field call fdr. that field is full of data but i realized it wood be much more capable to do what i need if i use a joined table with every single fdr. so i created a table with fdr and a id column and set primary key as id. I filled in every single fdr into the table but now I need to replace the original table with the id and then have the fdr # from the new table show up.
Does anyone know how to create the update query and create the proper relationship for what i need to do?
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 two tables containing (let's say for simplicity) questions and attachments (pictures). I am trying to perform a union query to join all the questions and pictures into one report, but it won't let me union the attachment because 'the multi-valued field 'TableA.Pictures' cannot be used in a union query'.
I have searched and searched for a solution (and got kind of close) but i still can't get it to work. The best I can do is union everything like below, which gives all the questions as desired, but says #Error in the pictures column:
SELECT TableA.* FROM TableA Union SELECT TableB.* FROM TableB;
(Note tables A and B have the same structure, several yes/no and open text questions as well as one attachment field. )
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?
>>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... :(
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:
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.
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"
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:
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.
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
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.
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)
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.
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
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.
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..
I have a table that looks like this: Code:ID Station DateTime Value 1 ABQ 10/8/2005 1:00 am 102 ABQ 10/8/2005 2:00 am 203 ABQ 10/8/2005 3:00 am 254 ABQ 10/8/2005 4:00 am 5... I am trying to build a query that, for each record, gives me the values for the previous 2 hours.
So my goal would be a query that returns: Code:ID Station DateTime Value ValueT-1 ValueT-21 ABQ 10/8/2005 1:00 am 10 Null Null2 ABQ 10/8/2005 2:00 am 20 10 Null3 ABQ 10/8/2005 3:00 am 25 20 104 ABQ 10/8/2005 4:00 am 5 25 20...
I created a table (NewTable) that, for each record, has the DateTime, DateTimeT-1, and DateTimeT-2. So columns in Newtable are ID, Station, DateTime, Value, DateTimeT-1, DateTimeT-2 where DateTimeT-1 = dateadd("h",-1,DateTime) and DateTimeT-2 = dateadd("h",-2,DateTime).
I then joined (using a left join) this new table to the original table on [NewTable].[DateTimeT-1] = [OldTable].[DateTimeT]. so code is Code:select [NewTable].ID, [NewTable].Station, [NewTable].DateTime, [NewTable].Value, [OldTable].DateTime, [OldTable].Value AS ValueT-1from NewTable left join ON [NewTable].[DateTimeT-1] = [OldTable].[DateTimeT]
The output does not pull all the right records. Some of the records have Null values for ValueT-1 when the datetimeT-1 is in OldTable. So for some reason the join doesn't seem to be working.
Hopefully this is clear. Any thoughts? Help is very much appreciated!
