Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-20-2019, 04:17 PM   #1
JoseO
Newly Registered User
 
Join Date: Jul 2013
Posts: 66
Thanks: 41
Thanked 0 Times in 0 Posts
JoseO is on a distinguished road
Inserting into a single column

Hi,

Ever since I started using Access, I've been told to make use of Tables in order to populate my combo boxes. As such, rather than setting up, say, 4 tables to hold just one column of combo box choices each, I use one table and each column then supplies the combo box choices.

This works well the majority of the time but, I am in need to add choices to the first column only [Field1] independent of the other columns - is this possible?

So, while I only have 5 choices in Field1, Field4 has 70 choices. So, in Field1 from the 6th empty cell down to the the (New) record field, all of these cells in Field1 are empty.

My question: Is there a way to programmatically insert a text value (or any value for that matter) in the next empty cell of the first column only (Field1)? I am using an unbound form.

Thank you for any guidance/assistance.

JoseO is offline   Reply With Quote
Old 06-20-2019, 04:24 PM   #2
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 797
Thanks: 10
Thanked 161 Times in 155 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Inserting into a single column

If I understand the question, the way to update existing fields (whether they have data or not) in an existing record is to use an UPDATE query or sql statement. You might have to provide a better indication of what you're table looks like. Maybe a pic?
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
JoseO (06-20-2019)
Old 06-20-2019, 04:49 PM   #3
JoseO
Newly Registered User
 
Join Date: Jul 2013
Posts: 66
Thanks: 41
Thanked 0 Times in 0 Posts
JoseO is on a distinguished road
Re: Inserting into a single column

Thank you Micron. Yes, I have tried UPDATE and INSERT. But when I try them with a WHERE clause it's essentially filling all Null fields in Field1 (first column). I've attached a picture.
Attached Images
File Type: jpg Tbl_Sample.jpg (79.8 KB, 10 views)

JoseO is offline   Reply With Quote
Old 06-20-2019, 05:24 PM   #4
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 2,028
Thanks: 0
Thanked 486 Times in 482 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Inserting into a single column

Personally, I would build 4 tables.

If you want to keep the column 'compact' and avoid gaps, your code would have to identify which record in Field1 is the 'next' record, in other words the first record with Null in that field. Assuming DDID field will always be positive and increasing, something like:

UPDATE tablename SET Field1 = "something" WHERE DDID = DMin("DDID", "tablename", "Field1 Is Null")

or

UPDATE tablename SET Field1 = "something" WHERE DDID IN (SELECT Min(DDID) FROM tablename WHERE Field1 Is Null)
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 06-20-2019 at 06:58 PM.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
JoseO (06-20-2019)
Old 06-20-2019, 05:25 PM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,599
Thanks: 38
Thanked 875 Times in 858 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Inserting into a single column

Hi Jose. Just my 2 cents, but you might as well bite the bullet and set up 4 tables with one column each. You're not really saving a whole lot of space by using only one table with 4 columns. One advantage of having separate tables is they're easier to maintain, as you have already found out. Also, they become portable. If you need to use the same set of choices in another project, you just export the table and you're done. Again, just one person's humble opinion. Cheers!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
JoseO (06-20-2019)
Old 06-20-2019, 05:40 PM   #6
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 797
Thanks: 10
Thanked 161 Times in 155 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Inserting into a single column

I'll go along with those sentiments, but if you keep it to 1 table don't worry about the gaps. The combo won't care. The pic isn't exactly what I thought you were dealing with.
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
JoseO (06-20-2019)
Old 06-20-2019, 07:33 PM   #7
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,560
Thanks: 88
Thanked 1,479 Times in 1,395 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Inserting into a single column

Look at it as data. Data comes in records. The value in each field of a record should normally have some relationship to the values on other fields of the same record. This is not the case when having the fields as the RowSource of independent combos.

