How to add a field and auto-populate values

[DampFloor]

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).

 

[plog]

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.

 

[DampFloor]

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.

 

[Pat Hartman]

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.

 

[DampFloor]

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.

 

[Pat Hartman]

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?

 

[DampFloor]

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?

 

[static]

To add new fields to a table you can use sql

alter table TABLENAME add FIELDNAME DATATYPE

to update records in a table you can also use sql

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.

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

 

[Pat Hartman]

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.

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.

 

[Mark_]

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).

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.