I would like to generate a primary key for each line of my spreadsheet. My spreadsheet flows like so: Each row is an individual record. I have a status column that represents the status of each record. Ultimately, thie represents different forms of open status & then of course a closed status. The only other status is blank. When I go from blank status to non-blank I would like to generate a unique # that I can use key that I can it use refer to this unique record.
Cell C1 contains the formula to return the day from the full date in B1... I tried the formula help....they return the number...but not the day... like monday or tue...how do i do it?
My excel charts don't resize the way they should after hiding them through a worksheet macro. After unhiding, the scale overlaps the data labels and, over several times, the scale even colapses to a small thin line. Any suggestions on this? The code I'm using to hide cells and charts:
I am trying to ensure that the information entered in an input box is only accepted when it is a letter first then 4 digits. I have help with this using data validation within excel but it doesnt work when I run the VBA routine
I am creating an Access database from excel. In the code I get the Database and Tables name from Excel spread sheet and assign them to variables.
In the first table, & tblNamePrime &, I create a Primary Key called, [PrimeRecId]. I create a second table, & tblName & and try to create a Foreign Key called, [RecId] and try to Reference it to the table, & tblNamePrime & [PrimeRecId]. I always receive the following error message:
Syntax Error in CONSTRAINT Clause -2147217900(80040e14)
If I remove the code in blue it creates the tables but then I need to go th access and link them by access
I have tried many different ways and have followed instructions from SQL, Access VBA and I am unable to Link the Primary Key to the Foreign Key.
I am new at trying to tie Excel to Access and SQL.
The code below is just 2 tables but when I am done there will be a total of 4 tables all linked back to the first table.
Private Sub RDCModel() Dim dbConnectStr As String Dim Catalog As Object Dim cnt As ADODB.Connection Dim dbPath As String Dim tblNamePrime As String Dim tblName As String
'Set database name here 'MsgBox "Current user is " & Application.UserName dbPath = ActiveSheet.Range("C1").Value 'Database Name.........
I have been trying to break my new chart that combines stacked bars on the primary axis with their labels on a transparent line graph tied to the secondary axis. The secondary axis allows me to show the labels I want as categories and position them relative to the visible part of the stacked bar (waterfall style) proportionately outside of the bar. The trouble comes when my label positioning is less than or more than the primary boundaries. I can solve this manually by changing the max or min value of the secondary axis, but what I really want is for the secondary axis to be locked to the primary axis' max and min automatically.
Is it possible to use a formula to replicate primary and secondary lookup in SQL and is it possible to retrieve data from Excel spreadsheets using SQL.
I know it is possible in Access auto assign a unique ID (primary key?) to each record in a table. I am creating records in a table using Excel and Visual Basic to write to a ".mdb" file without using Access. Is there a way to auto assign a unique ID within the coding?
The reason I ask this is if I had two users writing to the ".mdb" file at the same time, I don't want them to end up with the same ID's for two differend records.
Sub UploadRecord() Dim DBName, DBLocation, FilePath As String Dim DBConnection As ADODB.Connection Dim DBRecordSet As ADODB.Recordset Dim XLRow As Long, XLColumn As Long Dim DataRange As Long Application.DisplayAlerts = False Application.ScreenUpdating = False
I have a VBA program in Excel that creates an Access Database. There are 4 tables and below are the SQL statements that create the Database. There are more fields associated with each table. When I go to Access the tables look great and I do not get any error messages.
[code] ...
When I send, “write” records from Excel to the tables the record's data is written correctly, the AcountInformation [RecId] updates with an auto number correctly the Primary Key auto number of each of the subsequent tables updates correctly.
The Foreign keys do not contain the record number from the, “refers back to AcountInformation [RecId] Primary Number”. Every thing works except the updating of the Foreign Key
I have tried everything even going into access and manually deleting a record from AcountInformation table to see if it deletes the appropriate records in the other tables and updating a record. It does not update and or delete the records from the other Tables.
The bottom line, Access does not pass a Primary key number to the Foreign Key.
I have a document to track funding and would like to be able to auto-populate individual task sheets within the workbook based on a task drop-down. So if I choose Safety from the "Task Area" drop down it would auto populate the full row from the Master Sheet to the Safety Sheet. I have uploaded my document so you can better understand.
I have a chart with sales data on one axis and inventory on a secondary axis. I have the average of each columns of data and want to add a reference line to each axis with it's average. I have added the averages to my data set, but it only adds the reference line to my primary axis. I actually began by manually inserting two lines to the chart (which seems asinine). They actually didn't print because they weren't actually on the chart, but rather just on the spreadsheet behind.
I have this spreadsheet that has over 20,000 rows. I was asked to build a search page to will bring back all transactions based on a primary key (account number). Here is a sample:
I have a sheet which I need to arrange and it looks like -
Column A Column B Column C Column D
[Code]...
the values in Column B, C , D , E & F need to be in new columns based on what we have in Column A associated with "1"
Output should be - Column A Column B Column C
[Code]....
so whatever is in Column A associated with "0" should remain in place whereas whatever is in Colimn A associated with "1" should get added to the new columns. 0 and 1 resembles the 1 Records which are used as a split to normalize the data.
It all works fine but I've noticed an odd quirk which I can't explain nor can I seem to fix. Here's the (reduced) code :
Code: Dim appExcel As Object ' Excel Application Dim chtPareto As Object ' Chart Set appExcel = CreateObject("Excel.Application") Set chtPareto = appExcel.Charts.Add With chtPareto ' Primary category axis
[code]....
The category (x) axis title should be horizontal, whereas the value (y) axis title should be rotated. Stepping through the code, when I .SetElement for the category axis title, it appears horizontal as planned. However, as soon as I .SetElement for the rotated value axis title, the category axis title also rotates.
The following code works to update the target sheet based on matching column names within the source sheet.
However, I would like to bolster this by not only updating based on matching field names, but also the primary key in column A. Meaning the data from the source sheet isn't always in the same order (based on primary keys) as the data in the target sheet. So I want to keep the order of the primary key in the first column and then update matching field names in column B through the last column where the primary keys match between the source and target worksheets.
Sub CopyDataBlocks() 'VARIABLE NAME 'DEFINITION Dim SourceSheet As Worksheet 'The data to be copied is here
I am in the process of making a database more efficient and am running into a problem with sorting data. I currently am trying to use AutoFilter to sort the data. When I want to then narrow the results further using the same column as the critical and there is nothing that matches the critical, I get everything from the entire database that matches that critical rather than what I want to see, which, in this case, would be nothing.
I have attached a sample file. In it, when All AF 1000 is run and then Selected MAC 2000 Wash is run, I want to see no results instead of seeing all MAC 2000 Washes from the original data set. I can do it using IF/THEN but I am looking for a faster way to do it.
