ms-access

Need help on access database design and queries

**tblLeeasse Details** nLesseeId txtLesseeName txtContractNumber txtLesseeAddress1 txtLesseeCity txtLesseeState txtLesseeCountry txtLesseeEmail `tblSupplierDetails nSupplId txtSupplName txtSupplTinNum txtSupplAddress1 txtSupplCity txtSupplState txtSupplCountry txtSupplEmail tblSchedule nScheduleId txtScheduleNumber nLesseeId txtLease...

Update: using sql to keep only a single record where both name field and address field repeat in 5+ records - Microsoft Access

Hey all, I am trying to delete all but one record from table where name field repeats same value more than 5 times and the address field repeats more than five times for a table. So if there are 5 records with a name field and address field that are the same for all 5, then I would like to delete 4 out of 5. An example: id name address...

problem with rows.count of a table in a word file

I opened a Word Doc,which had tables in it, use VBA from access. I want to add a new line and write something in that line. I worte some code but that code worked only in debug mode. As soon as i clear all break point and let the programm run, i will have a problem. It seems that word will only write data into the first line. Please hel...

How to correct drivermanager.getconnection exception

I want know about how to correct driver connection exception. I got that exception as drivermanager.getconnection exception. Does any one know please answer me. I use ODBC Bridge connection and MS ACCESS Database. Class.forName("sun.jdbc.odbc.JdbcOdbc"); Connection con = DriverManager.getConnection("jdbc:odbc:mydb", "", ""); excepti...

Allow to save data on access db using vb or c# on win7 or vista?

Hi all, I want to install my app or (just access db)to AppData folder to enable write for it. so, how can I do that in setup project. Is there just parameter or should I wirte custom action. Thx. ...

Database Compacting and Archiving - MS Access Backend

Scenario: There is a legacy program (Not sure what language) and I have been asked to "Compact and Archive forms in the database". At the moment when the user opens the application it is taking about 2-5min to load around 27000 Records!!! My theory is that it is loading all the records on start-up but that might not be the only reason. ...

Calling WCF Service from MS Access

Hi all, I want to create a create a WCF Service which is invoked on the button click of MS Access Form. Please help me out. Kind Regards, Shahid Hazoor ...

Access form, search for a record from another table and insert into field in current form

I have a table that stores invoices and a form based off of that table that looks like an invoice so that you can search previous invoices. Currently I am creating the invoices in excel and then exporting the data as a new record into the Invoice table. I was wondering if there was a way I could create the invoices from my Invoice form. ...

Select that returns based off two columns as a Key

I have a problem that I can't seem to solve. What I am trying to do is find unique columns based in table A that doesn't exist in other table B and store them in table B. Something on the lines of SELECT DISTINCT(A.FNAME), A.LNAME from ADDRESS A WHERE NOT EXIST (SELECT DISTINCT(B.FNAME),B.LNAME ...

Access Report won't allow me to refer to a field in VBA unless it's on the report in its own right

So, in an Access Form or Report a good way to show something dynamic on the screen that is more complex than =[UnitPrice]*[Quantity] is to drop into VBA. e.g. in this simplified example, the underlying table for this Report has two fields ShowTax and TaxRate. By making a TextBox's control source =GetTaxInfo I get to introduce some compl...

How to display all fields regardless of match?

I have two tables, ProblemChildren and DailyTable. ProblemChildren consists of an ID and a name. DailyTable consists of a ID, name, and date_hit. Right now, I'm displaying all ProblemChildren.name where it exists in DailyTable. So if the name "Mike" in ProblemChildren does not have a record in DailyTable, then his name is not outpu...

Using LIKE operator in Access works but not in Java

I'm making a java application for a toy store and using the MS-Access database. I'm using the operator "LIKE" to search for products in database using the user's keyword. E.g. in Access: SELECT * FROM tblToy WHERE toyName LIKE '*puppy*' OR toyBrand LIKE '*puppy*' this gives me the desired result in access. But in java when i run thi...

How to find the highest populated instance in a column in SQL.

Hey, So I have a table (person), that contains columns such as persons name, age, eye-color, favorite movie. How do I find the most popular eye color(s), returning just the eye color (not the count) using SQL (Microsft Access), without using top as there might be multiple colours with the same count. Thank you ...

MS-Access -> SELECT AS + ORDER BY = error

Hello. I'm trying to make a query to retrieve the region which got the most sales for sweet products. 'grupo_produto' is the product type, and 'regiao' is the region. So I got this query: SELECT TOP 1 r.nm_regiao, (SELECT COUNT(*) FROM Dw_Empresa WHERE grupo_produto='1' AND cod_regiao = d.cod_regiao) as total ...

make a table in MS Access

Hi, I have a query in MS ACCESS, I ran it in MS ACCESS: SELECT * FROM table1 INNER JOIN table2 ON table1.f1=table2.f1 WHERE table1.f2=table2.f2 It works fine. However, I need to save the results into another table. So, I changed it to: SELECT * Into a1 FROM table1 INNER JOIN table2 ON table1.f1=table2.f1 WHERE table1.f2=table2.f2 ...

Using Microsoft Access SQL operators in Python ODBC

Short version: When I try to use Access's DatePart function via ODBC, it cannot be resolved. Longer version: I have a Microsoft Access query which returns rows with a timestamp and a score. I want to sort it by day and then by score - effectively a high-score table for the day. For want of a better function, I used the DatePart funct...

Exec an SQL-Server 2008 stored-procedure from Access, passing a TABLE variable

I need to pass a table from Access to SQL-server and execute a stored-procedure. I'm using pass-through queries in Access to do this. My pass-through query: DECLARE @MyVar TABLE { .....<variables> } INSERT INTO @MyVar SELECT * FROM [MyTable] EXEC sproc_test @Myvar My stored-procedure: ALTER PROCEDURE [dbo].[sproc_test] @M...

Syntax error in FROM Clause

hi all I'm working on asp.net web application & for this use MS access for back end, My Query is given below which is successfully executed on MS Access but error on front end ("Syntax Error in FROM Clause") select USER.EMPID as EMPID,USER.FULLNAME as FULLNAME, USER.USERNAME as USERNAME,Employee.ROLEID,ROLE.ROLENAME AS ROLE FROM USER ...

Refactoring sql into regexp - Microsoft Access

I'm not a pro with regexp and especially in this specific context. Any idea how to use a function to do a RegExp Replace and then calling it from the query in Microsoft Access? By the way, this query oddly enough doesn't work fully and maybe it's why this could use regexp. The idea is to match the three fields in the temp_table query:...

Access partition function: Is there a way to make it show bin categories that don't have a count?

Hi, I'm trying to use the Access Partition function to generate the bins used to generate a histogram chart to show the frequency distribution of my % utilization data set. However, the Partition function only shows the category bin ranges (e.g. 0:9, 10:19 etc) only for the categories that have a count. I would like it to show up to 1...