Order By Sum From Another Table

Dec 5, 2006

Hi All

I need to order a table of COMPANIES based on the sum of their LOGS in another table. This is what I have so far

Code:SELECT COMPANIES.COMPANY_NAME, COMPANIES.COMPANY_EMAIL, SUM(LOGS.LOG_REVENUE) AS TotalRevenue FROM (COMPANIES RIGHT JOIN LOGS ON LOGS.LOG_COMPANYID = COMPANIES.COMPANY_ID) GROUP BY TotalRevenue, COMPANIES.COMPANY_NAME, COMPANIES.COMPANY_EMAIL

But i get "No value given for one or more required parameters." in response to the 'TotalRevenue' aggregate field.

Any suggestions on how to accomplish this would be gratefully received.

Many thanks for your time

Nick

View Replies


ADVERTISEMENT

Tables :: Make Calculated Field In One Table That Calculates Values From Another Table Linked By Order ID

May 13, 2015

I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?

View 3 Replies View Related

Order Data Table Help

Aug 9, 2007

Hello All,
I am new to access so this might be a stupid question but here goes.

I am making a new database for my orders (which I download from my eCommerce software in the form of a CSV file).

The Problem I am having is that my Order numbers are unique and that is what identifies the orders, however in the table data each order that has more than 1 item is listed on 2 seperate table lines. All info on the rows are identacle until the Part # field and the price field.

How can I make access understand that these few lines of data with the same order # are actually the same order with multiple items on the order?

Thanks,
Eddie

View 2 Replies View Related

Ascending E Descending Order Of A Table.

Jan 4, 2006

Happy new year everyone.

I have a table in which the data input comes from a dropdown list selected in a form combo box as:

contract signed
PIN issued
PDD issued
validation finished
Host country approval
EB approved
CER's issued.

I am designing a report in which I want to display the above list in Ascending or Descending order but as shown rather than alphabetically.
What I mean is no matter how the information is on the table the report showd display it in Ascending as contract signed, PIN issued, etc and in Descending as CER's issued, EB approved, Host country approval , etc.

Any ideas?

Thank you

dfuas

View 6 Replies View Related

Order By Weekday In Table And Form

Dec 27, 2013

I have a column in my table for day of the week (Monday, Tuesday, etc.). What can I enter in the "Order By" table-property to order the records by their chronological weekdays? Additionally, is there a similar type of action I can perform within a related form?

View 3 Replies View Related

Tables :: Autonumber Order In Existing Table

Jul 9, 2015

I was adding an autonumber field to an existing table and I assumed the numbering would follow the order of the primary key but that doesn't seem to be the case.

View 9 Replies View Related

Preserve Order Of Items In Table Without ID Or Primary Key?

Nov 10, 2012

This is a CSV file that comes through an Access specification. I need to be able to copy the items from the table to other tables but somehow apply a unique and sequential ID to the rows first, this is essential to the job. The text import spec applies a unique ID but save the spec, and call it using VBA, it doesn't do that.

Also, there seems to be a bug where using a macro to run the text import specification, (and this is after an update from Microsoft), that it doesn't recognize the specs anymore... after I deleted several of them.

View 8 Replies View Related

Forms :: Order Form Pasting Data Into Table

Aug 20, 2013

Okay I have an Orders Form, which generates a SubTotal Price (ex VAT), VAT, Shipping & Freight Charge based on an IIF statment of being under a 50.00 order apply 20.00 charge. And finally a Order Total with it all added together. This works fine, however I cannot seem to find a way to post these prices in my Orders Table. When I click on Datasheet View for the form, all the details appear as they should.

View 3 Replies View Related

Modules & VBA :: How To Arrange Table Data Into Date Order

Dec 31, 2013

I have an Access table with transactions that were entered in random order. I need to recreate the table with those transactions entered in date order. I started by making a copy of the table (table structure only) then creating an Append Query sorted by the date field thinking that the records would be appended in the order created by the query. It did not work as I wanted. My next technique was to use VBA code to open a recordset that was created with the following SQL statement:

strSQL = "SELECT * FROM tablename ORDER BY datefield" & ";"
Set rst1 = db.OpenRecordset(strSQL)

The compile hung up, apparently not liking something about the SQL statement. I have done these things many times before during the last 15 years of using Access. I am about to shoot my computer.

what I am doing wrong?

View 4 Replies View Related

Tables :: Generate New Order ID For Same Customer In Orders Table?

Oct 3, 2012

how do i get form to generate a new order ID for the same customer in my orders table

View 2 Replies View Related

Queries :: Create Query To Sort Table In Particular Order

Feb 28, 2014

I have a table with multi columns with unsorted data.

I want to run query to sort data in multiple columns.

How can i do it?

View 1 Replies View Related

General :: Order Database - Discount Table With Percentage For Each Item

Aug 23, 2013

I have been asked to create a database that creates orders, debits stock etc for my company. Now I have been asked to put in some kind of discount table with percentages for each individual item for each individual customer. What would the best way of doing this?

I figured in the Customer Table I would have a code such as 'CustomerADiscount' and 'CustomerBDiscount' etc. and then in my table maybe

Product A - CustomerADiscount - 10%
Product A - CustomerBDiscount - 20%

Product B - CustomerADiscount - 5%
Product B - CustomerBDiscount - 7%

This link these values up within a query? Would this be the best way of doing it?

Is Access able to determine a percentage in "10%" or "5%" or do I need some type of formula for it instead?

