I need help with my database (1 Viewer)

[breese]

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!

 

[Alansidman]

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

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;

 

[breese]

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.

 

[Alansidman]

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.

 

[breese]

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.

 

[ByteMyzer]

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

Assume tables like the following:

Table1
======
Participant | Experience
------------------------
Breese      | 450
Alansidman  | 900

Table2
======
Experience | Level
------------------
0          | 1
300        | 2
600        | 3

You can use the following query:

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:

Query1
======
Participant | Experience | Level
--------------------------------
Breese      | 450        | 2
Alansidman  | 900        | 3

 

[Alansidman]

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

Alan

 

[breese]

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]

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]

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]

 

[Alansidman]

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.

 

[breese]

Alright, here's a zip file containing my database with all the information in it.

 

[Alansidman]

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.

 

[Alansidman]

I think I got it now. Look at the attached.

 

[breese]

Yes, that's exactly what I needed, Alan. Thanks a lot. ^_^