combining notsosimilar fields

[kstoker]

I have two tables I need to join.

Table 1
EmpNumber | Privilege1 | Privilege2 | Privilege3 | Privilege4

Table 2
EmpNumber | Privilege


In Table 1, EmpNumber is a unique primary key, in Table 2, there is no primary key. In Table 2, EmpNumber is repeated for each Privilege the employee has.

So a data set looks similar to this:

Table 1 (what it SHOULD look like)
EmpNumber | Privilege1 | Privilege2 | Privilege3 | Privilege4
001 | Y | N | Y | Y
002 | N | N | Y | N
003 | Y | N | Y | N

Table 2
EmpNumber | Privilege
001 | 1
001 | 3
001 | 4
002 | 3
003 | 1
003 | 3

I have Table 2. It's a dump from another program. I'd like the information to look as similar to Table 1 as possible, yet every which way I attempt, I get a type mismatch error. I understand that one field is numeric and the other is boolean, so yes, it is a type mismatch error. I'm having a major head case with this... it shouldn't be as difficult as I'm making it out to be, but I can't seem to wrap my head around it! Can ya'll help??

 

[Scooterbug]

Well, you could write a query that uses IIF statements.

Privilage1: iif([Privilage]=1,"Y","N")
Privilage2: iif([Privilage]=2,"Y","N")
Privilage3: iif([Privilage]=3,"Y","N")
Privilage4: iif([Privilage]=4,"Y","N")

 

[kstoker]

Well, you could write a query that uses IIF statements.

Privilage1: iif([Privilage]=1,"Y","N")
Privilage2: iif([Privilage]=2,"Y","N")
Privilage3: iif([Privilage]=3,"Y","N")
Privilage4: iif([Privilage]=4,"Y","N")

So how would I accomplish this? Can I incorporate this into my Table 1? Set a conditional that says if(Table 1.EmpNumber = Table 2.EmpNumber AND Priviledge = 1, "Y", "N")?

Will that go through the entire table and find all instances?

 

[Scooterbug]

So how would I accomplish this? Can I incorporate this into my Table 1? Set a conditional that says if(Table 1.EmpNumber = Table 2.EmpNumber AND Priviledge = 1, "Y", "N")?

Will that go through the entire table and find all instances?

I misread your orginally post....

The query with the IIF statements shows the permissions, but doesn't put it in the format you wanted. The problem here is that table2 is normalized and table1 isn't. That's where your difficulties are being encountered.

To get it to look like Table1, you are going to have to setup a non-normalized table and dump the info from Table 2 into it.

 

[kstoker]

ok, so how would I go about normalizing it? if I am to import this table from a flat file, how would I make sure that it is normalized?

I can create a new table and add an autonumber on it, I still don't see how that'd help me.

 

[Scooterbug]

The way you have table 2 setup would be the proper normalized way. Setting it up to look like table 1....well that's where the problem comes in. Me personally, I would have a listbox on the employee record and just list the privilages that they have. Or are you trying to display the data like table 1 for a report?

 

[kstoker]

Because they can have more than one privilege (and most all do) a listbox isn't very intuative for this.

I'm trying to mimic a form that's hand written/filled out to make data entry easy. I've accomplished that with Table1.

However, trying to import the data (and have it so that the data is imported regularly) gives me issues. The data that I'm attempting to import is stored in a table that mimics Table2.

I need a fix to this in any way at any cost. I can deal with re-occuring imports later, I need to get the information in the database as it is now.

 

[Scooterbug]

Well, using a bunch of queries, I have recreated the look that you want. I've attached a db to illustrate. The data is still in the Table2 format, but there is a query (Query1) that emulates the look of Table1.

 

[kstoker]

ok, so I managed to get the solution that I needed. I created two queries for each Privilege. One to change it from a number to a text field "True" or "False" then another to change it from text to a boolean yes/no (checkbox).

That gets me where I need for the privileges.

However... now I have another problem.

I have an Account_Number that has previously been assigned manually with a few conditions.

If they're members of Department 1, their Account_Number starts with the next available account number in the 10000 - 19999 range.
If they're members of Department 2, their Account_Number starts with the next available account number in the 20000 - 29999 range.

We've decided that doesn't really benefit us so we're going to a more normalized "lowest account_number available" assignment. So how do I create an auto-incrementing number/integer field that will take the lowest available number that is unique and not already in the database?