Multiple sorting

[kcollop]

I have a table with multiple columns, 300 records and 6 products and need to do a sort by

Product 1
....town 1
........date 1
........date 2
........date 3

....town 2
........etc

....town 3
.......etc

Product 2
....town 1
........date 1
........date 2
........date 3

....town 2
........etc

Product 3
etc

Can I do this with a line of coding, or do I need to set up queries.

This is what I have now....

SELECT [Fuel 2012].* FROM [Fuel 2012] LEFT JOIN (SELECT [Table1].[No], [Table1].[Product] FROM Table1 ORDER BY [Product]) AS Lookup_Product ON [Fuel 2012].Product = Lookup_Product.[No] ORDER BY [Lookup_Product].[Product], [Fuel 2012].[Plant];

I have trouble with creating queries, so if I can edit this line of coding, it would be easier for me.

Thanks

Steve

[John_G]

If you have just the one table, just use a select query, have the first three fields as product, town and date, and sort on those three fields.

In SQL, it might look something like:

Select * from [Fuel 2012] order by product, town, date

It's quite easy - or am I missing something? Are you using (from what I've heard) abominations called lookup fields?

John

[Robeen]

You said you have "a table with multiple columns . . ." but your SQL appears to be referencing more than 1 Table [Table1, Lookup_Product, [Fuel 2012] . . .].
Can you clarify for us please?
Doing what you need is really easy with a single table as your data source - but with what your SQL indicates - some clarification would help.

[kcollop]

I have two look up fields....one - "Plant" (with 24 towns) and the other "product" - ( with 6 types of procucts)

[John_G]

Hi -

In that case I'll have to hand this one over to those who know A2007 - I haven't used it yet.

John

[Robeen]

John_G started to say this . . . Lookup fields are strongly not advised - check this page: http://access.mvps.org/access/lookupfields.htm
Also - since I have never used them myself - and only a couple of times helped people who have used them & got into a bit of a mess - I cannot really advise on that.
What I believe [because I've read several real experts discuss the topic] is that if at all possible - you should steer clear of Lookup Fields in your tables.
It not only makes your database more 'standard' and compliant with database normalization - it will also be easier for you to work in and for a larger number of people to be able/willing to help you.
If you are absolutely stuck with your Lookup fields - you might try messaging June7, RuralGuy, pbaldy . . . they have all impressed me with their knowledge and expertise and their willingness to help.
Also - if you can - put a copy of your db here - I might be able to help if I can play with it as opposed to trying to figure it out second hand.
What you're trying to do should be a very simple thing to do - as John_G said in his first post.
I think it may have become complicated because of the use of Lookup Fields [but I'm not an expert on that so I can't really say].
All the best!!

[kcollop]

Hi there

I have copied my two tables.
I have been trying to set up a querie...but am hitting a dead wall.
What I want is to find the gasoline cost for each of the 3 communities (plants) from the PRICE TABLE and multiply them with the associated meter ticket quantity for the gas purchased in that community from the TICKET TABLE.


End result of what I want is a table that says:

Plant Ticket Date Unit No Product Quantity Price Total

703 - Cape Dorset DF23004 6/7/2010 xxx Gasoline 100.00 $5.00 $500.00
etc
etc
etc

Here are my two tables

In reality...there are 25 plants with 5 products each and 1000's of tickets each year.

Thanks
Steve

ID	Plant	Meter Ticket	Date	Unit Number	Product	Quantity
1	703 - Cape Dorset	DF23004	6/7/2010		Gasoline	100.00
2	703 - Cape Dorset	DC34003	6/7/2010	U345	Gasoline	100.00
3	704 - Resolute Bay	DF23454	6/1/2010		Gasoline	100.00
4	704 - Resolute Bay	CW23454	5/23/2010	H1224	Gasoline	500.00
5	705 - Pond Inlet	DF6756	6/15/2010	cd1231	Gasoline	500.00
6	705 - Pond Inlet	GH1234	12/23/2000	Vc1234	Gasoline	500.00

ID	Plant	Product	Prod Descrip	Price
1	703 - Cape Dorset	Gasoline	Gasoline	5.00
2	703 - Cape Dorset	10 - P50 H	Heating Fuel	5.00
3	703 - Cape Dorset	10 - P50 D	Diesel Fuel	5.00
4	703 - Cape Dorset	10 - P50 D Av	Aviation Fuel	5.00
5	704 - Resolute Bay	Gasoline	Gasoline	6.00
6	704 - Resolute Bay	10 - P50 D NM	Plant Fuel	6.00
7	704 - Resolute Bay	10 - P50 H	Heating Fuel	6.00
8	704 - Resolute Bay	10 - P50 D	Diesel Fuel	6.00
9	704 - Resolute Bay	10 - P50 D Av	Aviation Fuel	6.00
10	705 - Pond Inlet	Gasoline	Gasoline	7.00
11	705 - Pond Inlet	10 - P50 D NM	Plant Fuel	7.00
12	705 - Pond Inlet	10 - P50 H	Heating Fuel	7.00
13	705 - Pond Inlet	10 - P50 D	Diesel Fuel	7.00
14	705 - Pond Inlet	10 - P50 D Av	Aviation Fuel	7.00

[kcollop]

This is what I would like.....

ID	Plant	Meter Ticket	Date	Unit Number	Product	Quantity	Price	Total
1	703 - Cape Dorset	DF23004	6/7/2010		Gasoline	100.00	5.00	$500.00
2	703 - Cape Dorset	DC34003	6/7/2010	U345	Gasoline	100.00	5.00	$500.00
3	704 - Resolute Bay	DF23454	6/1/2010		Gasoline	100.00	6.00	$600.00
4	704 - Resolute Bay	CW23454	5/23/2010	H1224	Gasoline	500.00	6.00	$3,000.00
5	705 - Pond Inlet	DF6756	6/15/2010	cd1231	Gasoline	500.00	7.00	$3,500.00
6	705 - Pond Inlet	GH1234	12/23/2000	Vc1234	Gasoline	500.00	7.00	$3,500.00

[Robeen]

NB: I think I changed some of your field names while I was creating the tables - so be aware of that while you're looking over my work.

I did a quick mock up of your two tables & here's what I got:
My SQL.

SELECT Ticket.ID, Ticket.Plant, Ticket.MeterTicket, Ticket.TicketDate, Ticket.UnitNumber, Ticket.Product, Ticket.Quantity, Price.Price, [Quantity]*[Price] AS Total
FROM Ticket INNER JOIN Price ON Ticket.ID = Price.ID
ORDER BY Ticket.ID;

See screenshot for my Query Design & the Query Results.

I hope this helps.

[kcollop]

Thanks

I will work on this tonight and tomorrow and will let you know

Steve

[kcollop]

Well this won't work. Record are updated by each price, not the price associated by the product and the specific town....which causes many many records in the resulting query.

I think my problem is that there is no unique record bridging both tables...and I am not sure how to do or what this could be.

Steve

[Robeen]

I don't understand your problem. I think that actually I don't understand how your data exists in your tables.
As you can see from my screenshots - I was able to produce the exact results you said you wanted . . .

Can you send screenshots of your tables in Design view AND when they are open with data?

OR - can you post a copy of your DB here?