Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2009
    Posts
    32

    Question Unanswered: How to create conditional expressions (maybe I used wrong term?)

    Hi guys, I need help. I'm not too familiar with the various SQL terms and all so please forgive me if I don't say it right Let me start by explaining what I'm trying to achieve.

    I'm trying to create a tracking system whereby all outgoing and incoming documents are matched. The problem is that outgoing documents (I'll use an invoice book as example) run in sequence, say each book contains 10 invoices. So when we issue one IV book to a staff, that is 10 invoice sheets are given to him. But when they submit these back, it is returned one-by-one. So what I'm trying to do is to match what is returned and what is not.

    Anyway, this is what I've done so far.

    Code:
    Book	AgentID	CI1	CI2	CI3	CI4	CI5	CI6	CI7	CI8	CI9	CI10
    0001	AB01	000001	000002	000003	000004	000005	000006	000007	000008	000009	000010
    0002	AB02	000011	000012	000013	000014	000015	000016	000017	000018	000019	000020
    0003	AB02	000021	000022	000023	000024	000025	000026	000027	000028	000029	000030
    0004	AB03	000031	000032	000033	000034	000035	000036	000037	000038	000039	000040
    0005	AB04	000041	000042	000043	000044	000045	000046	000047	000048	000049	000050
    So I've created a field for the unique Book #, the Agent ID and the individual invoice numbers for each book (these are autogenerated based on the formula, errr expression : [Book]*10-X where X=9,8,7...0

    Next, there will be a separate entry whereby we key-in all the returned invoices manually. These will appear in a separate Table. What I want is that whenever any of these outgoing invoice numbers find a match in the returned Table, these will be removed from display. Example, if the CI numbers 000001 - 7 and 000009 are returned, I'd like the above display to show this:

    Code:
    Book	AgentID	CI1	CI2	CI3	CI4	CI5	CI6	CI7	CI8	CI9	CI10
    0001	AB01								000008		000010
    0002	AB02	000011	000012	000013	000014	000015	000016	000017	000018	000019	000020
    0003	AB02	000021	000022	000023	000024	000025	000026	000027	000028	000029	000030
    0004	AB03	000031	000032	000033	000034	000035	000036	000037	000038	000039	000040
    0005	AB04	000041	000042	000043	000044	000045	000046	000047	000048	000049	000050
    Reason is that we're interested to track those unreturned ones only. When I tried to create the script, it either entirely hides the row or column, which is not what I want (okay, if all 10 sheets are returned, I'd want to not show the whole row).

    I don't know how far off I am, this is the code that I have right now (which doesn't actually do anything unfortunately):

    Code:
    SELECT CI_expand.Book, CI_expand.AgentID, CI_expand.CI1, CI_expand.CI2, CI_expand.CI3, CI_expand.CI4, CI_expand.CI5, CI_expand.CI6, CI_expand.CI7, CI_expand.CI8, CI_expand.CI9, CI_expand.CI10
    FROM CI_expand LEFT JOIN KeyIn ON (CI_expand.CI10 = KeyIn.CI) AND (CI_expand.CI9 = KeyIn.CI) AND (CI_expand.CI8 = KeyIn.CI) AND (CI_expand.CI7 = KeyIn.CI) AND (CI_expand.CI6 = KeyIn.CI) AND (CI_expand.CI5 = KeyIn.CI) AND (CI_expand.CI4 = KeyIn.CI) AND (CI_expand.CI3 = KeyIn.CI) AND (CI_expand.CI2 = KeyIn.CI) AND (CI_expand.CI1 = KeyIn.CI)
    WHERE (((KeyIn.CI) Is Null));
    FYI: The outgoing CI are split into 10 columns (CI_expand.Cxx) but each column will be matched against a single column (KeyIn.CI). I realise that if I used a single column that would be easy as hell, but it would take my guys helluva time to record 10 numbers per 1 book -- besides, I'd like to eliminate errors.

    Please help me, whoever has the time to read through my lengthy request. I'll bet the solution is really simple XD Thank you

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are you open to a table redesign? Because as usual with this sort of design you are swimming against the current. Your FYI hints that you know what the design should be - there are ways to simplify the entry.

    Otherwise (and I firmly believe this masks a problem rather than is a solution):
    Code:
    SELECT book, iif(a.ci1 <> b.ci1, a.ci1) AS ci1, iif(a.ci2 <> b.ci2, a.ci2) as ci2 .....
    FROM a
    INNER JOIN
    b
    ON a.book = b.book
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2009
    Posts
    32

    Red face

    Quote Originally Posted by pootle flump
    Are you open to a table redesign? Because as usual with this sort of design you are swimming against the current. Your FYI hints that you know what the design should be - there are ways to simplify the entry.
    Well, I'm open to any suggestions, but the thing is the data entry ppl aren't exactly wizards so I want to make the entry as simple as possible for them. *points to 1st post* But sure, if you think that my design should be changed for the sake of easy scripting, I'm all ears.

    Btw, I tried your suggestion and it kinda didn't work. Maybe I substituted the scripts wrong, would you please go through for me?

    I'll quickly explain the names of my tables/queries/columns:
    - CI_expand = query with the 10 columns (simplified to 3 in this case)
    - KeyIn = table with all the returned CI numbers
    - CI_expand.Book = the unique identifier Book

    Code:
    SELECT CI_expand.Book, iif(CI_expand.CI1 <> KeyIn.CI, CI_expand.CI1) AS CI1, iif(CI_expand.CI2 <> KeyIn.CI, CI_expand.CI2) AS CI2, iif(CI_expand.CI3 <> KeyIn.CI, CI_expand.CI3) AS CI3
    FROM CI_expand
    INNER JOIN
    KeyIn
    ON CI_expand.CI1 = KeyIn.CI;
    As you can see, I'm practically clueless on what I have just done

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by andi_kan
    I'm practically clueless on what I have just done
    Me too - I don't know what "kinda didn't work" means.

    The only think I can see is a change that looked wrong:
    Code:
    SELECT CI_expand.Book, iif(CI_expand.CI1 <> KeyIn.CI, CI_expand.CI1) AS CI1, iif(CI_expand.CI2 <> KeyIn.CI, CI_expand.CI2) AS CI2, iif(CI_expand.CI3 <> KeyIn.CI, CI_expand.CI3) AS CI3
    FROM CI_expand
    INNER JOIN
    KeyIn
    ON CI_expand.book = KeyIn.book
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2009
    Posts
    32
    Ooops my bad. "Kinda didn't work" means that when I used that script, it just shows me a completely blank database. No errors, meaning it must have filtered everything right through.

    On the part you corrected... err, I do not have a column called Book in the KeyIn table.

    Now I have an alternative design... suppose I split the returning entries to 10 columns as well (which I can do albeit with a little more time), would that greatly simplify the coding?

    Question... Is it even possible for the database to selectively hide an RC (row, column) entry instead of filtering an entire column or row? Because I'm assuming it can, but if there is no such thing then I might as well abandon this idea =(

  6. #6
    Join Date
    Jul 2009
    Posts
    9

    Smile Use a query?

    First you are using MS Access? I recreated your problem in a database and this is how I got it to work. Create a query in design using the two tables, Issued and Returned link the two tables and change the join properties to 2.
    Issued
    Book_____Agent ID____CTI____CT2
    1________01__________01______01
    2________02__________02______02
    3________03__________03______03
    4________04__________04______04

    Returned
    Book_____Agent ID________CTI______CT2
    1_________01________________________
    2_________02_____________02_________
    3_________03_____________03______03

    Query
    Book_____Agent ID________CT1_____CT2
    1________01_____________01_______01
    2________02_____________02_________
    4________04_____________04_______04

    Field
    Book: IIf([Issued]![CTI]=[Returned]![CTI] And [Issued]![CT2]=[Returned]![CT2],Null,[Issued]![Book])
    Type this in the Criteria Is not null
    AgentID: IIf([Issued]![CTI]=[Returned]![CTI] And [Issued]![CT2]=[Returned]![CT2],Null,[Issued]![AgentID])
    CT1: IIf([Issued]![CTI]=[Returned]![CTI],Null,[Issued]![CTI])
    CT2 : IIf([Issued]![CT2]=[Returned]![CT2],Null,[Issued]![CT2])

    You will have to expand on this for more columns
    Last edited by Tom Neely; 07-08-09 at 16:14.

  7. #7
    Join Date
    Jul 2009
    Posts
    32
    Quote Originally Posted by Tom Neely
    First you are using MS Access? I recreated your problem in a database and this is how I got it to work. Create a query in design using the two tables, Issued and Returned link the two tables and change the join properties to 2.
    Yes, I was using MS Access. Appreciate your help, though I'm a little confused atm so I'll slowly digest it. But the problem as I mentioned was that the Returned table will be all in single column... there will be no CT1 and CT2, it's all just CT and there will be "Book" nor "Agent" next to it, just plainly 2 columns for Date and CT. Anyhow, by briefly looking at your suggestion, I think it might be OK to swap the CT1/CT2 reference for CT and I'll see what the result is like.

    Thank you all so far. Btw, I managed to get this thing done in MS Excel yesterday, but I dislike using Excel for this purpose. If anyone would like to contribute, I'll show the exact requirement in Excel format and you can see how it can be converted to Access? THANKS

  8. #8
    Join Date
    Jul 2009
    Posts
    9
    I would be willing to look at

  9. #9
    Join Date
    Jul 2009
    Posts
    32
    Thanks Tom. But since I can't attach Excel files, I'll just attach a screenshot (2 in fact) of my file. Not much there, just 3 formulae of note:

    1) Automate the generation of 10 CI numbers based on the book number;
    2) Search for an exact match of each CI number against the entries in another tab -- if found, hide the value; at the same time, highlight the outstanding ones;
    3) Automatically alternate status between "complete" and "outstanding" based on the number of CI returned.

    And then there's the filtering option to select which salesperson we want to monitor.

    I've managed to implement it semi-perfectly on Excel but the actual "KeyIn" data run into tens of thousands of entries... not something that Excel is comfortable working with therefore it's a matter of time before I need to switch. Besides, we are using MySQL for other functions so eventually we want this integrated with our main database too. (I don't want to divert my time monitoring this.)


    The rest of this has nothing to do with the creation process -- so you may skip it -- but explains the reason behind why I'm doing all this.

    In this example, there are a few things to highlight. First, CI 000013 is not returned and hence highlighted. Therefore, it raises the query on why CI 000014/15 were used out of order... perhaps 13 was missing, perhaps void, perhaps it's just superstition whatever it is, it is there for us to call to find out why.

    Second, AB02 has not completely used up his first 10 CI, why did he begin on the second book (000021). Did he have a partner and they each take one book? Another question.

    Thirdly, AB01 has finished his first book, thus it was right for us to issue him a second book (book no 5). Conversely, AB02 and AB03 still have remaining inventory with them thus their request for more CI will be denied.

    So that's the short version of why I'm setting this all up. In time there will be more reports to be based on these, such as the frequency of usage, breakdown by month etc. But no point talking all that until I get this sorted out.Click image for larger version. 

Name:	Test_CI.gif 
Views:	29 
Size:	79.0 KB 
ID:	9807

  10. #10
    Join Date
    Jul 2009
    Posts
    9

    Smile Try this

    Let me see if I understand what you have and what you want. Books and document number are created in one table and when the documents are returned they are entered in another table by the Date, Agent ID and CI only. Using those two table you want to do some comparisons and counts?
    If this is the case I would use the following code. This puts the issued data into a 4 column format, Book, Agent ID, Date Issued, and CI.
    SELECT Issued.Book, Issued.AgentID, Issued.Date_Issued, Issued.CI1
    FROM Issued
    UNION
    SELECT Issued.Book, Issued.AgentID, Issued.Date_Issued, Issued.CI2
    FROM Issued
    UNION
    SELECT Issued.Book, Issued.AgentID, Issued.Date_Issued, Issued.CI3
    FROM Issued
    UNION
    SELECT Issued.Book, Issued.AgentID, Issued.Date_Issued, Issued.CI4
    FROM Issued
    UNION
    SELECT Issued.Book, Issued.AgentID, Issued.Date_Issued, Issued.CI5
    FROM Issued
    UNION
    SELECT Issued.Book, Issued.AgentID, Issued.Date_Issued, Issued.CI6
    FROM Issued
    UNION
    SELECT Issued.Book, Issued.AgentID, Issued.Date_Issued, Issued.CI7
    FROM Issued
    UNION
    SELECT Issued.Book, Issued.AgentID, Issued.Date_Issued, Issued.CI8
    FROM Issued
    UNION
    SELECT Issued.Book, Issued.AgentID, Issued.Date_Issued, Issued.CI9
    FROM Issued
    UNION
    SELECT Issued.Book, Issued.AgentID, Issued.Date_Issued, Issued.CI10
    FROM Issued;

    Create a new query using the above as input. The returned table has Date and Returned, in the next query link the two and add a record count, will use that later.
    SELECT Issued_B.Book, Issued_B.AgentID, Issued_B.Date_Issued, Issued_B.CI, Returned.Date_Returned, Returned.CI, 1 AS [Count]
    FROM Issued_B LEFT JOIN Returned ON Issued_B.CI = Returned.CI;Outstanding
    From her you should be able to get what you want

  11. #11
    Join Date
    Jul 2009
    Posts
    32
    Hi all,

    Thanks for all who took the time to help me. I've finally managed to find a way to get what I needed -- though the coding's definitely not pretty, but at least I found it functional. If anyone could improvise on it, please share with me =)

    Anyhow, for those interested, here's my breakdown on how it was done.

    1) Table for Outgoing/Issuance

    Consists of the following fields: Date, Agent, BookNo


    2) Table for Incoming/Return

    I did it first in MS Excel to get the formula. I'm sure I could have The raw data consists of these fields: Date, Agent, InvoiceNo
    Additionally, MS Excel would automatically generate for me the following fields:
    - BookNo (based on InvoiceNo)
    - Ref (an identifier of the final digit of the InvoiceNo)
    - InvoiceNo (10 separate columns... only the respective column will have entry)

    Basically, this table rougly looks like this:

    Code:
    Date		Agent	CItext	BookNo	Ref	1	2	3	4	5	6	7	8	9	0
    09/01/2008	DC	000009	0001	9									000009	
    09/01/2008	AN04	000010	0001	0										000010
    10/01/2008	AN04	000011	0002	1	000011									
    10/01/2008	AN04	000012	0002	2		000012								
    10/01/2008	AN04	000013	0002	3			000013							
    10/01/2008	AN04	000016	0002	6						000016				
    11/01/2008	AN04	000014	0002	4				000014						
    11/01/2008	AN04	000015	0002	5					000015					
    16/01/2008	AC01	000017	0002	7							000017			
    21/01/2008	DC	000018	0002	8								000018		
    21/01/2008	DC	000019	0002	9									000019	
    21/01/2008	DC	000020	0002	0										000020

    3) Query to summarise by BookNo

    The desired output based on the above will be as follows:

    Code:
    BookNo	Returned	1	2	3	4	5	6	7	8	9	0
    0001	2										1	1
    0002	10		1	1	1	1	1	1	1	1	1	1
    And to my great surprise, the code was in fact quite simple. Using a Crosstab Query, this is the SQL code:

    Code:
    TRANSFORM Count(LT_in.Date) AS CountOfDate
    SELECT LT_in.BookNo, Count(LT_in.Date) AS Returned
    FROM LT_in
    GROUP BY LT_in.BookNo
    PIVOT Format([LT_in].Ref,0) In (1,2,3,4,5,6,7,8,9,0);

    Hope someone will find this useful.
    -andi_kan
    Financial guy by trade, software tinkering by hobby

    PC : Intel(R) Core(TM) i3-3110M CPU @ 2.40GHz, 4GB RAM
    Windows 7 Professional 32-bit SP1
    MS Office 2010

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •