Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2007
    Posts
    40

    Unanswered: Duplicate Coloumn Text IF Text Contains "JOB:*"

    I'm generating invoice reports from raw data inported from our EPOS software into Access. Part of this data is a coloumn called Discription which contains a discprition of a product but is also used to record a job reference.
    A sale may look like this:
    123 1 1/11/09 Thing x1 3.00
    123 2 1/11/09 widget x2 6.00
    123 3 1/11/09 Job: Mr Smith x1 0.00
    (where 123 = recepit number an the following digit = line number)

    A present each sale is sorted by the date. However I'd like to set it up so I can sort if by Job, then date. Obviously just sorting on discription will lose format so all the "Jobs" will be grouped together, then all the "Things" and the all the "Widgets" with no clarity as to what products are for which job.

    Each Job entry has the following format "Job: xxx".

    Can I make a query which looks at the discription coloumn and If it contains the text "JOB:*" then it copies this entry into a new coloumn (job) AND copies that entry to each row which shares the Receipt number? so the query run on the above example would result in:

    123 1 1/11/09 Thing x1 3.00 Job: Mr Smith
    123 2 1/11/09 widget x2 6.00 Job: Mr Smith
    123 3 1/11/09 Job: Mr Smith x1 0.00 Job: Mr Smith
    124 1 1/11/09 a differnet thing x 1 3.00
    125 1 1/11/09 stuff x1 2.00 Job: Works
    125 2 1/11/09 Job: Works x1 0.00 Job: Works

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    first; I see no logic that would connect
    123 1 1/11/09 Thing x1 3.00
    with
    123 3 1/11/09 Job: Mr Smith x1 0.00

    second; you will spend an inordinate amount of time attempting to change db records into a side-by-side format.... I wouldn't bother... I would focus on establishing a common cross reference between those records you consider linked so that you can pull up the records properly grouped...

  3. #3
    Join Date
    Dec 2007
    Posts
    40
    I can't see any logic either - hence my question!

    Let me try and simply the question:
    I have the following information:
    No. Desc
    1 Screw
    1 Job: Car
    2 Bolt
    2 Job: House
    3 Light Bulb
    3 Job: Car
    No. and Desc are two separate columns. How can I sort the information by the job entry while still keeping the no.1 column grouping the same?

  4. #4
    Join Date
    Oct 2009
    Posts
    340
    now the No. column has values that group together records.....this is no problem in terms of grouping feature in an Access Report

    but this wasn't evident in your first example...

    as mentioned before - - I wouldn't bother the time to attempt to get 2 records to appear with their data side-by-side i.e. 1 Screw Job: Car - - that's a pain...

  5. #5
    Join Date
    Dec 2007
    Posts
    40
    OK. So how do I go about achiving that? How can I sort on the Job Entry while still keeping the grouping intact?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Amused View Post
    I can't see any logic either - hence my question!

    Let me try and simply the question:
    I have the following information:
    No. Desc
    1 Screw
    1 Job: Car
    2 Bolt
    2 Job: House
    3 Light Bulb
    3 Job: Car
    No. and Desc are two separate columns. How can I sort the information by the job entry while still keeping the no.1 column grouping the same?
    What, in this, is job entry? And what would you like this to look like once sorted? It reads to me that you want:
    Code:
    ORDER BY 1, 2
    but I can't believe it is that simple.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Oct 2009
    Posts
    340
    as with PF's reply - what is job entry? if it the 1,2,3 value column - - then you can make it even simpler than PF's suggestion...which is simply source your Report on this table/query and make the grouping by the job entry column.... that's it....

  8. #8
    Join Date
    Dec 2007
    Posts
    40
    Quote Originally Posted by pootle flump View Post
    but I can't believe it is that simple.
    If only.

    OK maybe in trying to simplify things I confused the matter.
    The Report I generate might look like the following:

    Code:
    Date		Time	Receipt Line No	Product Description	Qty	Price
    01/11/09	10.12	123	1	Screw			5	0.22
    01/11/09	10.12	123	2	Bolt			1	1
    01/11/09	10.12	123	3	Nut			1	0.15
    01/11/09	10.12	123	4	JOB: House		1	0
    03/11/09	14.43	156	1	Bulb			1	0.5
    03/11/09	14.43	156	2	JOB: Car		1	0
    06/11/09	14.54	187	1	Bracket			1	4
    06/11/09	14.54	187	2	Screws			4	0.2
    06/11/09	14.54	187	3	JOB: House		1	0
    06/11/09	16.32	189	1	Screw			1	0.2
    06/11/09	16.32	189	2	JOB: House		1	0
    09/11/09	9.15	201	1	Fence Panel		3	15
    09/11/09	9.15	201	2	Post			3	9
    09/11/09	9.15	201	3	JOB: Apple tree		1	0
    The key is the product description col. As you can see it CAN contain a "JOB: XXX" entry but usually contains other data, namely the product description. This is how the data comes to me - I can't change that - only treat it.

    I want a way to sort the results by Job entry (when it occurs) while still keeping the Receipt Grouping intact so the treated data would look like this:
    Code:
    Date		Time	Receipt Line No	Product Description	Qty	Price	Job
    09/11/09	9.15	201	1	Fence Panel		3	15	
    09/11/09	9.15	201	2	Post			3	9	
    09/11/09	9.15	201	3	JOB: Apple tree		1	0	JOB: Apple tree	
    03/11/09	14.43	156	1	Bulb			1	0.5	
    03/11/09	14.43	156	2	JOB: Car		1	0	JOB: Car
    01/11/09	10.12	123	1	Screw			5	0.22	
    01/11/09	10.12	123	2	Bolt			1	1	
    01/11/09	10.12	123	3	Nut			1	0.15	
    01/11/09	10.12	123	4	JOB: House		1	0	JOB: House
    06/11/09	14.54	187	1	Bracket			1	4	
    06/11/09	14.54	187	2	Screws			4	0.2
    06/11/09	14.54	187	3	JOB: House		1	0	JOB: House
    06/11/09	16.32	189	1	Screw			1	0.2	
    06/11/09	16.32	189	2	JOB: House		1	0	JOB: House
    (The JOB column is not a mandatory addition, i've merely added it to help illustrate how it's been sorted).

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    looks to me like a symptom of dodgy design
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can there only be one job per receipt group?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Dec 2007
    Posts
    40
    Quote Originally Posted by pootle flump View Post
    Can there only be one job per receipt group?
    Good Point. In theory there should only be one job per group. That said there is nothing to stop it happening by mistake - staff might make a mistake when busy and rushed. How much difference would this make? I could probably manually edit the receipt no on the few occations that would happen if it would make a solution easier.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It's not so much a technical problem as a logical one. We can just use MAX() to get round it.
    Code:
    SELECT  myTable.*
    FROM    myTable
    INNER JOIN
            (
                SELECT  MAX(Iif([Product Description] LIKE "JOB: *", MID([Product Description], 6, LEN([Product Description])), NULL) AS job
                      , Receipt
                FROM    myTable
                GROUP BY Receipt
            ) AS jobs
    ON myTable.Receipt = jobs.Receipt
    ORDER BY jobs.Receipt
           , jobs.Job
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Oct 2009
    Posts
    340
    looks to me that you can group by job receipt or by time....and then order by line number....easily implemented in Reports with no coding at all....

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm assuming it is coincidence that the Job is always the numerically highest Line No in the sample data otherwise this would have been correct:
    Quote Originally Posted by pootle flump View Post
    it is that simple.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Dec 2007
    Posts
    40
    Pootle Flump: look promising. I'll try implementing and see where we get to. Thank you for taking the time.

Posting Permissions

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