Hello,
I have an Excel spreadsheet which contains several different Option Groups (e.g. Man, Woman, Child, People) and Y/N fields into MS Access database. In Microsoft Access, the option group is represented as 1, 2, 3. Similarly Y/N is represented as 1 and 2. Should I create the corresponding option groups in MS Access database (e.g. Man, Woman, Child, People) or (Park, Zoo, Reserve) or I have to use 1, 2, 3, 4? I have to import Excel data into MS Access database and also export data from MS Access database to the Excel spreadsheet. How do I set up the different option Groups in Excel and similarly in the MS Access database?
Thank you in advance.
Import Excel data containing Option Group and Y/N fields into MS Access
-
- StarLounger
- Posts: 67
- Joined: 19 Jan 2018, 00:00
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import Excel data containing Option Group and Y/N fields into MS Access
I'd use the same values (1, 2, ...) in Access. The option buttons in an option group in Access also have 1, 2, ... as Option Value by default.
You can use lookup tables to get the associated text.
You can use lookup tables to get the associated text.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 67
- Joined: 19 Jan 2018, 00:00
Re: Import Excel data containing Option Group and Y/N fields into MS Access
Hi Hans,
Thank you very much for the information and your prompt response.
Thank you very much for the information and your prompt response.
-
- StarLounger
- Posts: 67
- Joined: 19 Jan 2018, 00:00
Re: Import Excel data containing Option Group and Y/N fields into MS Access
The Excel spreadsheet's option group has text and if I import this data into an existing Microsoft Access database to update the database, would it cause a conflict of text data (from Excel) being added to a field with a numeric value (1,2,... in MS Access)?
Regards
Regards
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import Excel data containing Option Group and Y/N fields into MS Access
You'd have to convert the data from text to number.
- import or link the Excel data into Access.
- Create an append query to add and convert the data from that table to the target table.
- import or link the Excel data into Access.
- Create an append query to add and convert the data from that table to the target table.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 67
- Joined: 19 Jan 2018, 00:00
Re: Import Excel data containing Option Group and Y/N fields into MS Access
Hi Hans,
Thank you for the information. I really appreciate your help.
Kind regards,
Laurence
Thank you for the information. I really appreciate your help.
Kind regards,
Laurence
-
- StarLounger
- Posts: 67
- Joined: 19 Jan 2018, 00:00
Re: Import Excel data containing Option Group and Y/N fields into MS Access
Hi Hans,
Regarding your post dated "28 Jun 2022, 08:19", I wanted to create a combo box similar to your screenshot example, as a two column combo box with an ID header and Description but Microsoft Access only allows a Col1 and Col2 header. How can I change the names from Col1 & Col2 to ID and Description, as shown in your example?
Kind regards,
Laurence
Regarding your post dated "28 Jun 2022, 08:19", I wanted to create a combo box similar to your screenshot example, as a two column combo box with an ID header and Description but Microsoft Access only allows a Col1 and Col2 header. How can I change the names from Col1 & Col2 to ID and Description, as shown in your example?
Kind regards,
Laurence
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import Excel data containing Option Group and Y/N fields into MS Access
A combo box uses the field names of the table (or query) that acts as its Row Source for the column headers, if the ColumnHeads property is set to Yes.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 67
- Joined: 19 Jan 2018, 00:00
Re: Import Excel data containing Option Group and Y/N fields into MS Access
Hi Hans,
Thank you for this information. I appreciate your help.
Kind regards
Thank you for this information. I appreciate your help.
Kind regards