Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-18-2012, 05:53 PM   #1
breese
Newly Registered User
 
Join Date: Oct 2012
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
breese is on a distinguished road
Exclamation I need help with my database

I am doing a database containing a roster for a roleplaying group. Of course, the meanings do not matter but I will explain my problem.

Everyone on the Roster has a specific set of experience points that they've earned. I have a table created displaying the levels of experience and the amounts of experience that requires. Now, I need to find out how to match those so that the people's experience can fall into those categories and I can list the " level " of experience on their information.

For example...

Level 1 - 0 Experience
Level 2 - 300 Experience
Level 3 - 600 Experience

and so on, whereas a person might have...

Breese - 450 Experience

Which would label them at level 2.

I am currently taking a Computer Applications Software class that teaches Access, but we have not done this, as this seems like a very specific need and most people probably wouldn't run into it.

Any help is appreciated!

breese is offline   Reply With Quote
Old 10-18-2012, 06:21 PM   #2
Alansidman
AWF VIP
 
Alansidman's Avatar
 
Join Date: Jul 2008
Location: Lake County, IL
Posts: 1,493
Thanks: 40
Thanked 244 Times in 243 Posts
Alansidman will become famous soon enough
Send a message via Skype™ to Alansidman
Re: I need help with my database

Here is a Sql statement for the information you have provided. If you have more levels then you may have to nest more iif statement. If you have a lot, then you will have to do it in VBA with a CASE Select statement or create a call function. Both of these options are a little more advanced

Code:
SELECT Table1.Participant, Table1.Experience, IIf([table1].[experience]>0 And [table1].[experience]<300,"Level 1",IIf([table1].[experience]>299 And [table1].[experience]<600,"Level 2","Level 3")) AS Expr1
FROM Table1;
__________________
Alan


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Win 7 Office XP, 2007, 2010
Alansidman is offline   Reply With Quote
Old 10-18-2012, 06:25 PM   #3
breese
Newly Registered User
 
Join Date: Oct 2012
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
breese is on a distinguished road
Re: I need help with my database

I see. Will I be able to take this and just make it very long and still work? Because I am unaware as to how to do anything else, and yes... there is unfortunately a lot of levels/experience data. It will take some time, and I am willing to put forth the effort.

breese is offline   Reply With Quote
Old 10-18-2012, 06:43 PM   #4
Alansidman
AWF VIP
 
Alansidman's Avatar
 
Join Date: Jul 2008
Location: Lake County, IL
Posts: 1,493
Thanks: 40
Thanked 244 Times in 243 Posts
Alansidman will become famous soon enough
Send a message via Skype™ to Alansidman
Re: I need help with my database

Not sure how many IIFs you can nest. But you are limited to 250 characters in the expression. If it exceeds that then you may want to go the VBA way.
__________________
Alan


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Win 7 Office XP, 2007, 2010
Alansidman is offline   Reply With Quote
Old 10-18-2012, 06:53 PM   #5
breese
Newly Registered User
 
Join Date: Oct 2012
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
breese is on a distinguished road
Re: I need help with my database

I can see how that would be a problem... Yeah there's about 156 I believe... levels, that is. Perhaps I should do a little research elsewhere and see how to do these other methods, even if they are advanced.
breese is offline   Reply With Quote
Old 10-18-2012, 08:26 PM   #6
ByteMyzer
AWF VIP
 
Join Date: May 2004
Location: United States
Posts: 1,385
Thanks: 0
Thanked 48 Times in 43 Posts
ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough
Re: I need help with my database

This can also be accomplished with a second, lookup table, and a query with subquery.

Assume tables like the following:
Code:
Table1
======
Participant | Experience
------------------------
Breese      | 450
Alansidman  | 900

Table2
======
Experience | Level
------------------
0          | 1
300        | 2
600        | 3
You can use the following query:
Code:
SELECT T1.*, (
    SELECT MAX(T2.Level)
    FROM Table2 AS T2
    WHERE T2.Experience <= T1.Experience
    ) AS [Level]
FROM Table1 AS T1;
...which would return a result like this:
Code:
Query1
======
Participant | Experience | Level
--------------------------------
Breese      | 450        | 2
Alansidman  | 900        | 3
ByteMyzer is offline   Reply With Quote
The Following User Says Thank You to ByteMyzer For This Useful Post:
Alansidman (10-19-2012)
Old 10-19-2012, 03:24 AM   #7
Alansidman
AWF VIP
 
Alansidman's Avatar
 
Join Date: Jul 2008
Location: Lake County, IL
Posts: 1,493
Thanks: 40
Thanked 244 Times in 243 Posts
Alansidman will become famous soon enough
Send a message via Skype™ to Alansidman
Re: I need help with my database

@ByteMyzer;
I forgot about a subquery. Thanks for posting that. Much easier than my suggestion for using VBA.

