Unique Letter Code For Each Row.....

[anilytics]

Hey Guys n Girls.....

Ok so far I am developing an Access Runtime application for an international magazine firm.

The trouble I am having is the following scenario:

I have a table call [tbl_lists] which has a PK and FK linked to a main table. And a table that is [tbl_rep_code] this table has a column with each row having a unique letter example: Row 1 - 'A', Row 2 - 'B'..... till it ends at 'ZZ'. Just like all the column titles in Excel.

A user would journey through the forms the main form would build the initial campaign. This would give that campaign a unique CAMPID which is stored in 'tbl_campaigns'. The 'CAMPID' is passed through to the Form Lists. On this form the user can enter X amount of lists to be assigned to the CAMPID.



This is all in [tbl_lists] which has 'LISTID' as PK and 'CAMPID' as FK. For each record they enter they also need to give it a rep code letter the first would be 'A' then 'B' then so on. Some lists can be only 1 record other campaigns can be 50 which would mean they will be using 'A' - 'AZ'.

However I want a combo box in my unbound form that will basically intitally allow the user to choose a letter. When they click on ADD that record is then sent to tbl_lists. Afterwards if they want to assign another list record they will cannot see 'A' the next they can choose is 'B'.

But if the user needs to delete a list they can do so this would mean that for example 'A' will become available again to use next time.

Please bare in my mind that the list is linked to CAMPID as well. So when a new user creates another campaign there lists will have that new CAMPID which would mean that the new CAMPID will now get a fresh list of all the rep codes again from 'A' - 'ZZ'.


But i am really stuck in getting the logic right.... when i write down the logic i think of joining tables in queries but end up not working.

If its VBA i need to use am happy to do this. I just cannot name what this sort of logic is called.

Your help will be appreciated.

Anil

[June7]

You want user to have choices of rep codes not already used for a CAMPID? This will mean a combobox with a RowSource that joins the two tables. Use an outer (left or right) join, like 'select all records from tbl_rep_code and only those from tblLists ...'

Then think something like:
SELECT DISTINCT Rep_Code FROM tbl_rep_code (RIGHT or LEFT, I always forget which is which way) JOIN ON tbl_rep_code.rep_code = tbl_Lists.rep_code WHERE tbl_lists.rep_code Is Null AND CAMPID = Forms!formname![CampID];

[anilytics]

Thanks for the help.... So I managed to build a query in the form for that combo box.

Here is the full SQL Query:

SELECT (SELECT DISTINCT tbl_response_code_letter.RespCodeLetter FROM (tbl_response_code_letter INNER JOIN tbl_lists ON tbl_response_code_letter.respcodeletter = tbl_Lists.respcode) WHERE tbl_lists.respcode Is Null AND CAMPID = [Forms]![NCAMP_03_MJ_SOL]![CampID1]) AS [Rep Code]
FROM tbl_response_code_letter LEFT JOIN tbl_lists ON tbl_response_code_letter.RespCodeLetter = tbl_lists.RespCode;


When I run the form which has the campid assigned. When I choose the Resp Code Letter COMBO BOX it does drop down but with 682 blank records. As its a new Campaign it should only be showing 676 records. I believe what that 682 is made up off is its also listing repcodes used from previous campid's.

But How do i resolve the blank records? And how to make a new list of rep codes again?

[orange]

Can you show a picture (jpg) of your tables and relationships?

[anilytics]

Find attached the full relationship of my DB and also the relationship and SQL code for the COMBO Box.....

[anilytics]

Also find a picture of the Response Codes I need listing....

[June7]

I was way off on my original suggestion, didn't see need for subquery but is so obvious now. This worked for me:

SELECT RespCodeLetter FROM
(SELECT RespCode FROM tbl_Lists WHERE CampID=[Forms]![NCAMP_03_MJ_SOL]![CampID1]) As Query1
RIGHT JOIN tbl_response_code_letter ON Query1.RespCode = tbl_response_code_letter.RespCodeLetter
WHERE Query1.RespCode Is Null;

[anilytics]

I was way off on my original suggestion, didn't see need for subquery but is so obvious now. This worked for me:

SELECT RespCodeLetter FROM
(SELECT RespCode FROM tbl_Lists WHERE CampID=[Forms]![NCAMP_03_MJ_SOL]![CampID1]) As Query1
RIGHT JOIN tbl_response_code_letter ON Query1.RespCode = tbl_response_code_letter.RespCodeLetter
WHERE Query1.RespCode Is Null;

Awesome! That worked and I have tested my form and it does not allow the user to select the same code unless deleted.

Thank you so much really appreciate your support.