Search results for query: *

Thanks PHV, this worked, great tip. I have two follow up questions. 1) The tblMatrix also has 12 balance fields in it. For the part of the code above (Line 3) where you say "SELECT * FROM tblMatrix..." would I be better off specifying fields? 2) I'm not very experience with nested...

PHV, Thanks for your post, the table alias trick really improves the readability of the code. You asked what happened...I got no error, just appends 0 records. Imagine this sample data already in tblMatrix: App Bank Account Year 1 1 1 2008 Now I want to add: App Bank Account...

I was able to add the composite primary key by doing the following: 1) Turn warnings off 2) Remove the full WHERE clause above 3) Turn warnings back on This way access by default will try to add duplicate primary key but the error message is suppressed. Here's sample data: App Bank...

Randy, I've double checked it in the debugger and the year appears to be correct. I'm pretty sure the where clause it missing parenthesis () but I'm not sure how to next them.

I have two tables: tblDeposits (App, Bank, Account) and tblMatrix (App, Bank, Account, Year, Amount) The composite primary key in tblDeposits is App + Bank + Account. The composite primary key in tblMatrix is App + Bank + Account + Year. I have a form where user chooses Year from a combo box...

Thanks pwise. Since ultimately the form in the external db was updating a table in the external database, by linking the tables, and importing the form, I was able to update the data in the external databases locally. Thanks.

I would like to use one database form to run an update in 4 external access databases. Master.mdb has 1 form called frmAutomation which has one combo box called cboPeriod. I would like to add a command button to this form adding vba code to the "on click" event. When clicked, the code will...

Worked great. Thank you.

To append new records from tblOne to tblTwo, I use an action append query to add records where the id field in tblOne does not exist in tblTwo. I am trying to do this using vba rather than an action query. Below is my code. The code compiles and executes but 0 records are appended (no error...

HarleyQuinn, It worked when I did it, but when I try to run the query a little later, it did revert back again. It's weird, it's like the changes keep when I save, close & rerun the query, but when I run the subsequent query which uses the 1st query above as it's source, it just changes the...

Thanks, it worked when I saved it in SQL view. I never would have thought of that.

Yes. I tried the bracket. I got the following error message: [Adj-01] is not a valid name. Make sure that is does not include invalid characters or punctuation and that it is not too long.

While in query design view, I am trying to rename a column/field name to Adj-01 (see formula below; This works fine, however, when I save and exit the query, and then go back in, access renames the column/field name to Adj-1. The "0" gets ommitted. Since I am using the precise field name in a...

Thanks Ginger, it worked. I modified slightly, I had to add brackets to the fields names as it seems access doesn't like it. For i = cbostart To cboend If i < 10 Then strSQl = strSQl & ", [0" & i & "]" Else strSQl = strSQl & ", [" & i & "]" End If Next i One...

Thanks RivetHead, This is close. I have 2 issues: For month for 2 digits 10, 11, & 12, the sql code makes them 010, 011, 012. Also, for some reason I keep getting an error on the line QDef.sql = strSQl or perhaps the next line. It says error in from clause. I used a breakpoint and the...

Does anyone know how to create a select query to display a dynamic # of columns from a underlying query. I have a table called <tblMatrix> with 13 fields/columns: Account 01 02 03 04 05 06 07 08 09 10 11 12 where columns 01 thru 12 represent months with financial data or null values. I know...

This worked like a champ. It was super fast & efficent. I simply placed this code directly in the query SQL view & I replaced the hardcoded year week in the code below with the two combo boxes (Year Week) concatenated together, so now it is fully automated. Thanks. Jack

I am trying to mimic the vlookup functionality of excel in access where there isn't an exact match. tblStore has 4 fields (sample data) Store Year Week Manager A 2007 01 Jack A 2007 05 Cindy B 2007 01 Tom C 2007 26 Bill The way to read this data is Jack is the manager...

Your solution worked. I tweaked things a bit. Here is my final button code on a form with 2 combo boxes: cboMonth (Jan thru Dec) & cboYear (2007 thru 2008) as drop downs. I added the year as a field to the matrix table so this will work for multiple years as well. Works perfect. Thanks...

Thanks, Duane, I will give this a try today and let you know how it worked. With respect to setting up the table like a crosstab, versus setting a table up with 3 fields (account, month, amount)...I thought it would save space. I have approximately 100,000 accounts, as time went on, my table...