Recent content by jal

I guess the more concise way to do it is a scalar subquery: Select COUNT(*)/ (SELECT COUNT(*) FROM ( SELECT DISTINCT DepositDate FROM FilesToDownload WHERE JobName LIKE 'Optum*' )) FROM FilesToDownload WHERE Jobname LIKE 'Optum*' I wonder whether the join method is faster? Dunno.

I've got a solution although i would think there was easier way. I pretended that the first Count-query returns a table, and the same with the second query. To each of these two tables (one value as a table) I added a second value (the number 1) so that I could join the two tables on this...

I feel like a complete idiot. I used to be able to write simple queries but I haven't had much practice lately and now it seems I've completely forgotten how. If you type SELECT 100/20 in a query window it will return "5" (try it for yourself), and in that same vein I want to run SELECT...

Well, I think I figured out the answer. Now I'm thinking the engine didn't generate the dup values, they must have already existed in the three source tables. I didn't remember that those tables also have an autonum column.

A while back I finally became aware that AutoNumber columns do not necessarily provide unique values. Today I discovered a bug in an old database. I had a query like this (note that tblCombine is initially empty at the start of this query). INSERT INTO tblCombine ( SELECT * FROM table1...

Sorry I forgot about you. I'm not a very advanced programmer so don't expect my notes to be 100% reliable but they will definitely get you started on crosstab queries.

I've never created an Access pivot table but, as far as I know, a pivot table creates the same kind of display seen in crosstab queries/reports. Do you know SQL? If so, I can give you a cutout from my notebook that will explain (in simple terms) how to create crosstab reports using SQL.

Using your mouse, import the excel sheet into an Access table. Add a column called House_FlatNumber and a column called Street. Also add a column (Yes/No or True/False) called "Success". Then using VBA, use a recordset to loop through the table one row at a time, using the following logic...

I believe It's possible to write an ADO connection string in 2 different stages like this: Dim cn as New ADODB.Connection Cn.Provider = "Microsoft.Jet.OLEDB.4.0" Cn.ConnectionString = "Data Source=C:\Northwind.mdb;" but I don't see any compelling reason to do it that way.

What do you mean by "better"? In what ways are the current connections unsatisfactory?

Tell me about it. I too have never worked for an IT dept, nor held a programming job.

If you have no prior VBA experience, then hiring a professional might be wise. But if you have a little VBA knowledge, we should be able to get you started. Rewrite the code and, if you like, post it back so we can take another look at it.

This also worked for me, note that it uses an alias (although it worked fine without it) AND a WHERE clause, which puts it closer to your version. Dim cn As New ADODB.Connection cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Jet OLEDB:Database Password=access;Data...

A third problem in your code: FROM tblGeneral_Information IN [;Database=D:\RMS Test DB\Raw Material Specification Secondary Database.accdb;pwd=jeff] That's not the code I gave you in the password example (although I may have used it in the non-password version). The password version was more...

Nevermind the alias for now, I can't seem to get the alias to work. I'll keep trying. (For now, I deleted my comment about possibly using an alias).