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..
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.
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
Dear all,
I would like to seek your help on this problem.
I would like to do a search for my records based on 2 different criterias. If found, the function should just return a boolean value so that I can act further based on the boolean value.
The reason I'm doing this is that I have 2 similar tables that contains EventID and AttendeeID and these 2 tables cannot have the same EventID and AttendeeID. So before I can allow a record to be inserted in the 1st table, it must check that there is no record in the 2nd table that has the same EventID and AttendeID. If there is, then i cannot allow the record to be inserted in the 1st table.
The same thing applies when I want to insert a record in the 2nd table. It must now check the first table.
Recordset.find seems to be only catering to 1 criteria, not 2.
Pls help. Codes are most welcome too.
Ok. Here's the situation. I have query setup. This query has 2 columns:
1. Agent Name
2. Sale Value
I have 2 tables.
Table1: Includes Sales for this pay period only
Table2: Includes all Sales From past to present.
We are setting up our system on Tiers and Each sale value is going to change. So, Here's what I need:
I need to run an update query that first Looks up all the records in Table1 based on Agent Name. The field that needs to be updated is called SaleValue. It needs to be updated with what's in my Query mentioned above.
Then, all the values in Table1, need to be copied (or updated) into Table2. This can be done based on the Primary Key (SaleID), which exists in both tables. So I guess we will need to run 2 separate update queries. Any ideas on how this can be done?
I have two tables that will contain the information that I need the third table that will be used for constant data entry to auto fill in two of the fields (Class, Rate)based on four criteria from the first two.
Table 1
EmpID Name
1 EMP1
2 EMP2
Table 2 (A Subtable of Table1 based on EmpID)
EmpID ACDate Class Rate MJob SJob
1 1/6/13 A 15 100 1A
1 2/6/13 B 20 100 1A
2 1/6/13 A 18 100 1B
Table 3
Name WDate MJob SJob Class Rate
EMP1 1/7/13 100 1A A 15
EMP1 2/5/13 100 1A A 15
EMP1 2/6/13 100 1A B 20
EMP2 1/6/13 100 1B A 18
I have two tables linked by ID (Table1,Table2)
The Table1 holds data that is a request for a task.
ID, Request, Task_tms (the number of times the task is required to be done)
1, Make a cup of coffee,15
Table2 is my allocation of people to the tasks.
ID, Person, Task_done (the number of times this person has done the task)
1,Sally,10
1,Eddie,5
What I want to do is to limit the amounts of allocation in some way (ie in this case if I have already put 10 for Sally then I would be unable to put anymore than 5 when entering the amount for Eddie.
Additionally if I were to allocate all 15 to Sally then no additional people would be able to be allocated to this task (ID 1)
The two table are in datasheet format linked on a form.
I want the lookup to be performed with criteria based off of the same row that record being selected is on.
I wrote typed out the Columns and rows that my queries return, and attached a screenshot as well.
You enter your phone number which provides you with an indexed location. Based off of that location only certain products are available and I want them to be filtered accordingly. I have it working to some extent, here is an example below:
|Site Name|Phone Number| Location |Products Available|
| Store 1 | 5044645521| KNNRLABR
The lookup displays products available where the indexed products match the same location. Here is what the query returns:
| Location |Products Available|
| KNNRLABR| Product 21 |
| KNNRLABR| Product 9|
| KNNRLABR| Product 7 |
| KNNRLABR| Product 3 |
This is the affect that I want to achieve but, if I add another location like so:
|Site Name|Phone Number| Location |Products Available|
| Store 1 | 5044645521 | KNNRLABR |
| Store 2 | 3609061624 | VANCWA01|
The lookup returns all products available for all the records for each location entered rather than based off of the selected record.
| Location |Products Available|
| KNNRLABR | Product 21 |
| KNNRLABR | Product 9 |
| KNNRLABR | Product 7 |
| KNNRLABR | Product 3 |
| VANCWA 01 | Product 31 |
| VANCWA 01 | Product 8 |
| VANCWA 01 | Product 4 |
| VANCWA 01 | Product 3 |
| VANCWA 01 | Product 1 |
Is there a way I can make the lookup run off of the location specific to the current row that I am working on?
P.S. Here is a copy of the SQL statement I'm using right now.
SELECT DISTINCTROW [DSL Product CLLI's].CLLI, [DSL Product CLLI's].Speed, [DSL Product CLLI's].CLLI
FROM [DSL Product CLLI's], [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet]
WHERE ((([DSL Product CLLI's].CLLI) In (SELECT DISTINCTROW [Location] FROM [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet] WHERE [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet].[Location]=[DSL Product CLLI's].[CLLI])));
Which Returns the Following in my lookup field for Products Available.
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
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 RelatedHello! 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!
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
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
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.
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. tblAName---Type---Amount---StatusJoe---01FAE---$10---NewJoe---01FBE---$10---NewLee---01FBE---$10---OldBob---30FBE---$30---NewtblBName---Type----Amount---StatusJoe----01FBE---$5------NewLee----01NAE---$20-------OldLee----30FBE--$30------NewtblCName---Type---AmountJoe---01FAE---$25---NewLee---01FBE--$30---OldI 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 1 Replies View RelatedHow 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?
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
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.
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
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 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.