A normalized single table RowSource structure has a column for the ID of the combo box and a separate record for each value in each combo. This is especially important when the combo reqiures both an ID for the value that will be stored and a displayed text. Trying to keep multiple fields aligned in the select by field model would be a nightmare.

Put composite indexes on ComboID, ValueID as well as ComboID,DisplayText to ensure there are no duplicate combinations.

The rowsource query is in the form of:
Code:
SELECT ValueID, DisplayText FROM tblComboRowSources WHERE ComboID = whatever
Adding new values is simple

Code:
INSERT INTO tblComboRowSources (ComboID,ValueID, DisplayText)
VALUES (whatever, thenewID, "thenewdisplaytext")
whatever identifies the combo.


Last edited by Galaxiom; 06-20-2019 at 07:45 PM.
Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
JoseO (06-20-2019)
Old 06-20-2019, 07:51 PM   #8
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,077
Thanks: 81
Thanked 1,600 Times in 1,484 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Inserting into a single column

Got to side with Galaxiom and the others who recommend separate tables for combo boxes. And G's explanation (technical violation of normalization by having unrelated columns adjacent to each other) is about what I would have said.

However, there is a solution to your problem that might work if you bloody-bedamned have to keep it as uneven columns... in the .RowSource for the combo box, include a WHERE clause that requires that the text column cannot be empty or null.

I.e. build the row source for the combo just like you normally would. But then go back into the .RowSource property, which should contain a simple-minded SELECT query. Add a WHERE clause such that for the combo that uses ColumnB, you can't pick a blank.

Code:
SELECT IDCol, ColumnB FROM ComboMultiTable WHERE NZ( ColumnB, "" ) <> "" ;
Theoretically, you shouldn't do this for databases where you really wanted everything to be normalized. But from a pragmatic viewpoint, this will keep you from tripping over the unevenly populated columns.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
JoseO (06-20-2019)
Old 06-20-2019, 08:54 PM   #9
JoseO
Newly Registered User
 
Join Date: Jul 2013
Posts: 66
Thanks: 41
Thanked 0 Times in 0 Posts
JoseO is on a distinguished road
Re: Inserting into a single column

Thank you, thank you June7, Dbguy, Micron, Galaxiom, and Doc-man

Not only do I appreciate the SQL samples but, more so, the educating part of each of your posts. I want to learn good DB design and implementation and, having pros like each one of you guide me and instruct me, is a huge blessing!

I will go with the general consensus of having a separate table for each combo box. This is awesome! Thanks again!!!
JoseO is offline   Reply With Quote
Old 06-20-2019, 09:21 PM   #10
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 2,028
Thanks: 0
Thanked 486 Times in 482 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Inserting into a single column

Glad we could help. Maybe I will muddy the waters a little. Yes, I voted for 4 tables but have to say I have done something sort of like this. I have a table with 2 fields and never more than 1 record. Table is not a source for comboboxes. I just use it to save the version number of db in one field and the other field has a date for a process that is run periodically. Neither value has anything to do with the other. Technically, should be 2 tables. I will admit to violating a few rules of normalization in this db. It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works."
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
JoseO (06-20-2019)
Old 06-20-2019, 09:24 PM   #11
JoseO
Newly Registered User
 
Join Date: Jul 2013
Posts: 66
Thanks: 41
Thanked 0 Times in 0 Posts
JoseO is on a distinguished road
Re: Inserting into a single column

Nice. Thank you June7 :-)

JoseO is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate Column Values from Multiple Rows into a Single Column erncelen Queries 50 12-14-2018 10:20 AM
Single Column Group, Multiple Column Detail Thales750 Reports 3 11-16-2018 06:45 AM
List boxes: single column vs multi column scubadiver007 Forms 6 04-04-2013 04:43 AM
Re: Concatenate Column Values from Multiple Rows into a Single Column Snuffy94 Queries 2 07-19-2012 08:54 AM
Converting dates from 3-column to single column floridamussels General 4 12-17-2010 11:34 AM




All times are GMT -8. The time now is 12:13 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World