Alan

__________________
Alan


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Win 7 Office XP, 2007, 2010
Alansidman is offline   Reply With Quote
Old 10-19-2012, 04:33 AM   #8
breese
Newly Registered User
 
Join Date: Oct 2012
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
breese is on a distinguished road
Re: I need help with my database

This seems to make sense to me, but it keeps telling me that I have a syntax error somewhere. Perhaps I am not transferring the information very well. Here, I'll give the exact information and see what I'm doing wrong.

1st Table is called Character Information. The things I need from that table are Character First Name, Character Last Name, Character SN, Experience.

2nd Table is called Experience. This table has Dice and Experience.( As every level of experience gets you another dice side/die ) I need their dice amount to show up on the query.

So the query would look like:

Query1
=====
Character First Name | Character Last Name | Character SN | Experience | Dice
Hoobity Blah Blarghy Blargh breese 300 2d31
breese is offline   Reply With Quote
Old 10-19-2012, 04:42 AM   #9
breese
Newly Registered User
 
Join Date: Oct 2012
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
breese is on a distinguished road
Re: I need help with my database

oh, and I also need it to display -every- character from the list. I made another database to test yours out, and of course it works there, but that's because I -made- our example. However, it only posted one name where I had written two out on the database.
breese is offline   Reply With Quote
Old 10-19-2012, 04:51 AM   #10
breese
Newly Registered User
 
Join Date: Oct 2012
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
breese is on a distinguished road
Re: I need help with my database

I was trying to post the pictures of my database so that I can show you guys, but you can't post pictures until you have 10 posts. I only have 7 as of this post, so I'm trying to knock up my posts so I can show you how it looks.
breese is offline   Reply With Quote
Old 10-19-2012, 05:01 AM   #11
breese
Newly Registered User
 
Join Date: Oct 2012
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
breese is on a distinguished road
Re: I need help with my database



breese is offline   Reply With Quote
Old 10-19-2012, 05:35 AM   #12
Alansidman
AWF VIP
 
Alansidman's Avatar
 
Join Date: Jul 2008
Location: Lake County, IL
Posts: 1,493
Thanks: 40
Thanked 244 Times in 243 Posts
Alansidman will become famous soon enough
Send a message via Skype™ to Alansidman
Re: I need help with my database

Instead of posting pictures and having us recreate your db, why not post a copy of your db, so we can work with the data and not have to recreate it. Do a compact and repair before compacting (zipping) your file for input.
__________________
Alan


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Win 7 Office XP, 2007, 2010
Alansidman is offline   Reply With Quote
Old 10-19-2012, 05:48 AM   #13
breese
Newly Registered User
 
Join Date: Oct 2012
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
breese is on a distinguished road
Re: I need help with my database

Alright, here's a zip file containing my database with all the information in it.
Attached Files
File Type: zip KoM Database.zip (28.8 KB, 35 views)
breese is offline   Reply With Quote
Old 10-19-2012, 06:32 AM   #14
Alansidman
AWF VIP
 
Alansidman's Avatar
 
Join Date: Jul 2008
Location: Lake County, IL
Posts: 1,493
Thanks: 40
Thanked 244 Times in 243 Posts
Alansidman will become famous soon enough
Send a message via Skype™ to Alansidman
Re: I need help with my database

Examining your tables, you have a data mismatch on the experience field. In one table you have it listed as a number (long integer) and in the other table, you have it listed as a text field. For these to be joined and return valid results, you need to make sure that they are formatted the same. In this case, they should be numbers. Because of this issue, I have not taken this any further.
__________________
Alan


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Win 7 Office XP, 2007, 2010
Alansidman is offline   Reply With Quote
Old 10-19-2012, 06:52 AM   #15
Alansidman
AWF VIP
 
Alansidman's Avatar
 
Join Date: Jul 2008
Location: Lake County, IL
Posts: 1,493
Thanks: 40
Thanked 244 Times in 243 Posts
Alansidman will become famous soon enough
Send a message via Skype™ to Alansidman
Re: I need help with my database

I think I got it now. Look at the attached.
Attached Files
File Type: zip KoM Database.zip (30.9 KB, 29 views)

__________________
Alan


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Win 7 Office XP, 2007, 2010
Alansidman 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
Question Database connection in excel to get data from password protected Access database AccessToAcess General 2 08-06-2012 06:40 AM
Copy table from one database to another(current) database with data, indexes and keys ramindya Modules & VBA 1 05-17-2012 12:09 AM
Code connect to database and query select data from database in Ms Access 2007 veasna Queries 2 11-29-2010 12:25 PM
Access Database command button to open external database ttbeverley Modules & VBA 2 12-14-2009 08:32 PM
Moving selected records from source database to archive database rk1_23 Tables 1 06-06-2008 09:53 PM




All times are GMT -8. The time now is 07:09 AM.


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