View 3 Replies View Related

Forms :: Dataset Based On A Table - Order Of Added Records

Aug 9, 2013

I have a form with a dataset based on a table. (there are a number of buttons on the form with code so it's important I don't delete the form and start again with a new one)

The data displays correctly but the order is wrong. It displays the last record added as the first record when viewed in form view.

I want to reverse this order, how do i go about it? It seems very simple but I've yet to find a solution.

I also don't want to base it on a query because of the code mentioned earlier.

View 4 Replies View Related

General :: Work Order System - Empty Records Showing In Table

Apr 3, 2014

I have a work order system that people use but it somewhat randomly puts blank records into my table. I've added a lot of validation checks when submitting, closing and resetting the form and limited the way people can exit out of the form to fight this issue but it still happens.

View 3 Replies View Related

Queries :: SELECT Records From A Table Based On IN Clause And Sort Them In Order

Jan 4, 2014

WinXPPro Sp3
Access 2007

After some research I thought I had found a neat way to SELECT records from a table based on an 'IN' clause and sort them in the same order as the values for the 'IN' clause... i.e.

Code:
SELECT Unique_No, Table_Name, List_Order FROM My_Table
WHERE Table_Name = 'Titles'
AND List_Order IN (3,1,15,4,5,12,7,2)
ORDER BY INSTR('3,1,15,4,5,12,7,2', List_Order)

Unfortunately, this returns list_order 5 just after 15 and list_order 2 just after 12, thus

List_Order
3
1
15
5
4
12
2
7

View 3 Replies View Related

Tables :: Modifying MS Access Database - Direct Order Or Placement Of Fields Within Table

Feb 5, 2013

Is it possible to direct the placement/order new of fields when modifying an Ms Access database in code?

I need to modify the schema of an MS Access database via code - but I want to be able to direct the order or placement of the fields within the tables.

For example - if TableOne has 3 fields - Field10, Field20 and Field30.

I would like to be able to add say Field15 between Field10 and Field20 - not just append it to the end of existing fields.

I believe it is possible as you can do it within Ms Access itself. I can use ADO, DAO, ADOX or SQL for that matter - but it seems all of these offer no placement of the field within the table.

View 13 Replies View Related

Forms :: Create A Data Entry Form To Order Table Using 2 Cascading Combo Boxes

Jan 31, 2014

What I am trying to do is create a data entry form to an "order table" using 2 cascading combo boxes. I have created a data entry form based on a query. I can't get the cascading combo boxes to work properly.Here is the code:

Private Sub Combo0_AfterUpdate()
Combo2.RowSource = "SELECT L2_ID,L4_Element_name,L5_Category FROM qry_ord WHERE L3_ID = Combo0.Value;"
Combo2.DefaultValue = [Combo2].[ItemData](0)
Command4.SetFocus
End Sub

[code]....

View 2 Replies View Related

Allow More Than Order In ORDER Form

Apr 4, 2013

I have a problem when I want to create an "ORDER" form, that will allow user to enter more than one order.

I have no clue how to do it.

View 14 Replies View Related

Queries :: ORDER BY - Update Values In One Table With The Values In Another Using Join

Sep 18, 2013

I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:

UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN)
SET TableA.HCC = TableB.HCC
WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL
ORDER BY TableB.HCC, TableA.CDN;

View 2 Replies View Related

Order By

Feb 10, 2008

Good morning,

I have a table ID (primary key) and

ID
--
FEASSDDE
RDSAAASE
NEESSDSD
AEIERIEIEE

and I want to do a select of these values, order by ID ASC, and see the position of each one in this order, something like this:


POS ID
--------------------
1 AEIERIEIEE
2 FEASSDDE
3 NEESSDSD
4 RDSAAASE

View 2 Replies View Related

Tab Order

Feb 17, 2005

I have a startup form with three buttons. All three buttons have Tab Stop set to No. However, when I open the form, there is a highlighted box around what would have been the first button set in the tab order had Tab Stop been set to Yes. Is there a way to not display the light green box around the text in the button?

Thanks,
SKK

View 2 Replies View Related

Tab Order Help

Nov 7, 2006

My tab order is the way I want it and when the user enters after the last field on the form, they get a new record. Problem is, the cursor is putting them in the last field on the form instead of the first one. I don't get it. The first field is first on the tab order list. Help.

View 1 Replies View Related

How To Order A Sub Form

Jun 16, 2005

I have a form (Members of the club), linked to a sub form (payments of membership).

When I open the record of Mr John, I want to see all his payments order by date of payment.

How do I do?

Thanks in advance,

Jackske - Belgium
:confused:

View 6 Replies View Related

ORDER By Date (SQL)

Sep 14, 2005

I have a query which i've ordered by date with:

ORDER BY status_reports.date;

But I want it to display in reverse order with most recent date first for example i want it in order of 18/sept/05 then 15/sept/05 then 10/sept/05.

im guessing i just need to add to the above SQL but im not sure what!

Can anyone help please

Thanks

View 3 Replies View Related

Sort Order

Oct 10, 2005

How do you change a file's sort order from ascii to international?

View 3 Replies View Related

ORDER BY Question

Dec 9, 2005

Hi, is there any way to do an order by, but specify that a particular value is listed last, or first.

E.g. ORDER BY t_name ASC ... but feature Simon first and not with the other names beginning with 'S'

Thanks.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved