Results 1 to 13 of 13

Thread: Sorting problem

  1. #1
    Join Date
    Dec 2007
    Posts
    40

    Question Unanswered: Sorting problem

    I've a query that might return something like this:

    Code:
    Till  	Receipt Date 	Time 	Desc 	  	Job 	
    1	1234	1/1/10	12.04	Paint		
    1	1234	1/1/10	12.04	Brush	
    1	1234	1/1/10	12.04	Job Mr Smith 	Job Mr Smith
    1	1267	1/1/10	14.32	Turps
    1	1267	1/1/10	14.32	Job Mr Smith 	Job Mr Smith
    2	986	2/1/10	9.26	Job 12 East St.	Job 12 East St.
    2	986	2/1/10	9.26	Nails
    2	986	2/1/10	9.26	Screws
    There are many other columns but this lists the relevant ones. At present the results are sorted by date, time then receipt.

    I need a way of first sorting by the Job (where it occurs) but still keeping the receipt grouping intact. I'm stuck as to how to achieve this. Just sorting by Job obviously separates the job from the products to which it applies.

    Can anyone suggest a way of treating this, either in the query or in the report based on the query, so I can sort by "Job" and also bring the other entries for that receipt with it (ie. 12 East St. first with the Nails & Screws listed with it, then the receipts for Mr Smith and so on)? In the example a result like this:
    Code:
    Till  	Recepit Date 	Time 	Desc 	  	Job 	
    2	986	2/1/10	9.26	Job 12 East St.	Job 12 East St.
    2	986	2/1/10	9.26	Nails
    2	986	2/1/10	9.26	Screws
    1	1234	1/1/10	12.04	Paint		
    1	1234	1/1/10	12.04	Brush	
    1	1234	1/1/10	12.04	Job Mr Smith 	Job Mr Smith
    1	1267	1/1/10	14.32	Turps
    1	1267	1/1/10	14.32	Job Mr Smith 	Job Mr Smith
    Can I then still sort each of these JOB Groups by date, time, receipt?

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    well you are lacking logic; the Job field is frequently null; there is no data that defines the null records to a specific Job record.

    your Job field is better named JobDescription - and then you need a Job field with a unique Identifier that is never null.

    for the correct relational approach you should have a JobsTable - with the Job ID and Job Descriptor....and then this table you show here would be JobsDetails; and there would be also the JobID field that serves as a cross reference.......then your sort is simple because you would use a form/subform (or report/subreport) - - this is the way it is suppose to be done.

  3. #3
    Join Date
    Dec 2007
    Posts
    40
    Quote Originally Posted by NTC View Post
    for the correct relational approach...
    All well an good but since the data comes from a heap of junk DOS based EPOS program - and I'm not forking out 4000 for new software just so I can generate invoices by Job Reference - I've abandoned the Correct relational approach!

    For example I can't implement a Job Table since the list of Jobs isn't finite - they get imported with the rest of the data every time I do an invoice run and can include whole new job entries. I can only treat the data, not change the way it's organised or stored.

    Surely there must be a way of just binding the receipt numbers and Job entry together so the grouping stays intact on a job sort? If the Job entries which are NULL is a problem can I not duplicate the Job entries I DO have for them?

    Something like:

    JOB:IIf([DESC]= LIKE "Job:*", [DESC], ([DESC] WHERE [RECIEPT]=[RECIEPT] AND [DESC] CONTAINS LIKE "Job:*")
    I know that's not anything like propper code. Just an example of something that will Duplicate the JOB entry in those Null cells where the receipt matches the cell WITH a job entry?

    I'm not after the correct approach - just a bodge that'll let me do what I need to do.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can create a virtual job relation and use that. Since this is a relational database you have to respect those rules even if (for whatever reason) you won't\ can't persist the data.

    Without your SQL I can't give exact code, but you can create a Derived Table and join that. Something like:
    Code:
    SELECT *
    FROM yourExistingQuery
    INNER JOIN
    (SELECT Receipt, Job
    FROM yourTable
    WHERE Job IS NOT NULL) AS jobs
    ON yourExistingQuery.Receipt = jobs.Receipt
    ORDER BY jobs.Job, <other columns>
    Note this won't give your exact results but I don't understand your exact requirements.

    HTH

    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2007
    Posts
    40
    Sorry if my exact requirements are too vauge. I'm painfully aware I lack even the vocabulary to describe the problem - I've spend hours searching the net and have got myself thoughourly frustrated. I'm very grateful for the help so far.

    Up to now I've been working on the assumption that I can "Link" the Job entry with all other rows which share the Receipt no. Maybe this approach is wrong - it never occured to me the Null entries for Job are a problem.

    In essence I have DBASE data imported into Access which lists every sale made by three tills - each file being about 20MB.

    My queries then returns a list similar to my example for matches on a specified CUSTOMER code and between two DATES (Start and End). This is then made into a report, totalled VAT worked out etc. For most customers simply listing the items taken choronolocally is enough.

    Some customers - namely trade clients - have a job entry added to the lists of goods taken (This is under "PRINT_DESC" column normally listing the product description since that's the only place it can be entered on the till). The Job Col is obtained by:
    Job:Iif([Print_Desc] LIKE "Job:*", Print Desc, "")

    These customers need all the items taken grouped together by Job, with this subset then sorted chronologcally. I've attached a zip file (Excel format) of the sort of result I get from my Query at present if this'll help at all.

    Does this better convey what I'm trying to achieve? Also I'm not nessesarily fussed by the separate Job Table if there is a simpler way.
    Attached Files Attached Files

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    As a quickie, it looks like just order by receipt first, then date and time will get your expected results. I suspect this is wrong, so could you explain why it is wrong?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Oct 2009
    Posts
    340
    if the receipt value is unique to the Job - then you are in good shape.

    just copy that table and name it JobMain

    then remove all fields except Receipt and Job - so it is 2 fields

    and then make a query on it so the Job field does not contain nulls....that gives you a master list of unique jobs with description....

    you can then do the traditional form/subform with these two tables...

  8. #8
    Join Date
    Dec 2007
    Posts
    40
    Quote Originally Posted by pootle flump View Post
    As a quickie, it looks like just order by receipt first, then date and time will get your expected results. I suspect this is wrong, so could you explain why it is wrong?
    Ordering by Receipt first will essentially list sales in chronological order - there will be no order to the Job Entries.
    I need it ordered alphabetically on the Job Field - THEN by receipt, date, time.

    Quote Originally Posted by NTC View Post
    just copy that table and name it JobMain

    then remove all fields except Receipt and Job - so it is 2 fields...
    After much frustrating playing about I've progressed further, but not all the way!

    I now have a second query (JobMain) with this:

    JOB: IIf([PRINT_DESC] Like "JOB:*",[PRINT_DESC],"")
    Criteria: Like "JOB*"

    RECEIPT (which is from the same tables as my main Sales Query

    ---------------------------------------------

    This returns just the unique Receipt value and any Print_Desc which contains a Job entry BUT it turns out not all receipt numbers correspond with a Job Entry = some Recipts DON'T have a Job!

    What I now need is some way of taking the JOB entry from JobMain that matches the Receipt entry but also displays some text if a Receipt doesn't have a matching entry in JobMain.

    In my main Sales Query I've tried:
    Job: SELECT Job FROM JobMain WHERE JobMain.Receipt = [RECEIPT]
    (which just kicks up a syntax error)

    Job: SELECT Job FROM JobMain WHERE JobMain.Receipt = Sale.RECEIPT (Syntax again)
    and

    Job: IIf (Sale.Receipt = JobMain.Receipt, JobMain.Job, "Job entry not given")
    which asks for a Parameter for Sales.Receipt (Sales being the name of the query we're working in)

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That was a long quickie

    This is quite some time ago now. I wanted to just check that it was a coincidence that the Receipt and Job were in the same order in your data.

    Could you quickly run this and see how it goes:
    Code:
    SELECT *
    FROM yourExistingQuery
    INNER JOIN
    (SELECT Receipt, Job
    FROM yourTable
    WHERE Job IS NOT NULL) AS jobs
    ON yourExistingQuery.Receipt = jobs.Receipt
    ORDER BY jobs.Job, jobs.receipt, yourExistingQuery.date, yourExistingQuery.time

    You absolutely can't get your results without a join (or something similar to a join like a subquery). No number of Iif()s will do it for you.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, can you post your excel file as Access if the above fails?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Dec 2007
    Posts
    40
    I'd spent that whole day(!) banging my head against that brick wall so decided to give it a break. The trouble with breaks is it's very hard to motivate your self to end them!

    Anyway, I gave the code a go but probably made a simple error somewhere because

    Code:
    Mircosoft Jet database engine cannot find the input table or query 'SELECT Receipt, Job FROM JobMain WHERE Job is Not NUll;
    The exact coding of the new query is:
    Code:
    SELECT *
    FROM SALES 
    INNER JOIN 
    [SELECT Receipt, Job FROM JobMain WHERE Job IS NOT NULL; ] AS Jobs ON Sales.Receipt=jobs.Receipt
    ORDER BY jobs.Job, jobs.receipt, Sales.date, Sales.time;
    Please note: I entered the code with parenteis () and saved the query but ACCESS changes these to Square Brackets [] everytime I save n close!

    I can't refer to "yourTable" since the source table has no Job column - I can only get the Job column by using a second query on the same source table(s)

  12. #12
    Join Date
    Dec 2007
    Posts
    40
    Quote Originally Posted by pootle flump View Post
    Also, can you post your excel file as Access if the above fails?
    Errh No! I've just tried exporting the Query but it needs a database to save into. What information do you think would most help (the SQL view of the query or the result the query generates) and how does one remove that from a database?

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Access does that with derived tables if you play around with the query in design view. It is a douche in that respect and another reason why Access sucks if you want to learn and\ or use SQL.

    Edit and paste back in.
    Code:
    SELECT *
    FROM SALES 
    INNER JOIN 
    (SELECT Receipt, Job FROM JobMain WHERE Job IS NOT NULL) AS Jobs ON Sales.Receipt=jobs.Receipt
    ORDER BY jobs.Job, jobs.receipt, Sales.date, Sales.time
    I am afraid I can only abstract your problem so far without the exact schema and data.

    What I would like is an access database containing all the tables that contain the data you require, with at least some sample data in there. Nothing more, nothing less. Certainly no queries required - I'll write those for you.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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