I need to query an Access database that will take field 3 (Stock_In_qty)
plus any bal from
the above row in the calculated field (Bal_qty) minus field 4
(Stock_Out_qty) that will show me
the 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 from
Stock In, it could simply be taken out from the Bal_qty balance from
previous month, any clues?
can anyone help? Thanks
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
I am new to SQL server, and am learning as I move an Access db to MSDE2000A. With Access db I run several different queries from a VB6 application in the basic format:
SELECT testdb.* FROM testdb WHERE testdb.datefield = #1/01/2004#
When working with the MDSE version of the db, problem is with the "#" delimiter of dates. MSDE is giving a bad query error. If I change the format to: datefield = '1/01/2004' , the query works on MSDE
However, using SQL builder in VB Design Environment I can run the query in either format and get a result.
I have written a small app in VB2005 for a friend of mine and now I am trying to extract records for an access database with a specific month in the date field. What I am trying to get is all the entries for a month like July. I have this as my query:
SELECT ID, ShopDate, ShopText FROM Cost WHERE DATEPART([month], ShopDate) = ?
When I run the preview, it is asking me to pass along an AnsiString as a parameter. I have tryed numbers, spelling out the month, numbers in single and double quotes, spelled out month in single and double quotes but it will not take anything that I type as a parameter for the query
I am receiving the following errors while trying to import data from an access database.
[Destination 2 - Amc_C_ARRV [400]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
[Destination 2 - Amc_C_ARRV [400]] Error: There was an error with input column "tSympOnsetDate" (494) on input "Destination Input" (413). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
[Destination 2 - Amc_C_ARRV [400]] Error: The "input "Destination Input" (413)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (413)" specifies failure on error. An error occurred on the specified object of the specified component.
The source database has "invalid" dates such as "6/4/199" and "12/23/1899". I am unable to change the data in the source database and have been instructed to load the values as nulls in the SQL destination tables. I tried to do a conditional split using a "ISDATE()" function but that is not a valid function for conditional splits.
What are my options for validating the incoming dates are correct or nulling the dates out that aren't
I have an SSIS package that moves data from SQL Server to an legacy Access database. In SQL Server, there is a date/time column that I need to split into a separate date column and time column in the access database. Initially I just created a derrived column for the time and set the expression equal to the source date/time column from SQL Server. Unfortunately, that just makes the date column and time column the same having the full date/time.
What expression can I use during a derrived column transformation to assign just the date to a derrived column and just the time to another derrived column?
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);
// //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);
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.
how to write a query to get current date or end of month date if we pass year and month as input
Eg: if today date is 2015-09-29 if we pass year =2015 and month=09 then we have to get 2015-09-29 if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).
Hi Group!I am struggling with a problem of giving a date range given the startdate.Here is my example, I would need to get all the accounts opened betweeneach month end and the first 5 days of the next month. For example, inthe table created below, I would need accounts opened between'5/31/2005' and '6/05/2005'. And my query is not working. Can anyonehelp me out? Thanks a lot!create table a(person_id int,account int,open_date smalldatetime)insert into a values(1,100001,'5/31/2005')insert into a values(1,200001,'5/31/2005')insert into a values(2,100002,'6/02/2005')insert into a values(3,100003,'6/02/2005')insert into a values(4,100004,'4/30/2004')insert into a values(4,200002,'4/30/2004')--my query--Select *[color=blue]>From a[/color]Where open_date between '5/31/2005' and ('5/31/2005'+5)
I'm currently using the SQL to find records older than todays date in the SSD_SED field. I'm having to update the date manually each day. Is there a way I can automate this?
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
I Apologize if this isn't the forum to ask this... I have a MS Access (MDB) file with a table with 2 date fields, i want to read from a dialog on my app (on MS Visual .NET Studio 2005), here's the code I've been using do far:
FechaC, FechaCob, are the two Dates I want to retrieve, but when I debug, it reads a 0 (zero) from the date fields, is there a limitation? can they be read? is there a special way to read them? > thanks in advance!
Hello, I have a package variable called LatestTableFileDate that is typed to DateTime. I have an Execute SQL Task that executes the following SQL statement against an MS Access 2000 database table through the OLEDB Jet provider;
SELECT MAX(FileDate) AS MaxFileDate FROM CollectionData_Access
The statement parses fine. At the moment, the returned value is NULL. I have 'Result Set' set to 'Single Row'. The FileDate column in the Access table is 'Date/Time', and the Format is 'Short Date'.
In the result set properties, I attempt to set the result to the LatestTableFileDate user variable. When I run the task, the task fails, and the following appears on the 'Progress' tab;
[Execute SQL Task] Error: An error occurred while assigning a value to variable "LatestTableFileDate": "Unsupported data type on result set binding 0.".
I searched the forum for this problem, and didn't find anything. Do I need to convert the date in MS Access to a string and set the package variable to a string type, or is there some other way to handle this?
A Contractor has a list of Contracting Companies that he has worked for in his history.
For each row in the "Contracting Company" table, there is an Active Date and an Inactive Date. Each Contracting Company cannot have overlapping dates.
The last row in the table for that Contractor may have a null Inactive Date. That just means that he doesn't know when his contract will end.
In Report Builder, I can access the "Last Active Date", because there is a pre-built formula for "Last Active Date". However, whenever I access the prebuilt formula "Last Inactive Date" - I get the inactive date for the second-last row, because the last row has a null value for the Inactive Date! I want to get the Inactive Date for the last row - not the second last row!
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000 Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000 W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000 A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000 B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000 C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000 B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000 C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
I have a whole bunch of forms that have an unbound StartDate and anEndDate field that I have used in MSAccess MDB databases as parametersin queries (ie tblEvent.StartDate > Forms!myFormName.StartDate.)So, now I'm migrating this beast over to and ADP/SQL Server projectusing Views and Procedures.How do I pass the value in Forms!myFormName.StartDate to a Procedureand get something that looks like:If tblEvent.StartDate > Forms!myFormName.StartDate then ...Any help is GREATLY appreciated. This is a major problem before I canmove ahead with this beast!lq
First off, it has been a few years since I have done extensive work with SQL and that was using Oracle. But I am trying to develop a simple asset database for work, as we have nothing in place. I started out with Access, and decided to move to SQL express for many reasons.
What I have now is that I imported my data from my access 97 database to Excel, only my AssetTable did not import dates, I assume because Access and Sql Express handle dates differently... so a the time I just ignored that column.
Is it possible to insert the dates into the now populated SQL Express database AssetTbl where the AssetID's match? Here is what I have.
Sql Express Database Name: BAMS Table Name: AssetTbl fields: AssetID, SerialNum ...(many other fields)... DateAcq <- currently Null
Excel file: AssetDateAcq.xls fields: AssetID, DateAcq (in format 07/12/2007)
To me it sounds like I need to do a short script/program to loop through the file read an AssetID from the excel file, and the DateAqcuired and then have it do an update on the DateAcq field, but it has been so long since I've done any work with SQL that I am finding there is a lot of "Dust" to blow off, and I don't know if I'm heading down the right track... or completely off course.
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.
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
I have an MS Access 2002 application that is distributed to a number of PCs around our office. The data for this application is stored on a central SQL Server that is linked in through ODBC.
This application has been in place for two years and working fine. We recently formatted and restored a PC, and now that particular PC has issues with the Access application.
Every time it tries to filter one of the linked SQL tables on a date field, Access goes unresponsive and GPFs out. If it's in a query that is behind a report, I get the old standard 'Catastrophic Failure'. If I open the table and right-click filter or run a query manually, Access GPFs.
I've tried recreating the ODBC, linking the tables through TCP/IP as well as Named Pipes. Nothing fixes it. All Windows and Office updates have been applied. This is not the first time we've reformatted a PC in the office, but we've never had this issue.
Hi All,I am facing a problem with a sql what i used in MS Access but its notreturning the same result in MS Sql Server 2000. Here i am giving thesql:SELECT TOP 3 format( MY_DATE, "dddd mm, yyyy" ) FROM MY_TAB WHEREMY_ID=1The above sql in ACCESS return me the date in below format in onecolumn:Friday 09, 2003But in Sql server 2000 i am not getting the same format eventhough iam using convert function, date part function etc.Please if you find the solution would be helpful for me..ThanksHoque
Please find below my query and result , how to display [Total Service Time ] in HH:Min format (Currently values in minutes)
Query: SELECT DISTINCT dbo.sectn_dept.sectn_sc AS Customer, MONTH(dbo.incident.date_logged) AS Month_Number, DATENAME(month, dbo.incident.date_logged) AS Month, YEAR(dbo.incident.date_logged) AS Year, dbo.incident.incident_ref PM_ref, dbo.product.product_n "Product",
[Code] ....
Result: Need to Display [Total Service Time] in below Format:
I am trying to load a table from MS Access into SQL Server. The Table has several columns defined as Date/Time. When I define the transform I get an error saying that the conversion between DT_DBDATE and DT_DBTIMESTAMP is not supported.
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
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.
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!
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));
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
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
I have the following 4 rows in a table Company JobNumber BeginDate ModifyDate 1 2 12/12/2005 11/12/2006 1 2 12/12/2005 11/15/2006 2 3 11/12/2005 1/12/2006 2 3 11/12/2005 9/15/2006 The company and Job Number make up the key so yes this table has duplicate keys. My question is how would I return the two keys with the max modify date? So the results would look like this: 1 2 12/12/2005 11/15/2006 2 3 11/12/2005 9/15/2006 Thanks,
