Using MS Access 2003 My query. select a.personid, a.cardeventdate as newday, a.intime as intime, max(b.cardeventdate) as prevday, b.outtime as outtime from ( select personid, cardeventdate, min(cardeventtime) as Intime, max(cardeventtime) as outtime from ( SELECT T_PERSON.cardno, T_PERSON.NAME, T_TITLE.TI...
Wow, I'm having a bit of a headache with Access 2007. I have two tables, main_table and ref_table. when I store an int id in main_table corresponding to some record in ref_table, I get the looked up value of the record that I'm storing and not the int id. This isn't what I expected and I'd like access to behave like every other databa...
I want to perform JDBC SQL queries on a MDE Access file. I've set up the data source ODBC and everything worked well for a MDE file. Now, I'm working with a newer version of the MDE file, and here is the result: java.sql.SQLException: [Microsoft][Driver ODBC Microsoft Access] Cannont read record. Read authorization unavailable for "tbl...
I have an Access 2007 report which is grouped by RoomType_ID I need to do a running count on each line within the Group so that I end up with something like: RoomType_ID 1 1 Mr and Mrs Smith 2 Mr and Mrs Jones 3 Mr and Mrs Smythe RoomType_ID 2 1 Mr Foo 2 Mrs Bar etc... How can I go about getting this? ...
I have found the way to copy the record that I would like, but now I am having a violation of the Primary Key constraint. Here is what I am trying to do: We make a product that comes out of our maching into 2000lbs bags and it is givne a number, e.g. 26273. We store and sell it in those bags, but we also can sell it in smaller 50lbs and ...
I'm currently on a 4-person team tasked with the development and maintenance of a legacy MS Access application. The application is quite large, with hundreds of forms, reports, queries, and tables. Currently we have the front end split into about 7 mde components, each of which is essentially an application in its own right, joined by ...
I have an Excel spreadsheet provided as a report, when really its more of a database, with the row number acting as primary key. I need to compare some columns in this set with a different set of records from an Access 2007 database. To make matters more complicated, this needs to be done automatically, without user input, on a regular...
I have a regular form called "Fish". It has a TreeCtl called "loctree" which I used as a location chooser to change a field in Fish. I've since added several subforms to Fish. Two of these have location fields that I would like to use loctree for. Since I didn't want to instantiate a new TreeCtl for each of these subforms, I thought I ...
Hi, I am using Access 2007 and need help creating a query. If this is crazy long, sorry! I figure the more details the better understanding. I am not a programmer--I hope someone will help. I need to know how to collapse/summarize/roll up non-numeric data into a single row that shares a characteristic/value. What I want is like a crosst...
OK, I have several controls on a parent form, along with a subform. The user enters data in the subform. Then when they click off the subform, I have code to validate their data. However, MS in its infinite wisdom, has chosen to not let the focus leave the subform until the user clicks another control on the parent form (instead of the p...
Hi do you know a safe converter to convert .mde file to .mdb ? ...
How to get a value from previous result row of a SELECT statement If we have a table called cardevent and has rows [ID(int) , Value(Money) ] and we have some rows in it, for example ID --Value 1------70 1------90 2------100 2------150 2------300 3------150 3------200 3-----250 3-----280 so on... How to make one Query that get...
Hi, In my SQL query in Access 2000, I got a field containing numbers like: 0,15 1,30 1 0,50 Where x, is the hour, and ,xx is the minutes. I need to convert this field into a DateTime (or the Access equivalent) field, because I later need to sum on the time values in a report. How can I convert that field into DateTime? ...
The code below is what I am trying to use in order to get the count of one column and the averages of two other columns from multiple tables and then put the results into another table. I thought this would work, but the count that is being put into the new table is incorrect as are the averages. A lot of times the averages are outside t...
I have Office 2007 on my dev machine. I have a small app I'm building for a customer server with Office 2003 that needs to do some interop. My program will run as a scheduled task in a nightly batch process. No matter what I do, I can't find the version 11.0 of the Access interop assembly so that will work on the server. I've tried...
I know this is a simple question for someone out there, but I have never really used function module at all because I did not understand what they were. So I have a whole bunch of things I can use this for (cut down on redundancy), but I want to know how I call call into into a sub (like a button click) procedure from a form. I tried t...
How to create Auto Number Field in access? Create table table1 (RecordNo autonumber, personid varchar(50)…) But it showing error. How can I make a query in the access? ...
How to give where condition in the select query? ACCESS 2003 MY Query SELECT RECORDNO, PERSONID, EMPNAME, TITLENAME, DEPARTMENT, NATION, CARDEVENTDATE, INTIME, OUTTIME, (select TOP 1 F1.CARDEVENTDATE from tmp_cardevent as F1 where F1.RECORDNO < F2.RECORDNO AND F1.PERSONID = F2.PERSONID order by F1.RECORDNO DESC, F1.PERSONID DESC) A...
The below code is the close routine of an Access App our BA wrote. When executed it is not only closing the Access App but my C# winform app as well, on the same computer. The Access app is named DME Referral and my winform app main process runs in the Task Manager as MATRIX.exe.(Yes I am programming the MATRIX...never allow a group of...
Following podcast #59, I've been considering moving some images from an MS Access DB into MS SQL Server (it's the only piece I haven't migrate over yet). Right now they are stored as OLE Objects in the MS Access DB. However, I haven't got a clue what data type they should be stored as on the SQL Server side, or how to get them inserted ...