I have a Database which is linked to several spreadsheets. I use an append query to move the sheets into a table while also filtering unwanted rows. What i would like to do is add a two fields to each table and auto-populate those fields with a specific text (dependent on which sheet the table came from).
How to add a field and auto-populate values (1 Viewer)
- Thread starter DampFloor
- Start date
For specific advice, we need specifics of your database. In general
1. Go into design view of the table(s), add the new fields you want.
2. Change the APPEND queries to populate those new fields with the appropriate values.
1. Go into design view of the table(s), add the new fields you want.
2. Change the APPEND queries to populate those new fields with the appropriate values.
The sheets which are linked are quotes from suppliers. So each sheet is from a specific vendor and is for a specific category of supplies. The sheets themselves do not state in them which category or supplier. in the tables I appended them to, i would like a field which states supplier and a field which states vendor. From there i will put all tables into one.
- Local time
- Today, 07:17
- Joined
- Feb 19, 2002
- Messages
- 43,257
If you can extract the data from the file name of the workbook, you can do that and put the two pieces of data into hidden fields on your form. Then the append query would pick up the values from the form.
The attached screenshot shows what my current append tables look like. You can see that the table names have both the category and supplier in them. For instance the open table is the supplier "Centennial" and the category is "protein". I would like two more fields in these tables which have the category and supplier named in them for each record.
Attachments
-
Table_Screenshot.jpg
- Local time
- Today, 07:17
- Joined
- Feb 19, 2002
- Messages
- 43,257
How, exactly, do you want to parse the table name? There are no separators between words. Nor, are the words fixed in length. How do we tell where one part ends and the other begins?
My apologies, I am very new to Access and this is for a school project. So if this is not proper technique let me know. Right now the words are separated by capital letters. So a capital letter is the beginning of each word.
I am I required to get this data from the table name? Is there a way to simply make a field and manual enter the word which will then auto-populate all records with this word?
I am I required to get this data from the table name? Is there a way to simply make a field and manual enter the word which will then auto-populate all records with this word?
To add new fields to a table you can use sql
to update records in a table you can also use sql
To split a string up into different parts you can use the split() function.
Since the split char is based on capitalisation I used option compare Binary and inserted a character to split on into the string.
Code:
alter table TABLENAME add FIELDNAME DATATYPEto update records in a table you can also use sql
Code:
update TABLENAME set FIELDNAME1='some string', FIELDNAME2='some other string' [WHERE some condition is met]To split a string up into different parts you can use the split() function.
Since the split char is based on capitalisation I used option compare Binary and inserted a character to split on into the string.
Code:
Option Compare Binary
Private Sub Command1_Click()
Const tbl As String = "CentenialProteinImport"
'Add new fields 'supplier' and 'category', both text, max 30 chars
CurrentDb.Execute "alter table " & tbl & " add [supplier] text (30), [category] text (30)"
'split string up based on caps
Dim i As Integer
Dim s As String
Dim v As Variant
Dim d As String
For i = 1 To Len(tbl)
Select Case Mid(tbl, i, 1)
Case "A" To "Z": d = ";"
Case Else: d = ""
End Select
s = s & d & Mid(tbl, i, 1)
Next
If Left(s, 1) = ";" Then s = Mid(s, 2)
'assume first array element is supplier and second is category
Dim supplier As String
Dim category As String
supplier = Split(s, ";")(0)
category = Split(s, ";")(1)
'update table
CurrentDb.Execute "update [" & tbl & "] set [supplier]='" & supplier & "', [category]='" & category & "'"
End Sub- Local time
- Today, 07:17
- Joined
- Feb 19, 2002
- Messages
- 43,257
That is good naming technique for objects. But, what you are asking to do is to parse that object name. If the parsing has to happen on finding the capital letters, then the parsing must be done in a code loop character by character and when you find a capital letter, that triggers the start of a new field. With that concept in mind, what rule would you apply to determine which parts of the word contain the data that you want.
Given what I said, take a look at the second table name - GFSBakedGoodsImport. That ends up in an array as
G, F, S, Baked, Goods, Import
I'm guessing that won't work because GFS is probably a company name abbreviation given the other table names that I can see.
I think you should probably step back and look at the problem as a relational database rather than as a series of spreadsheets.
Do all the spreadsheets have the same format? How are the spreadsheets named? Perhaps rather than importing the spreadsheets, you should link to the active one and use a common name so that when you link to a spreadsheet, the Access name becomes Link_Import or something generic. If the spreadsheet names have separators such as "_", that would make breaking them into parts much easier. So GFS_BakedGoods.xlsx can be parsed into GFS for the company name and BakedGoods as the product. As you can see, using capital letters isn't going to work for this task.
I'm assuming this is an ongoing import so it will be important to automate it. Somehow a human is looking at the spreadsheet name and determining how to name the table. You just have to come up with rules that will allow this to be done with VBA.
You have started a few threads around this common theme. In the future, I would highly suggest you start by letting others know you are doing this for a school project. This will help with getting answers better aimed to your skill set and more detailed answers.
For myself, I would have a form that prompts for "spreadsheet Name" that would open each spreadsheet and append from it. In addition you could also prompt for category and supplier (possibly using lookup fields, either lookup by value or by reference if you need to make sure these link back to a "Supplier" or "Category" record) with a default based on the name of the spreadsheet.
As a user interface this would be fairly straight forward, would allow for changes if the file name isn't accurate, and would allow you to add to ONE table used for inputs that you could then review. After review you would then append to your real table and delete all records in the review table.
If this is a way that would work for your assignment let us know so we can guide you on how to do this.
