Import Excel data containing Option Group and Y/N fields into MS Access

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Import Excel data containing Option Group and Y/N fields into MS Access

Post by wire_jp »

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.

User avatar
HansV
Administrator
Posts: 78236
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

Post by HansV »

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.

S1524.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Import Excel data containing Option Group and Y/N fields into MS Access

Post by wire_jp »

Hi Hans,

Thank you very much for the information and your prompt response.

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Import Excel data containing Option Group and Y/N fields into MS Access

Post by wire_jp »

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

User avatar
HansV
Administrator
Posts: 78236
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

Post by HansV »

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.
Best wishes,
Hans

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Import Excel data containing Option Group and Y/N fields into MS Access

Post by wire_jp »

Hi Hans,

Thank you for the information. I really appreciate your help.

Kind regards,

Laurence

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Import Excel data containing Option Group and Y/N fields into MS Access

Post by wire_jp »

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

User avatar
HansV
Administrator
Posts: 78236
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

Post by HansV »

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.

S1529.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Import Excel data containing Option Group and Y/N fields into MS Access

Post by wire_jp »

Hi Hans,

Thank you for this information. I appreciate your help.

Kind regards