I have a database of song track data with track length as a field.
I want to produce play lists up to specific lengths e.g 15 minutes and just want the query to show me enough songs to fill up this time period.
Any help appreciated - especially simple solutions!
Jim
I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
Can someone tell me what the maximum amount of fields that you can have in a query is please?
View 1 Replies View Relatedhi there
how many columns is the maximum in a query?, because i have one that has 26 columns , but i need some more... what can i do?? is there a way to add some empty columns to a query??
thanks
Can someone help me out on this:
I have a table 'articles'
ID --- date --- authorID
1 --- 20060901 --- 1
2 --- 20060902 --- 1
3 --- 20060903 --- 3
4 --- 20060904 --- 3
5 --- 20060905 --- 2
I need a query that will produce the records that have maximum date for each author (distinct authors), ordered by date descending.
For example, output should be in this case:
5 --- 20060905 --- 2
4 --- 20060904 --- 3
2 --- 20060902 --- 1
Hope that you understand what I mean...
Please help!
I know that tables have a max of 255 fields. How many can you add to a query?
I have a large database that is divided into several tables. When building the query to select all the data I will be way over 255.
Will this be an issue?
Thx!
I need to know about "Need to query result from access2007 find only "one maximum number" in hour per day every cell ID"
How I use sql command?
See my file at ...
Hi,
1) I am pretty newbie to this access programming, do forgive me if my questions sounds stupid.
2) Basically I create an application in access capturing or production information for my company. now the top management suddenly wanted whats their main concern:- Total Daily/Monthly, Quarterly, Annual Sales (By Model If possible)
3) I start with daily (Lets don't be too overly ambitious).
4) I try to let user select dates from my calender control and reflect daily sales (in Total & By Model break down) insert into my form.
5) Understand someone told me from my previous post in Calender control I can achieve it either through forms or queries, which is a better way. (in terms of flexibility to change for program maintenance/ scalibility) wise ?
PS: Please forgive my ignorance :o:(
Thanks (In advance) & God Bless.
O.K, I am really trying to figure this out from other postings but my limited query experience is limting my understanding of the other posts.
I only know how to use the query wiz and then a bit in design mode.
I have a Query
[ID]pk [Contest Name], [Score]
There will be many records for the same [Contest Name] in the underlying table. Therefore i want to sub total by [Contest Name] so i can then create a report. I have created the report perfectly using the Report support in another posting submitted. But the report does not allow me to order the results as the sum calc is a function on the report. Therefore I am now exploring the Query Sum [Score] order by [Contest Name].
I just need it in a Qery for dummies format.
Go into design mode and put the following in what portion of the query on what line.
I will keep on searching in the mean time
I have a query which returns charge_cost (based on course cost, whether it went ahead, if hospitals are eligible for charging etc) which is then used in an existing report.
I want to make another report which simply is:
Total training spend for 2004-2005:
£1276.04
And i just want that to be the SUM of charge_cost.
I cant work out how to do this - i did a new query including charge_cost and then created a new field called total_spend: sum([charge_cost]) but i keep getting the message "You tried to execute a query which does not include the specified expression charge_cost as part of an aggregate function"
I have no idea what this means...
hmmm... found another little problem. I've got a list of ingredients with cost and amount from a table, total cost per ingredient is simply amount times cost, how would I go about getting a recipe total by summing the seperate ingredient totals?
View 2 Replies View Relatedis there a way to calculate/store total cost value of qty x unitcost in total cost field of same table. if it requires a query, would appreciate guding me how to write it.;) ;)
View 4 Replies View RelatedHello all,
I again ran into something that I can't figure out.
I have a table:
Date
Time
FirstName
LastName
SSN
InAmount
OutAmount
I need a query to sum up the InAmount and OutAmount into one total based on the SSN. This query is placed into a form that is then placed onto another form. The form is to alert the user if the amount of the Inamount and Outamount of a unique SSN totals above $10,000.01 on the current date.
So for example if on 01/01/07 if SSN=111-11-1111 has an Inamount of $5,000.00 and an OutAmount of $5,000.01 thus totaling $10,000.01, then the person's name will appear on the form list. This will change/clear when the date is 01/02/07.
Any Ideas on how to accomplish this?
Hi,
I am trying to write a query that will total the number of fields that have matching values. For example i need to have IP addresses added into the table via a form, that bit is done but i need to create a query that will count how many times an individual IP address is added to the list.
So that on the report i can show the list of IP address and instead of showing duplicates it will show how many times it has been added to the table.
Any help on how i could do this would be greatly appreciated.
Cheers
Luke
I have a Table where the are several entries all with Fields A, B and C
the same, and varying values for Field D.
There are several different values of A, B and C.
I want have a query, pick out each combination of A, B and C and just the
Max value of D for that combination.
It looks as if it should be simple but I can't figure out how to do it?
Anybody able to help?
Hi to all;
I am not familiar with access function, in Excel its very easy to find the max value at row level, what's the formula in access query to get the max of this example
Co1co2 co3….co60max(co1:co60)
200150300 500 ?
212542321 222 ?
thanks in advance for respond
Majed
Does anyon ehave any experience of running totals in an access query.
I'm reporting the data through excel not access reports so need a query not a report solution..
I have a table which looks:
RegionCategoryTypeDesc Period_IDPeriod_YTDPeriodTotal
CanadaEventsWSOP Team67Budget15000
CanadaEventsWSOP Team78Budget0
CanadaEventsWSOP Team89Budget0
CanadaEventsWSOP Team910Budget0
CanadaEventsWSOP Team1011Budget0
CanadaEventsWSOP Team1112Budget0
CanadaEventsWSOP Team1213Budget0
CanadaEventsTOTALAll12Budget15000
CanadaEventsTOTALAll23Budget15000
CanadaEventsTOTALAll34Budget15000
CanadaEventsTOTALAll45Budget15000
CanadaEventsTOTALAll56Budget15000
What I would like is to have an additional column which keeps a monthly summary of spend based on running total month 1to 12. All items have months 1 - 12 and are ordered in that fashion.
Any helpo really appreciated.
Simon
Please look at the attached sample db file and the text file explaining the problem.
Thanks
Hi,
I have a database, in which I need to add up the total number of entries made at any point of the day. I have started by creating a query with the entry's SerialID (unique) and the date it was entered, however I am stuck already.
Any help would be appreciated,
Ben
I quess it should be simple. But I couldn't find an answer or example in Access books and online.
I have an access 2000 database with a Accounts Receivable table. I am building a query that returns accounts what are 60 days past due and calculating total ballance of ONLY past 60 days accounts. I managed to write a code to display 60 days past due acounts, but when I use UNION query, it calculates total from entire Accounts Receivable table, but I need only total ballance of past due accounts.
Here is my SQL code
SELECT [Accounts Receivable].InvoiceID, [Accounts Receivable].[Patient Last Name], [Accounts Receivable].[Patient#], [Accounts Receivable].InvoiceDate, [Accounts Receivable].PaymentAmount
FROM [Accounts Receivable]
WHERE ((([Accounts Receivable].InvoiceDate)<Date()-60))
UNION SELECT 'TOTAL', "","","",Sum([Accounts Receivable].PaymentAmount)
FROM [Accounts Receivable];
I need my query to look like this.
InvoiceID Patient Last Name Invoice Date Payment Amount
23 Smith 05/01/2007 $100
25 Doe 04/03/2007 $200
Total - - $300
Please help.
Hi,
Am trying to create a query for a chart where I can total the employees over time but am having real trouble creating a running total from the "Total" field within a query but cannot seem to get it at all.
TotalStartDateLeftDate RunningTotal
126/03/1957
121/03/1971
127/02/1986
115/02/1988
207/03/1988
007/03/198831/05/2007
Here is my current SQL query:
SELECT Sum([CountOfStartDate]-[CountOfLeftDate]) AS Total, Atest1.StartDate, Atest1.LeftDate, Sum([CountOfStartDate]-[CountOfLeftDate]) AS RunningTotal
FROM Atest1
GROUP BY Atest1.StartDate, Atest1.LeftDate;
Can anyone help please?
This should be easy! Right?
I have a series of dates with events that occured on those dates. Some events were extended, others were not how do I get a running total, cumulative total, for all records in the RunTotal column?
Opened DateOpen IssuesCountOfExtendedNotExtRunTotal
5/21/2007 1 10 1
8/6/2007 1 10 2
10/8/2007 1 10 3
11/1/2007 1 10 4
11/8/2007 1 01 5
12/5/2007 1 0 1 6
Thanks for your help.
is it possible to have a query carry out a running total, so that it counts the record above and the current entry.....
example
Record 1 Total 10 Running total 10
Record 2 Total 10 Running total 20
Record 3 Total 30 Running total 50
Record 4 Total 5 Running total 55
as i said i need this done in a query if Possible...
anyone help?
I am doing an assignment where i am required to
Quote: "Find the total price (SUM) of all stock items in the database (use total query and find the SUM of the [price of stock]*[quantity on hand]"
Ive tried several times to do this, each time unsucessfully because im unsure how to go about it. i can get the sum of those two things, but i cant multiply the two.
Thanks in advance for the help
I have a query that is filtering records from a table, I have used the Totals row in the query to Group By and provide Count of totals. The datasheet view of the query has the total row and is working fine. I would like to display the total row on a report, using the query as the record source.
It does not seem to be available, so I put a text box in the footer of the report with the Control Source set to: =Sum([CountOfPermit_Type]), but that only returns an error.
Ive got an SQL query as below, what Im trying to do is get the total value of that SQL query and drop it into a form text box.
The placing of the result on the form textbox isn't a problem but getting a sum total of the query result is proving to be a little tricky tricky tricky.
Code:
Dim strSQL As String
strSQL = "SELECT TestTable.Hours FROM TestTable" & _
" WHERE (((TestTable.sUser)=Forms!Submittedsheet_frm!AdminSelect_Combo.Column(0) AND" & _
"((TestTable.[Task Date])>=[Forms]![Submittedsheet_frm]![FromDateAdmin_TXTBox] And" & _
"(TestTable.[Task Date])<=[Forms]![Submittedsheet_frm]![ToDateAdmin_TXTBox];"