UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Multiple Yes/no Colums Query Form, Access 2016    
 
   
Canadianskibum
post Apr 4 2019, 10:05 AM
Post#1



Posts: 5
Joined: 3-April 19



I am building a video game screener, I have imported a list of all the games and for each game I have a Yes/No column for availability on the various platforms(Xbox, PS, PC, etc..). I am trying to use a option group so the user can see all the games available on one platform. I was able to build the query with Yes/No boxes for each platform but I think it looks better to use an option group. I am guessing I have to change the inputs from Yes/No to something else but I can't figure out how to perform this.

Any ideas would be much apperiated.

Thanks,
Go to the top of the page
 
theDBguy
post Apr 4 2019, 10:12 AM
Post#2


Access Wiki and Forums Moderator
Posts: 75,549
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

Unfortunately, I think you may have committed a common mistake in designing your table. Using multiple Yes/No fields in a table usually leads to problems when designing a user interface or retrieving and searching for information. If you are not familiar with the term "normalization," I suggest you read some short articles on the topic to get an idea of what I am saying. Once you get an idea on how to properly design your table for optimum use, you can post any questions if you run into any issues trying to follow the principles you learned. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Canadianskibum
post Apr 4 2019, 10:23 AM
Post#3



Posts: 5
Joined: 3-April 19



Thank you, I will take a look!!!
Go to the top of the page
 
theDBguy
post Apr 4 2019, 10:42 AM
Post#4


Access Wiki and Forums Moderator
Posts: 75,549
Joined: 19-June 07
From: SunnySandyEggo


Good luck! We'll be here if you need more help.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Apr 4 2019, 12:01 PM
Post#5


UA Admin
Posts: 35,146
Joined: 20-June 02
From: Newcastle, WA


We have some useful articles here.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Canadianskibum
post Apr 4 2019, 12:32 PM
Post#6



Posts: 5
Joined: 3-April 19



Thank you very much for the recommendations, I understand how normalization will greatly improve the functionality of my database.

I read a few articles regarding Normalization and how multiple Yes/No categories shouldn't be used. My interpretation for platform availability I should have a separate table for availability with each game having multiple entries based on all the platforms it is available on. My concern is having to update if availability is decreased. I import the information from an excel document which is my master sheet and numbers and availability occasionally changes from time to time.

Are my concerns valid or misplaced?

Go to the top of the page
 
nuclear_nick
post Apr 4 2019, 12:52 PM
Post#7



Posts: 1,746
Joined: 5-February 06
From: Ohio, USA


It confuses people new quite a bit.

To try and clarify... It takes three tables, in this example. One table holds games. Second table holds platforms. Third table just links games from the games table with platforms from the platforms table, creating a table of 'game platforms'.

That allows you to do things like... on the form showing 'games' you have a 'subform' showing the platforms for that game. (Entries in the 'game platforms' table) which you can update, adding or subtracting platforms from games as they change availability.

You can also report games for a platform, platforms for a game...

Does that help?

And of course...
welcome2UA.gif

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
GroverParkGeorge
post Apr 4 2019, 12:54 PM
Post#8


UA Admin
Posts: 35,146
Joined: 20-June 02
From: Newcastle, WA


Yes, the ability to update data is always a consideration.

In this case, though, it is less of a concern with a properly normalized table design.

If you have a table with a foreign key to "Game" and a second foreign Key to "Availability", adding or removing any given game from any given platform is as simple as adding or deleting one record for that combination.

It sounds like a complicating factor for you will be importing new records via an Excel worksheet and updating those records accordingly. That's an issue, for sure, but far less of an issue than trying to design a relational database application around a spreadsheet table.

How often do you import this data? How many items change each time you import? Is it a massive change?

The solution is going to be, IMO, importing that spreadsheet into a temporary table in Access from which you can then update/delete the Availability table using queries. Would that work? If so, we can take a whack at sketching out some SQL to get you started.

Thanks.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Canadianskibum
post Apr 4 2019, 01:17 PM
Post#9



Posts: 5
Joined: 3-April 19



Yes this does help, I was thinking 3 tables.

Thanks,
Go to the top of the page
 
Canadianskibum
post Apr 4 2019, 01:35 PM
Post#10



Posts: 5
Joined: 3-April 19



GroverParkGeorge

Some of the data changes daily(unit sales numbers), I dont think I will be updating it daily probably more on a monthly basis. Let me give it a try and thank you all for your suggestions.

Thanks,
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th June 2019 - 12:05 AM