Results 1 to 11 of 11

Newbie - Looking to change field name (somehow) prior to exporting

  1. #1
    Rhothgar is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    7

    Newbie - Looking to change field name (somehow) prior to exporting

    Hi

    Brand new to this forum and Access so please go easy on me if my question seems stupid. Searching the forum doesn't bring up any results for me. Also, I hope I have posted in the most appropriate section of the forum and that the terms I am using are correct!



    I have been using Access for about 10 days now on and off. I have used every other programme within the Microsoft suite over the years bar Access as I deemed I'd never have use for it. When I've tried it in the past, it's just been mind boggling so I've ended up shying away from it.

    Anyway, I now have the bit between my teeth on a simple starter project (which actually isn't a simple starter project). I have been watching Kirt Kershaw's videos on YouTube so I already have a grasp of some basics. As usual, I am trying to run before I can walk.

    I've created a database to contain stock from various sources and I ultimately want almost a one click solution to export revised quantities and prices into several different formats but, for now, one step at a time.

    I have a field in a query with the name Action. The imported field name was "Action(SiteID=UK|Country=GB|Currency=GBP|Version= 585|CC=UTF-8)".

    With this query, I want to be able to export it to CSV. For now, I can see I can only export to xlsx format which is fine. I can just go in and save it to CSV.

    So my question is, actually, two-fold:-

    a) When importing, is there some way using a macro or a VBA script to concatenate the "Action(SiteID=UK|Country=GB|Currency=GBP|Version= 585|CC=UTF-8)" simply down to Action and import all data to overwrite the previous data using a particular set field which wouldn't change (such as ItemID) and
    b) When exporting, can I reverse the concatenation such that Action becomes Action(SiteID=UK|Country=GB|Currency=GBP|Version=5 85|CC=UTF-8).

    One final point is I have also hidden fields in the query for legibility but I want those to be exported too (preferably without unhiding them). Simplicity is key.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,636
    When importing, why is it important to change a field name, only to want to put it back when exporting? I think there are a few things you can do, but to suggest them probably depends on the whys. If it's only for visual reasons this might be one of the few times I'd suggest that maybe you would use a caption for the table field, allowing you to refer to its name one time, or its caption another time.

    Can you not just append the source data to an Access table to serve as the raw data, then update or append to a table that's been set up to take the modified, data type corrected data? For the "hidden" fields, what does that mean - you've set the field width to zero, or unchecked them in a query design? If the latter, they are not hidden, they are probably criteria fields, meaning they filter but the field isn't part of the data set at all.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    Rhothgar is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    7
    Quote Originally Posted by Micron View Post
    When importing, why is it important to change a field name, only to want to put it back when exporting? I think there are a few things you can do, but to suggest them probably depends on the whys. If it's only for visual reasons this might be one of the few times I'd suggest that maybe you would use a caption for the table field, allowing you to refer to its name one time, or its caption another time.

    Can you not just append the source data to an Access table to serve as the raw data, then update or append to a table that's been set up to take the modified, data type corrected data? For the "hidden" fields, what does that mean - you've set the field width to zero, or unchecked them in a query design? If the latter, they are not hidden, they are probably criteria fields, meaning they filter but the field isn't part of the data set at all.
    It is indeed only for visual reasons.

    You're 2nd question is a good one. The honest answer is I don't know but know it is possible. The file comes from another source outside of my four walls so that changes on a daily basis ie I download a different file which has a different name.

    By hidden fields, I have hidden them in table view by right clicking and hitting Hide Fields (reading between the lines I guess this sets their width to zero?) as they are irrelevant to the view. You're making me wonder if the structure of my database is ideal...

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,636
    Maybe it's not ideal, but sometime when importing from other systems you have to deal with junk that doesn't fit your carefully crafted Access design. To be sure I understood, you want to change a field named SiteID=UK|Country=GB|Currency=GBP|Version= 585|CC=UTF-8 to Action . Seems like an odd field name - it looks like data. If it's only for visual reasons, try giving that Access field a caption instead. It's been a long time since I used any such captions so I'm fuzzy on what effect that has on things. For example, will a query work using the caption OR the field name, or does it have to be the name only, or the caption only if it exists? Once you know that, you should be able to decide if it will resolve your issue. Maybe research "ms access table field caption" and see what you can find.

  5. #5
    Rhothgar is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    7
    Quote Originally Posted by Micron View Post
    Maybe it's not ideal, but sometime when importing from other systems you have to deal with junk that doesn't fit your carefully crafted Access design. To be sure I understood, you want to change a field named SiteID=UK|Country=GB|Currency=GBP|Version= 585|CC=UTF-8 to Action . Seems like an odd field name - it looks like data. If it's only for visual reasons, try giving that Access field a caption instead. It's been a long time since I used any such captions so I'm fuzzy on what effect that has on things. For example, will a query work using the caption OR the field name, or does it have to be the name only, or the caption only if it exists? Once you know that, you should be able to decide if it will resolve your issue. Maybe research "ms access table field caption" and see what you can find.
    So kind micron but I wouldn't say my database is carefully crafted. I just don't want to go too far linking things up only to find something else will not resolve.

    Yep! That's the full field name that imports so I'll look into captions.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,636
    Post back if that won't help as you always have options, like the Replace function for example.

  7. #7
    Rhothgar is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    7
    I've already got distracted by another issue which I've possibly overlooked.

    Maybe you know the answer to this. It's pretty hard to interpret some of the searches I have done for this.

    I've now plonked another table into the database as an experiment and it is a linked table so obviously uneditable.

    I need to create an ID field as a primary key to link it to the information in another table from another source (which hopefully I would love to be linked also then I can update stock levels as often as I want).

    Is there some way to simply duplicate a linked table and add a plain ID ie 1, 2, 3, 4 autonumber do you know?

    Maybe I should start adding screenshots to explaining myself a bit better as I am not fully conversant with all the right terms of reference at the moment. My issue then would possibly be that the information in the linked table may not necessarily contain the same items every day. If we add new product lines, then there would be additional ItemID's which would not appear in the non-linked table unless manually added?

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,636
    Is there some way to simply duplicate a linked table and add a plain ID ie 1, 2, 3, 4 autonumber do you know?
    Not sure how that fits in with the rest of what you asked but I'll try. You can update or append to a resident table by using the values in a linked (e.g. ODBC table that you can't edit). I had to do this for a work order system where the source data got updated overnight. I first appended all the new records, then updated everything in the resident table; i.e. SET myTableName.SomeField = sourceDataTable.Somefield. Obviously have to do that for every field in the query, but just once. While this might duplication, the linked data could not be edited but certain fields in the resident table could be. Not only that, it took a load off of the network as there were about 25 users and the source data that was static all day long anyway. Task Scheduler ran the updates overnight so it was ready to go next morning.

    If something like that would work, then I'm not sure if there is a need for an additional autonumber field, but I might not be following completely. You have to imagine I know nothing about your db, system and process because practically speaking, I don't.

  9. #9
    Rhothgar is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    7
    Quote Originally Posted by Micron View Post
    i.e. SET myTableName.SomeField = sourceDataTable.Somefield.
    Is this SET a command that needs programming in somewhere in VBA or a macro? Sorry with only being a novice I'm not entirely following.

    I have a linked table which will update as and when I call the file manually from a server. It comes to me in CSV so I open and save it as XLSX and then it updates in the database.

    I also now have a table in the database and have linked it by dragging ItemID from the linked table to the static table but it reports that the relationship is indeterminate. This doesn't sound good.

    What I am trying to achieve is a central database where I can aggregate stock from Amazon, Ebay and our website, jiggle the figures about and then feed those figures back to all the channels.

    There has been a similar post on here which I happened to find yesterday (and commented on) but it looks as though the OP gave it up as a bad job perhaps thinking someone could give him an easy solution. I, on the other hand, realise that it isn't easy and am prepared to put in a lot of effort providing I am rewarded with a working solution.

    I've opened Access before on many an occasion and given up within a day because it just looked too complex. Of course, to become a master at stuff like this like yourself probably takes many years of doing it on a daily basis, making mistakes, learning from them, getting better bit by bit.

    Manually editing quantities on each channel takes time and therefore we never do it because there's only two of us and every day is pretty much full on. Hence me thinking a central database would be the way to go, drawing in the individual feeds (all in different formats with wildly different fields), organising the information and having either an input table, query, form or report (for incoming feeds), a central table, query, form or report where the information is aggregated using only the basic fields such as a shortened title, quantity, selling price and maybe barcodes and an output table, query, form or report whereby the edited quantities and prices can be outputted back into their original format saved as XLSX, XML or CSV and manually uploaded back to the three channels.

    To add complications, we also ship stock to Amazon for them to fulfil so it leaves us and sits in their warehouse and is a portion of the stock we carry. In a way that can be ignored for now as I am trying to learn how to access Amazon via API and am already stuck on how to write a pre-query script in Java using Postman but that's another issue. I cannot create a Base 64 encryption key despite following Amazon's advice to the letter.

    I feel that is relatively simple to do as, unlike the other chap that posted a similar post, I do not want customer records, shipping labels printing off and this, that and the other at this stage although it would be nice to work towards that.

    It would be really nice if I could import customer records and feed that back into Sage 50 Accounts at some point but I'm not even thinking about complicating it yet. I need to learn the basics and go from there.

    I think I may be getting mixed up with tables, queries, forms and reports already.

    Tables are used to organise data.
    Queries to answer questions about the data.
    Forms for inputting data.
    Reports to illustrate certain data.

    I believe I only need tables and queries? A form would be fine if it means I can lock down the tables and queries at some point to avoid interference and mistakes with the existing data and only allow my business partner to enter additional information rather than them having to learn about databases. They simply won't invest their time in it.

    Do you think I should start a clean post on this subject?

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,636
    I'm afraid I can't really comment on anything you're asking about Access that involves eCommerce because I have no experience with that. Have to think you'd be better off with some sort of eCom application or service that hosts or includes a web database?
    As for SET, used in that context, it is a SQL keyword that indicates what will take place is an update. INSERT INTO would be an append; SELECT is just that. There are also others, but I digress...

  11. #11
    Rhothgar is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    7
    Quote Originally Posted by Micron View Post
    I'm afraid I can't really comment on anything you're asking about Access that involves eCommerce because I have no experience with that. Have to think you'd be better off with some sort of eCom application or service that hosts or includes a web database?
    As for SET, used in that context, it is a SQL keyword that indicates what will take place is an update. INSERT INTO would be an append; SELECT is just that. There are also others, but I digress...
    OK. Thanks for all your input so far. A web database might well be the way to go in the future once I've learnt Access but I'd rather keep it in house for now. I was unaware SQL can be used in Access. More reading required...

    Thanks again.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-10-2015, 02:07 PM
  2. Replies: 20
    Last Post: 01-13-2015, 02:23 PM
  3. Replies: 4
    Last Post: 07-28-2013, 12:40 AM
  4. Newbie How to have form change??
    By NYCAcess in forum Forms
    Replies: 2
    Last Post: 05-20-2010, 02:21 AM
  5. Replies: 1
    Last Post: 02-26-2009, 11:31 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums