Full Version: All fields on a Form combined into one Field
UtterAccess Forums > Microsoft® Access > Access Forms
tormoni
I am having the [censored] time figureing this out. I have a form where sales information is entered and the data is stored in separate fields, of course, in the database... but also, once the data is entered into the form, all those fields will be combined into one Memo field of a separate table onto one of the the company's SQL server tables. I know it sounds confusing, I'm quite the noobe. So I have attached an example database so you may be able to see what I am talking about.
Edited by: R. Hicks on Fri Nov 5 19:30:48 EST 2004.
erwardell
Why do you want it to be placed in to one memo field that would be silly. It would be possible, but why?
S_R_T
I can just see you sitting infornt of your computer doing this,
I am the noobe myself but one thing that i have learned from this website that will come abck in the future and Bite you in the butt
could not tell you much about the memo field! i will be keeping track of this post so i can laern to but i will share this with you
Use primary keys in EVERY TABLE
it does not hurt to have it but i heard it will hurt of you DONT
use the foreign key concept
example
tblCustomer
customerID primary key (automnuber)
name
address
etc
etc
etc
table2
tblOrderID
Order see you sitting infornt of your computer doing this,
I am the noobe myself but one thing that i have learned from this website that will come abck in the future and Bite you in the butt
could not tell you much about the memo field! i will be keeping track of this post so i can laern to but i will share this with you
Use primary keys in EVERY TABLE
it does not hurt to have it but i heard it will hurt of you DONT
use the foreign key concept
example
tblCustomer
customerID primary key (automnuber)
name
address
etc
etc
etc
table2
tblOrderID
ORDERID primary key (autonumber)
CustomerID ( this is where you link the priamry key from tblCustomer [customerID] to the field in tblOrder [MemberID] making it the magical FORIEGN KEY
make sure the memberID in tblOrder is set to (Number) not text or anything else
use the standard naming for tables and such,
tbl = for tables
frm = for forms
rpt = reports
and many others for different events
i personally like to see a realationship structure so i see what things go where, maybe you can set that up so people can understand it better.
I believe that your combobox are set up wrong, when the value gets stored in the table it will store the (word it self) NOT ( the "what i refer to as the CODE" that tell access what the value is)
you want the "CODE" to be stored not the value itself ( like what you have to be GOLD AND OTHER)
i hope i am not sounding mean, believe me, when i first posted a db and did not have the above information followed, i had by head bit off, but they were great on teaching me so will they be for you ( i hope)
good luck with the memo problem.
finally there is a great quote flying arround this great website
it goes something like this
normalize your tables first, o really go a head, nrmalize your tables first, NO YOU are not listening, NORMALIZE your tabled FIRST: lol
good day to you sir, hope you get a solution.
later
tormoni
SRT, thanks for the info.
And as for WHY I want to do this, if you must make me explain...
Let me put it this way. We have the main Database that all customer service reps use. Then we have my Database that the sales department uses.
Customer Service doesn't have access to what goes on in sales but it is helpful for Customer Service to know some of the sales info. Each customer's record in the main database has a notepad table (actually it's one table distinguished by account numbers) So if I could just get all the information compiled into one field and append it to that particular customer's notepad in the main database, the customer service reps could get a small snapshot, if you will, of what went on when the sale was made.
I hope this helps explain.
ScottGem
Yes we "must make you explain". One of the nice things about UA is that we don't foster bad design. We want to understand why you want to do something so we can provide the best solution rather then just answer the question. This is particularly true in a case like this as doing what you want to do should only be done if no other way is available.

I think you need to get together with the admins for the Customer Service database. The RIGHT way to do this is to design a table in the Customer Service database that holds sales info. Then you simply append the data from the sale that you want the reps to have access to.

One of the problems I have with your scenario iw it would appear to only hold info about the last sale.

If you really can't do it right (because of restrictions on the design of the CS database) and your only option is to pass a text string to the notepad table, then its simple to do. Just create a query that concatenates the relevant fields into one column and export that query.

HTH
tormoni
See, this is for a phone company, I can't make changes or edits to the CS database, but I can append records to the back-end tables, ie the notepad.
Well, ya see, I know how to do that with the query, but the problem that I am running into is that on the main form goes some of the sales info but there is a subform that holds multiple records, ie multiple phone lines, rates, features, etc. And I can't figure out how to set up a query to include all fields on the main form and all the records and fields on the subform. I will attach a jpg of the form so you can see what I am talking about.
ScottGem
I didn't say YOU could make changes to the CS database. I said you NEED to talk to the admins of that database and see if they can make changes to accomodate your data. Unless they are using a canned application that they can't change, they may be able to accomodate you. Before you go any further you need to sit down with them.
ut, before that you need to normalize your data. Even if they can make changes they probably won't until your data is normalized. From just a brief glance you have repeating groups and other issues that need to be fixed.
Once you have normalized and talked to the admins, then come back here and we can help you deal with what you have to do.
HTH
tormoni
Ok, I have already spoke with the Admins, don't ask me why, but this is how they want the info in their database, they want the info put into the Customer's Notepad on their account. So far, we have been forced to data enter this info from the sales db over into the CS database manually by bringing up each customer's account separately and re-typing this into their notepad. My bosses want me to find a way, since our CS admins are so stubborn, to easily append the data over into the CS database.
IS I was supposed to have this figured out by today, but since I was forced into answering every question as to who what why. All I wanted was just to find out how to do this one simple thing, regardless of why, I just wanted to know how to do it!
Scroggie
You can do create an a field linked to the admin database: txtAdData. Have it hidden on the form view that the sales folks use.
then either have an "Update to Admin table button" or set the before_update to execute this code
txtadData = txt1 & vbcrlf & txt2 & vbcrlf ....so that you have all the text from the various fields assembled into one large text field (with Carriage Return/Line feed) between them.
tormoni
Yes Yes Yes, I have that, I can do that part. Like I said, (and if you look at my sample DB and the .jpg of the form i have attached to a couple of my replys) The problem is when I get to adding the records that appear on my subform. I can't figure out how to add fields from the records in my subform, I mean, surely I can add what is on the very first record, but not if there is more than one record, how to I get those also, ya know what I am saying?? I have this feeling that it will have to be some type of recordset code, but I don't know how to program up a recordset code that well.
But yes, thanks much for the info Scrooggie, I figured of doing what you explained, it's just how I imagined doing it. I just can't get the records in the subform is all.
ScottGem
Excuse me for trying to help you do the right thing. You need to understand that we all VOLUNTEER our time here. We also volunteer to share our expertise.
Since you are dealing with multiple records, you have to either loop thru the recordset to concatenate all the records. This will be more difficult since your database is not normalized.
tormoni
I understand about that's what I need to do, loop through the recordset, but I'm not sure how, and that's what I'm trying to figure out what to do. Is there anyplace, website, tutorial, etc, that I can go to to learn about recordsets? I'm not sure how to do the code for one.
lso, I know I've heard you talk about normalizing, but I've searched the forum about what exactly that means and I'm still not to clear on "normalizing" Where can I go to learn more about that?
Jerry Dennison
I have code in the Code Archive that will concatenate records into a single field. It may be possible to modify the code to meet your needs. At least it illustrates a method for doing what I think you're asking.
ScottGem
There are several good references here about Normalizing. Generally its the process of reducing redundant data in your database. Another rule of normalization is to not have repeating groups. Your table with Feature1, 2, etc. is a repeating group. It should be more like:
eatureID (PK autonumber)
AccountID (FK)
FeatureID (FK)
You would also have a lookup table listing all features. Another point is you should have an autonumber as primary key for your tables, including the lookup tables. By using foreign keys instead the actual text, you can speed up queries.
Take a look at the code Jerry referred to. It will probably be what you need.
Jerry Dennison
You are absolutely right about the repeating groups, etc...
Why do you have the PK and one of the FK's named the same?
ScottGem
Thanks for catching that Jerry, I must have been half asleep when I wrote that. The PK should probably be OrderedFeatureID or something like that.
Jerry Dennison
I figured as much. I just can't help getting a little dig in every now and then.
Keep up the "good work"!
tormoni
Hey, thanks for referring me to the code library for that bit of code about looping through a recordset. It was very useful (although I'm still playing around with it to figure it all out) but I think I can manage from here. I'm also looking into the whole "Normalizing" thing. I think it's starting to make sense to me now also.
Thanks again for all ur help.
ScottGem
Glad to assist.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.