Help In MS Access Query
Sep 22, 2007
Hi,
i need help on one of my issues.
i am working in MS Access.
i created first query as :
QueryABC:=
SELECT DISTINCT [BOT Proforma].[Jan_Net Sales] AS restated
FROM [BOT Proforma], GBMPPL
WHERE (([BOT Proforma].[Product Name]='Reflective') And ([BOT Proforma].[Country]='China') And
([BOT Proforma].Year=2006));
i have second query as :
SELECT DISTINCT [BOT Proforma].[Jan_Net Sales], [BOT Proforma].[Jan_Fact Cost],"i want to include the output of QueryABC in here"
From [BOT Proforma],[GBMPPL]
WHERE (([BOT Proforma].[Product Name]='Reflective') And (GBMPPL.[Sold By Country]='China') And (GBMPPL.[Specified by Country]='Canada') And ([BOT Proforma].Country='Canada') And ([BOT Proforma].Year=2006));
Kindly help.
Thanks & Regards,
Regards,
Sunaina Koul
View 1 Replies
ADVERTISEMENT
Apr 18, 2007
Hi,
I have an application that uses following code:
Code Snippet
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
namespace TimeTracking.DB
{
public class sql
{
OleDbConnection conn;
//
//the constructor for this class, set the connectionstring
//
public sql()
{
DBConnectionstring ConnectToDB = new DBConnectionstring();
conn = ConnectToDB.MyConnection();
}
//
//
//
public void UpdateEntry(int ID, string Week, string Year, string Date, string Project, string Action, string Time, string Comment)
{
int m_ID = ID;
int m_Week = (Convert.ToInt32(Week));
int m_Year = (Convert.ToInt32(Year));
string m_Date = Date;
string m_Project = Project;
int m_ProjectID = new int();
string m_Action = Action;
int m_ActionID = new int();
Single m_Time = (Convert.ToSingle(Time));
string m_Comment = Comment;
//
//get the project ID from the database and store it in m_ProjectID
//
OleDbCommand SelectProjectID = new OleDbCommand("SELECT tblProject.ProjectID FROM tblProject"
+ " WHERE (((tblProject.Project) LIKE @Project))", conn);
SelectProjectID.Parameters.AddWithValue("@Project", m_Project);
try
{
//open the connection
conn.Open();
OleDbDataReader Dataset = SelectProjectID.ExecuteReader();
while (Dataset.Read())
{
m_ProjectID = (int)Dataset["ProjectID"];
}
Dataset.Close();
}
//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}
//
//get the action ID from the database and store it in m_ActionID
//
OleDbCommand SelectActionID = new OleDbCommand("SELECT tblAction.ActionID FROM tblAction"
+ " WHERE (((tblAction.Action) LIKE @Action))", conn);
SelectActionID.Parameters.AddWithValue("@Action", m_Action);
try
{
OleDbDataReader Dataset = SelectActionID.ExecuteReader();
while (Dataset.Read())
{
m_ActionID = (int)Dataset["ActionID"];
}
Dataset.Close();
}
//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}
//
//
//
OleDbCommand Query = new OleDbCommand("UPDATE [tblEntry] SET [tblEntry].[Weeknumber] = @Week,"
+ " [tblEntry].[Year] = @Year, [tblEntry].[Date] = @Date, [tblEntry].[Project] = @ProjectID, [tblEntry].[Action] = @ActionID,"
+ " [tblEntry].[Hours Spent] = @Time, [tblEntry].[Comments] = @Comment WHERE (([tblEntry].[ID]) = @ID)", conn);
Query.Parameters.AddWithValue("@ID", m_ID);
Query.Parameters.AddWithValue("@Week", m_Week);
Query.Parameters.AddWithValue("@Year", m_Year);
Query.Parameters.AddWithValue("@Date", m_Date);
Query.Parameters.AddWithValue("@ProjectID", m_ProjectID);
Query.Parameters.AddWithValue("@ActionID", m_ActionID);
Query.Parameters.AddWithValue("@Time", m_Time);
Query.Parameters.AddWithValue("@Comment", m_Comment);
try
{
Query.ExecuteNonQuery();
}
//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}
finally
{
//close the connection
if (conn != null)
{
conn.Close();
}
}
}
}
}
Code Snippet
The update statement is not working in my application, no error in C# and no error in ms-access. When I paste the update query into the ms-access query tool and replace the parameter values (@....) with real values, is will update the record.
What am I overseeing here?
--Pascal
View 13 Replies
View Related
Nov 29, 2006
Is there a way to specify a MS Access table (or query object) in the select query of SQL Server.
Ex.:
MSAccessTable (in file.mdb)
col1
col2
a1
a2
b1
b2
SQL query in SQL Server:
SELECT col1, col2 into SqlTable from [file.mdb].MSAccessTable;
Thanks,
View 3 Replies
View Related
Mar 30, 2006
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
View 2 Replies
View Related
Jun 28, 2005
I have the following query created in MS Access that I would like to convert to MS SQL Server, but I can't get the IF logic to work properly, any help you can provide would be greatly appreciated.
Code:
SELECT Customer.Last_Name, Customer.First_Name, Customer.Middle_Name, Customer.Address, Customer.City, Customer.Region, Customer.Postal_Code, Customer.Country, Orders.Order_ID, Customer.Customer_ID, Employees.LastName, Employees.FirstName, Order_Line.Item_ID, Item.[Long Description], Order_Line.Units_Purchased, Order_Line.Price, Order_Line.Discount, CCur(Order_Line.Price*[Units_Purchased]*(1-[Discount])/100)*100 AS ExtendedPrice, Store.Store_Address, Store.Store_City, Store.Store_State, Store.Store_Zip_code, Store.Store_Phone_Number, Store.Store_Fax_Number, Item.Taxable_Nontaxable,
IIf(Item.Taxable_Nontaxable=Yes,([ExtendedPrice]*Tax_Table.Tax_Rate),([ExtendedPrice]*0)) AS Tax_Amt,
Tax_Table.Tax_Rate
View 3 Replies
View Related
Jan 30, 2008
I'm having trouble converting this access query into a sql 2000 query.
Your help would be appreciated:
IIf(nz([PTicketNum],"M999Z")="0","M999Z",IIf(Trim(nz([PTicketNum],"M999Z"))="","M999Z",nz([PTicketNum],"M999Z")))
Here is what I have, but I'm not confident it is correct:
CASE WHEN (PTicketNum = '0' OR PTicketNum IS NULL) THEN 'M999Z' else PTicketNum END AS Ticket
View 1 Replies
View Related
Dec 7, 2004
I have three tables, Equipment, LookUpGroups, and EquipmentGroups.
Equipment
----------
TagNumber
LocationCode
LookUpGroups
-------------
GroupTagNumber
TagNumber
EquipmentGroups
----------------
GroupTagNumber
I want my query to return the GroupTagNumber ONLY IF all of the TagNumbers in that group have a given LocationCode (Given by User).
Example:
GroupTagNumber: G1
TagNumber: A1
LocationCode: 1
GroupTagNumber: G1
TagNumber: A2
LocationCode: 2
GroupTagNumber: G2
TagNumber: A3
LocationCode: 1
So if the user enters LocationCode 1, then he should only get G2 back.
I've started by trying to do a count query:
Code:
SELECT tblEquipmentGroups.GroupTagNumber, tblEquipment.LocationCode
FROM tblEquipmentGroups, tblEquipment, tblLookUpGroups
WHERE tblEquipmentGroups.GroupTagNumber = tblLookUpGroups.GroupTagNumber
AND tblEquipment.TagNumber = tblLookUpGroups.TagNumber
GROUP BY tblEquipmentGroups.GroupTagNumber, tblEquipment.LocationCode
But really I'm completely stuck. Any help/suggestions would be appreciated!
Thanks,
BB
View 2 Replies
View Related
Sep 28, 2004
I have a database front end using access, back end uses sql server. In front end, i have a query, how can i run this query in sql server.
Another querstion, the cliend need the data, but that is too big, it is about 91700 records, How can i retrieve it and send to them. I try to use access, but when i save it as .xls file, it told me there is too many record to save it. Anybody has any idea about the data? Thanks in advance.
View 9 Replies
View Related
Apr 18, 2006
I've been using access to generate reports, queries... from progress db and now I need to switch to SQL. I realy need some help with converting my access statements into SQL. Here is statement I need help with:
LotStatus: IIf([pub_jobPhase].[warraid]="reg05" And [pub_jobphase].[Model]<>"_LotSale","Builder Available",IIf(Not IsNull([pub_jobsales].[finalsaledate] Or ([pub_jobphase].[Model]="_LotSale")),"Closed",IIf([description]=" ","Available",IIf(([sales status]="spec" Or [sales status]="model" Or [sales status]="ssbto" Or [sales status]="ssip" Or (Not IsNull([description]) And IsNull([pub_jobsales].[finalsaledate]))),"Allocated"))))
I know SQL doesn't have IF but CASE but I am stuck on multiple condition in one CASE and expecialy with if ... or... or (...and ...) type of statement!
View 5 Replies
View Related
Jul 20, 2005
I have a table that has numerical ID.I want a query that will get the 20 highest IDs. So in other words thequery would return the last 20 results entered.What would even be better is if they were distinct entries by anothercolumn, but it still returned 20 results.Can you specify in SQL how many results you want?ThanksDave
View 2 Replies
View Related
Jul 20, 2005
I have the MS access query below which uses an IIF statement to put a valuein either the Owned Pallets or Rented Pallets field, how do I do this in SQL?? ...SELECT KC_COPA1.[Posting date], KC_COPA1.SKUCode, KC_COPA1.HierLevel3Code,KC_COPA1.HierLevel5Code, KC_COPA1.LineNo, KC_COPA1.EndMarket,KC_COPA1.Dispenser, format(Sum(KC_COPA1.HA),"0.0000000000") AS Handling,format(Sum(KC_COPA1.ST),"0.0000000000") AS Storage,format(Sum(KC_COPA1.PF),"0.0000000000") AS [Primary Freight],format(Sum(KC_COPA1.IR),"0.0000000000") AS [InterRegion Freight],Format(Sum((IIf(KC_COPA1!MILLPAL="O",[MillPA],0)+IIf(KC_COPA1!RDC1PAL="O",[RDC1PA],0)+IIf(KC_COPA1!RDC2PAL="O",[RDC2PA],0))),"0.0000000000") AS [PalletsOwned], Format(Sum(KC_COPA1.SACost),"0.0000000000") AS SA,Format(Sum((IIf(KC_COPA1!MILLPAL="R",[MillPA],0)+IIf(KC_COPA1!RDC1PAL="R",[RDC1PA],0)+IIf(KC_COPA1!RDC2PAL="R",[RDC2PA],0))),"0.0000000000") AS [PalletsRented], Format(Sum(KC_COPA1.ODCost),"0.0000000000") AS Other,Format(Sum(KC_COPA1.DA),"0.0000000000") AS Admin,Format(Sum(KC_COPA1.CDPCost),"0.0000000000") AS SumOfCDPCost INTO [R3Results_Rev]FROM KC_COPA1GROUP BY KC_COPA1.[Posting date], KC_COPA1.SKUCode, KC_COPA1.HierLevel3Code,KC_COPA1.HierLevel5Code, KC_COPA1.LineNo, KC_COPA1.EndMarket,KC_COPA1.Dispenser;Thanks, John
View 1 Replies
View Related
May 30, 2008
Hi what is the MSSQL eqivalent for this MSAccess Query
1 UPDATE tbl1, tbl2 SET
2 tbl1.ADJUSTED = tbl2.Code,
3 tbl1.CODE = tbl2.Code,
4 tbl1.OTHER_CODE = tbl2.Other_Code,
5 tbl1.STATUS = 'Check', tbl1.ORGK = Null,
6 tbl1.SOURCE = 'Manual'
7 WHERE (((tbl1.STATUS)='Invalid') AND
8 ((tbl2.Override)=0) AND
9 ((tbl1.Path) Like tbl2.webadmin_path))
View 1 Replies
View Related
Feb 15, 2005
I have this query:
SELECT Trim(Left([Display Name],InStr([Display Name],",")-1))+Trim(Mid([Display Name],InStr([Display Name],",")+2,1)) AS UserName, dbo_Employee.[Display Name]
FROM dbo_Employee;
Works good in access, but when trying to put it un sql server, it comes up with errors.
I know mid is substring, I just don't know what trim and instr are in mssql.
Any ideas?
View 3 Replies
View Related
Feb 4, 2000
I have this query:
SELECT Tabelle1.Feld1 AS a1, [Feld2]+1 AS a2, [a1]+[a2] AS Ausdr1
FROM Tabelle1;
It runs perfectly on a Access database but not on a SQL 7.0 server. Can anyone give me a idea on how to convert this?
Thomas Schoch
View 1 Replies
View Related
Jun 24, 2004
Hi, I hope this is in the right forum!
I'm trying to do something extremely simple, but I keep getting this error:
Runtime error '3134' Syntax error in INSERT INTO statement
This is the code it complains about:
CurrentDb.Execute "Insert Into Speechfiles(FileName, SpeakerID,
SamplingRate,
DirectoryPathID,
Text,
CreationDate,
Duration,
Range)
values
(" & Chr(34) & fName & Chr(34) & ",
" & spkrID & ",
" & rate & ",
" & dirPathID & ",
" & Chr(34) & txt & Chr(34) & ",
" & Chr(34) & crdate & Chr(34) & ",
" & duration & ",
" & range & ")"
Since its so long, I've shown it as multiple lines, but in my code it is one long statement:
CurrentDb.Execute "Insert into Speechfiles(...) values (...)"
The table is called Speechfiles and the fields are as follows (types are in brackets)
FileName (text)
SpeakerID (number)
SamplingRate (number)
DirectoryPathID (number)
Text (text)
CreationDate (text) <- this isn't date format, so not the prob
Duration (number)
Range (number)
and the values I'm trying to insert are all of the same type as the fields are.... SOOOOOOOOOOOOOooooo, can anyone spot anything? I've spent way to long on this. All my other CurrentDb.Execute "Insert Into...." statements are working fine, it's just this one!
Thanks so much in advance...
View 3 Replies
View Related
Feb 21, 2006
hi,
I am in the process of creating an append query that sends the fields of an open form from my ms access 2000 db to a table in ms sql 2000. I've successfully linked the ms sql tables and created the query to pull data from my form but when i run the append i get a key violation error and nothing gets appended. I know which field is the primary key in ms sql i just don't know how to change my access table to suit, any ideas?
To start I've made my append query simple and it just pulls the contact name. Here's my sql statement if that helps.
CODE
INSERT INTO dbo_PatientMaster ( PatientFirstName, PatientLastName, PatientAccountNumber )
SELECT [Forms]![Contacts]![FirstName] AS Firstname, [Forms]![Contacts]![LastName] AS [Last], [Contacts]![PatientAccountNumber] AS [Patient Account Number]
FROM Contacts, dbo_PatientMaster
GROUP BY [Forms]![Contacts]![FirstName], [Forms]![Contacts]![LastName], [Contacts]![PatientAccountNumber];
In access the patient account number is empty and I just created that field in my access table today.
thanks in advance
View 6 Replies
View Related
Dec 9, 2004
I am trying to sum up sales for employees and get the employee with the highest sales in one query. The query I have below works but it doesnt get me the EmployeeID. Assume all the fields are in the same table. If I try to do something like this it bombs on me: "MAX(SUM(OrderAmount)"
SELECT Max(OrdersSum) AS MaxOrders FROM (SELECT SUM(OrderAmount) as OrdersSum
FROM Orders
GROUP BY EmployeeID);
Thanks in advance!
View 1 Replies
View Related
Dec 6, 2007
Hi,I would like some help converting an access query to a SQL Server query.The access query is made up of the following and then repeated for each field:SELECT Sum(IIf([gender]='Female',1,0)) AS Female, Sum(IIf([gender]='Male',1,0)) AS Male...FROM dbo.applicants I have tried using the following to test out an alternative, but it brings back the incorrect figure:SELECT COUNT(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS FemaleFROM dbo.applicants I've looked at the table and should get back 350, but only get back 193.But using the following query I get the correct figure:SELECT COUNT(gender) AS FemaleFROM applicantsGROUP BY genderHAVING (gender = 'Female') Although I can't use the above query because I want to also count how many 'Male' applicants there are.How can I do this?Thanks
View 5 Replies
View Related
Jul 12, 2012
I have created a form with box in MS Access. Later I added a query into MS Access. Now I want to make this query dynamic so that it can run with the value available in that box.
I tried several reference in "where" cluase of query but did not work.
View 3 Replies
View Related
Aug 17, 2007
I am being asked to convert this access query into sql server 2000.
Access query
SELECT Left(Trim([Notes_Primary_Key]),InStr(Trim([Notes_Primary_Key])," ")) AS PcnPID, Trim([Notes_Secondary_Key]) AS PcnTicketNum
FROM tri_offnotes;
I'm new to SQL and am not familiar with which function replaces the InStr access function.
View 2 Replies
View Related
Jul 27, 2006
Hi everyone,I am trying to access a client's SQL query anaylzer to run a SQLscript, and I can not find it anywhere.I have not used SQL in a few years, but I would always see a menu inthe program list, or go to Enterprise Manager. I could not find anymenu listings for SQL. I went to Programs - Administrative and did notsee anything related to SQL yet I know that I am looking at their SQLserver.I don't know if they are running SQL 2000 or 2005. Can anyone give mea directory path or perhaps the exe name that launches query analyzer?Thanks!
View 3 Replies
View Related
Oct 22, 2006
hi friends i need help in this sql queryi have table like,id fid__ _____autonumber textand i am storing values likeid fid___________________________________1 1,2,3,4,52 11,12,13,14,15now to find values i am using querysql = SELECT * FROM test12 WHERE `fid` LIKE ('%1%')only problem in this query is it is selecting 1 and 11 and i requireonly 1 as i am giving one in %1%now from this group some one give me the answer of this queryselect *from testwhere fid = '1' -- singletonor fid like '1,%' -- beginning of lineor fid like '%,1,%' -- middle of lineor fid like '%,1' -- end of linenow this query is running perfectly in other database except msaccess2000. can anyone solve this problem. this query is not giving anyanswer. it checks all those records which are singleton but not middleof line records. and it seems to be problem in access only not inmysql. it is working perfectly in mysql but not in access and as accessis my database in application i have to use access and i am reallyirritate when i find in help that either i can use ' * ' or ' % ' inexpression on any one side like '%,1' or '%1,' but not like middle ofline that i am using '%,1,%'here is example of my problemsample table:=id fid___________________________________1 1,2,3,4,52 11,12,13,14,15query likeselect *from testwhere fid = '1' -- singletonor fid like '1,%' -- beginning of lineor fid like '%,1,%' -- middle of lineor fid like '%,1' -- end of linewill result id=1 perfectly but when i searchselect *from testwhere fid = '2' -- singletonor fid like '2,%' -- beginning of lineor fid like '%,2,%' -- middle of lineor fid like '%,2' -- end of lineit will not give me no output. plz help me i dont know what is theproblem if anyone can solve this i will be really thankful.
View 1 Replies
View Related
Sep 25, 2007
HiI'm trying to convert an SQL Query from Access to SQL Server. TheAccess Query goes like this:SELECT Format(EntryDate, 'ddd mm dd') AS [Day]FROM JournalEntriesThis query returns the name of the day followed by month and date (Suaug 21)What would this be like in SQL Server ? What style id do I have to useto get the correct format ?RegardsRune
View 1 Replies
View Related
Jan 2, 2008
I would like to call this stored procedure, but I am unable to passparameters to the @Start and @End.Is thier a way to pass parameters to a pass through query from MSAccess?SELECT COUNT(dbo.tblPersActionHistory.PersActionID) AS [Total Ct],[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.Pe rsActionID, 2)AS [Signed PP]FROM dbo.tblPersActionLog INNER JOINdbo.tblPersActionHistory ONdbo.tblPersActionLog.PersActionID =dbo.tblPersActionHistory.PersActionIDWHERE (dbo.tblPersActionLog.StatusID BETWEEN 4 AND 7) AND(dbo.tblPersActionLog.Rejected = 0) AND(dbo.tblPersActionLog.IsPayAction = 0) AND(dbo.tblPersActionHistory.ActionTypeID = 5) AND(dbo.fn_IsParACorrection(dbo.tblPersActionHistory. PersActionID) = 0)AND([dbo].fn_ParNotException(dbo.tblPersActionHistory.PersA ctionID) = 1)AND (dbo.tblPersActionHistory.ItemDTG >= @StartDate) AND(dbo.tblPersActionHistory.ItemDTG <= @EndDate)GROUP BY[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.Pe rsActionID, 2)
View 2 Replies
View Related
Jul 20, 2005
I will explain (or at least try to) first and then give an exampleafter.I need to append a number of rows from several tables into one mastertable. Unfortunately there are certain columns (which are UNIQUEcolumns in the master table) in the source tables that are repeated inthe same table or across the source tables.Example:Source 1 Source 2[SYMBOL] [SYMBOL_TYPE] [STOCK_SYMBOL] [TYPE]MSFT STOCK AAPL STOCKAA STOCK MER OPTIONMER OPTIONMSFT OPTIONMaster[SECURITY] --- Unique Key[SECURITY_TYPE]As you can see in the example, MSFT is listed twice in Source 1 -- asa stock and an option. MER is listed as an option in both Source 1 andSource 2.My first solution (when I wrote it in Access) was to read in a sourcerow, check if it existed in the master table, and then add it if itdid not. This of course ran very slow. To fix that I added all thesource rows to a temp table and then appended that temp table to themaster. Since I was able to turn off warnings with the SetWarningsaction in access any row that caused a duplicate key error got ignoredBUT the query continued to the end.(Note: DISTINCT on a source table does not work since MSFT-Stock is arow while MSFT-Option is another.)I rewrote the query to run in SQL Query Analyzer but cannot seem tofigure out how to turn off errors so the query runs to completion. Itmay not even be possible to do so in which case I am hoping for asolution other than checking each row to see if it exists before I addit.
View 3 Replies
View Related
Jul 20, 2005
I work for an organisation that uses a bespoke document imaging system, thedatabase of which is an MS sql server.We have MS Access and already use it for some querying of the database.The database comprises a large number of distinct cases, which aredifferentiated by case reference numbers, in one field (table?) of thedatabase. Each of these cases may have many documents associated with it,denoted by the reference number, and these documents may be "new", "pending"or "complete" shown in another data field.We need to know how many cases have work outstanding on them.Our problem is that our bespoke software will only count the number ofdocuments of each status, and not the cases.Is it possible to design an MS Access query which will count the number ofdifferent reference numbers which have any "new" documents associated, butwont count each case more than once?I am reasonably computer-savvy, I just don't know Access or SQL..If I know it is possible, I don't mind putting in the effort to find outhow. I just don't want to waste time barking up the wrong tree ;-)Of course any advice about how this would be achieved, such as pointers tothe right parts of the MS Access helpfiles, or to relevant websites would begreatly appreciated. Some quick code would be even better...Also, if there is any 3rd-party software which could easily do this, I needhelp discovering it...I have looked long and hard, but don't know enoughabout what I am looking for.Yours in hope..--anthonyberetPlease reply in the groups, as my Usenet email address is not working at themoment.
View 5 Replies
View Related
Jul 20, 2005
Hi Everyone,I am doing a small database in ms access. I have two tables: One has allthe information such as name, address, phone number, and etc. The secondtable contains the keyID of the first table and some additionalinformation about the data in the first table( But not every record inthe first table will have additional information).Something like this:TABLE1:KeyID Name Major1 Jack PreMed2 Tom Engineering3 Susan WritingTABLE2:KeyID GPA Year1 3.5 19991 3.4 20003 2.9 2000When I use query:Select Table1.Name, Table1.Major, Table2.GPA, Table2.YearFrom Table1 inner join Table2 on Table1.KeyID = Table2.KeyIDI am only getting records that exists in both tables. How can I changethis query so that I will get all records from Table1 and all recordsfrom Table2. Like this:Name Major GPA YearJack PreMed 3.5 1999Jack PreMed 3.4 2000Tom EngineeringSusan Writing 2.9 2000Thank you a lot for helping me out.Jim*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies
View Related
Jul 20, 2005
Example:Date | ItemCode | Stock_In_qty | Stock_Out_qty | Bal_qty------------------------------------------------------------------12/09/2003 | A100 | 20 | 0 | 2025/10/2003 | A100 | 0 | 10 | *10I need to query an Access database that will take field 3 (Stock_In_qty)plus any bal fromthe above row in the calculated field (Bal_qty) minus field 4(Stock_Out_qty) that will show methe latest Bal_qty, note that Bal_qty = (Stock_In_Qty - Stock_Out_Qty)AS Bal_Qty.As an Example in the above scenario,the Bal_qty in the second row on 25/10/2003 is (0 + 20(Row 1) - 10) =*10.Stock out not necessary comes fromStock In, it could simply be taken out from the Bal_qty balance fromprevious month, any clues?can anyone help? Thanks*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Aug 21, 2007
Hello,
I have been looking for an example of how to do an ad hoc distributed query to an MS Access database. I've tried this;
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0',
'\Server1DataCorporateCPSDailyToolsTest.mdb' ;'admin';'',Names)
I recieve an OLEDB provider error when I try it this way. The version of Access is 2003. The database 'Test.mdb' is on a network share, and the table is called 'Names'. The share is located on the machine that hosts SQL Server 2000.
Is there a setting in sql server that can be set to allow/disallow ad hoc distributed queries?
What am I missing?
Thank you for your help!
cdun2
View 5 Replies
View Related
May 9, 2008
First off, I apologise if this is classed as off topic as it's concerning access but I couldn't see another forum that was better suited for this question.
I'm trying to run this query in Access. I designed it in SQL Management Studio with a test database that I set up and it runs fine. However, when I tried to run it in access I get a syntax error on the subquery bit. Can anyone tell me what I'm doing wrong here?
UPDATE tblInvoice
SET tblInvoice.PeriodID =
(SELECT p.PeriodID FROM tblPeriod p
INNER JOIN
tblInvoice i
ON i.TransDate BETWEEN p.PeriodStartDate AND p.PeriodEndDate
WHERE i.InvoiceID = tblInvoice.InvoiceID)
Thanks in advance,
Andy.
View 3 Replies
View Related
Apr 4, 2007
I have an Access update query that is not working.
Here's my SQL:UPDATE tablename SET tablename.HeartAttackDOD = " "
WHERE(((tablename.HeartAttackDOD)=#1/1/2001#) AND ((tablename.HeartAttack)="2"));
It sets HeartAttackDOD = " " even if HeartAttack = "1" and I only want it to set HeartAttackDOD = " " if HeartAttack = "2".
Can anyone help?
View 1 Replies
View Related
Apr 10, 2007
how to convert this access query to sql query
IIf([Total Bunches] > 0, Production * 1000 / [Total Bunches], 0) as Name2
SUM(IIf(BlockInYield = -1, [SIZE], 0)) as Name1
IIf(BlockInYield = TRUE, IIf(TC_M > 0, TC_M, TC_DENS *[SIZE]), 0) as Name
please......
View 3 Replies
View Related
Dec 3, 2006
Here a query Microsoft Access Query That I would like to convert to SQL. What replace the Word "Last" in SQL SELECT Contacts.FirstName, Last(Contacts.LastName) AS LastofLastNameFROM ContactsGROUP BY Contacts.FirstName;
View 2 Replies
View Related
