Hi all!, i would really appriciate it if you could help me out. I have this complex query... in one table i have dates and costs associated that show when fuel was put into the system
I then have another table that has data when fuel was taken out of the system
such as:
1/2/2008 -- ($50)
1/3/2008 -- ($20)
1/4/2008 -- ($10)
what i need is a query that is able to show a running tab on the fuel tank and how much fuel is in it...
so for example if it could spit out a result such as
1/1/2008 -- $200
1/2/2008 -- $150
1/3/2008 -- $130
1/4/2008 -- $120
If this is convoluted i can upload a little database..
Thanks! (Here is the current query i am using but its not working right.
SELECT DatePart("yyyy",[purchase_Date]) AS AYear, DatePart("m",[Purchase_Date]) AS AMonth, DatePart("d",[Purchase_Date]) AS ADay, DSum("gallons_purchased","purchase_fuel_tbl","DatePart('d', [purchase_Date])<=" & [ADay] & " And DatePart('m',
[purchase_Date])<=" & [AMonth] & " And DatePart('yyyy',
[purchase_Date])<=" & [AYear] & "") AS RunTot, fuel_use_tbl.fuel_date, fuel_use_tbl.external_ID, fuel_use_tbl.gallons, [RunTot]-[fuel_use_tbl].[gallons] AS cur_Value
FROM (tank_tbl RIGHT JOIN purchase_fuel_tbl ON tank_tbl.tank_ID = purchase_fuel_tbl.tank_ID) LEFT JOIN fuel_use_tbl ON tank_tbl.tank_ID = fuel_use_tbl.tank_ID
GROUP BY DatePart("yyyy",[purchase_Date]), DatePart("m",[Purchase_Date]), DatePart("d",[Purchase_Date]), fuel_use_tbl.fuel_date, fuel_use_tbl.external_ID, fuel_use_tbl.gallons
ORDER BY DatePart("yyyy",[purchase_Date]), DatePart("m",[Purchase_Date]), DatePart("d",[Purchase_Date]);
I was recently asked to rewrite some stuff and i was wondering if the query below can be expressed with just ( SELECT, FROM, WHERE, GROUP BY ) Instead of JOIN. Someone else wrote the query below and Joins always confuse me ... Thanks in advance
SELECT Grpoffic.CODE, Evoter.precinct, Evoter.grp, IIf(evoter.style Is Not Null,evoter.style,evoter.[activator code]) AS ballot, Evoter.party, Evoter.[date voted] AS [voted date], Evoter.certnum AS [voter id], Evoter.name, Evoter.address, Evoter.[ev location] AS [Location voted], Vote_status.status, AB_CODE_reason.reason AS [absentee reason] FROM Vote_status, AB_CODE_reason RIGHT JOIN (Evoter INNER JOIN Grpoffic ON (Evoter.precinct = Grpoffic.pct) AND (Evoter.grp = Grpoffic.grp)) ON AB_CODE_reason.code = Evoter.reason ORDER BY Grpoffic.CODE, Evoter.precinct, Evoter.grp, Evoter.name;
I have MainTable, on which I base MainForm. I would like to have MainForm show only the records that have a null value in CertainField. If I write NullQuery to select only those records, can I redirect MainForm to NullQuery? Well, I know I can do that... but how does MainTable get updated with new records if MainForm is based on NullQuery????
My DB has a table named noldba.int_member_history, which contains the following fields: member_id, date_start, date_end, member_status, iva_case_id, case_id,
It is possible for the member to have several lines of data under their same member_id like this:
20125626 12/1/1990 2/31/1990 N 21116625 20002717C 20125626 1/1/1991 4/30/1992 A 62574431A 20002717C 20125626 5/1/1992 8/31/2000 N 20006625 20002717C 20125626 9/1/2000 12/31/9999 M 21116625 20002717C 20125626 8/1/2000 12/31/9999 M 20111832 20090698C
So this member had 4 start and end dates under case 20002717C and only 1 start and end date under case 20090698C
I'm trying to write a query which is looking for member_id's along with a case_id which only has one member_status returned from the query and the member_status must be M. In this case the member has two cases which have the M member_status, but case 20002717C has other types of member status so I would not want the query to return the 20002717C case, only the 20090698C I'm sure Im asking for something that can't be done...but I'd never know unless I asked. Thanks
Currently I have one query that prompts the user for a beginning date, and end date of a week. It then proceeds to show the user all records in that time frame and makes a table. Now I need another query that looks at the dates the user had entered and gets all the records from the week before the user's dates. This query would also be a table query, but separate from the first one.
If anyone at all is able to help me out, I will be deeply impressed and eternally grateful! :)
I am trying to hardcode in the field name. This is what I want to hardcode "TEXT(ROW(A17),"-0")" but the parenthesis and quotation marks are causing a syntax error. It appears like this
Currently I have a combo box in my access 2007 application which is drawing its value through a table using a SQL Query. I would also like it to show up a hard coded value in addition to the current list.
I am building a database that will tell the company what items to dispatch to the customer first. It’s a food company. So getting products out on time is important. I will try explain it. Say the food company makes a batch of 30 apple tarts. This collection of products has a set batch number with all the relevant information attached. For exampleBatch no 4457 - qty 30 - created 10/10/07 - use by 10/11/07 Batch no 4458 - qty 30 - created 11/10/07 - use by 11/11/07Now lets say customer (joe Bloggs) orders 35 apple tartsThe food company wants to take all the first batch (4457) and 5 from the second batch (4458) The will have an order sheet with the followingApple tart batch 4457 - 30Apple tart batch 4458 - 5 After the order is gathered it will be given an order number of say order 8897The company can now go back and look at order 8897 and see what was shipped in that order and what the batch numbers where for all products. Any help would be greatly appreciatedAlan
SELECT Mid([Date],4,2) AS [Month Number], Count(tblOrders.Order_Number) AS CountOfOrders, Sum(tblBikes.Price) AS SumOfPrice, Format([Date],"mmmm") AS [Month Name] FROM tblOrders INNER JOIN (tblBikes INNER JOIN [Order/Product] ON tblBikes.Model_Number = [Order/Product].Model_Number) ON tblOrders.Order_Number = [Order/Product].Order_Number GROUP BY Mid([Date],4,2), Format([Date],"mmmm");
Hi expert. could any one explain this code for me. I do not understand some parts of it. I know the output but not some parts of the code.Thanks
Code:Private Sub processButton_Click() Dim fso As New Scripting.FileSystemObject <------ Dim io As Scripting.TextStream <------ Dim db As DAO.Database Dim rst As DAO.Recordset Dim fld As DAO.Field '''Declreaing our variables Dim strBase As String Dim strInsert As String Dim strFields As String Dim strValues As String Dim strTemp As String Dim strFile As String Dim strName As String Set db = CurrentDb() Set rst = db.OpenRecordset(Me![ComboBox]) strBase = "INSERT INTO " & Me![ComboBox] & "({%1}) VALUES ({%2})" <------ strName = "c:" & Me!ComboBox & " Data.sql" With rst <------ While Not .EOF strValues = "" <------ If Len(strFields) = 0 Then For Each fld In .Fields If Len(strFields) > 0 Then strFields = strFields & "," & fld.Name & "" Else '''strFields = "[" & fld.Name & "]" strFields = "" & fld.Name & "" End If Next fld strInsert = Replace(strBase, "{%1}", strFields) End If For Each fld In .Fields If Len(strValues) > 0 Then strValues = strValues & "," End If If IsNull(fld.Value) Then strValues = strValues & "null" Else v = fld.Value Select Case fld.Type Case dbMemo, dbText, dbChar strValues = strValues & "'" & v & "'" Case dbDate strValues = strValues & "#" & v & "#" Case Else strValues = strValues & v End Select End If Next fld strTemp = Replace(strInsert, "{%2}", strValues) strFile = strFile & strTemp & vbNewLine .MoveNext Wend rst.Close End With If Len(strFile) > 0 Then Set io = fso.CreateTextFile(strName) io.Write strFile io.Close End If End Sub
I have a financial database that gets downloaded transactions off the internet from our accounts. The problem is that the transaction payer/payee is not always unique and needs to be classified. This is easy done using If statements but I really want to enter parts of the string into a table and have a query return a category for this payer/payee. Example: I want this: AUTO ONE CLARKSON CLARKSON WA AU006495 to be recognised as: car parts
or with this tranaction: CLARKSON MINI MART CLARKSON553908 look for "mini mart" and return supermarket
This step will make classifying transactions a lot simpler and user friendly. thanks
Let see if I can explain my goal. I've been fighting with this for some time with no resolve. In the included DB has a table that represents the fields that will be queried in the real DB. It shows records of one of many employees that were "tested" (shopped) on a date and whether it was a successfull shop or not. My goal is in three parts.
1)A query that goes through each employee and returns the records with the check box in SuccessYes checked (Yes) back to the last unchecked box, not including the unchecked box. In the example given the last (most recent) 5 records would be returned. If the shop of date 1/18/2005 was not checked, it would only return the last 2 records. Basically, I'm looking for a way to have a report that shows everyone's current successes in a row. It could be any number.
2)A query very similar but it returns only those employees that have the current successes in a row totalling 5 and where the field "Award5_Paid" is NOT checked (they have not been paid their reward). From there it should be easy to do one for the 10 in a row.
3)If I've got a form open (or...?) where I can place a command button, that when clicked, it appends the selected records with checks in the appropriate boxes for being paid. Example: If a set of employees come up in the query with 5 successfull shops in a row because the "Award5_Paid" field is unchecked, then I need to pay them. Click the button and the Award5_Paid field gets checked in THOSE RECORDS ONLY so that the next time I run the query, they won't show up and get paid twice. Issue? What's the coding or SQL or ??? that's run when clicked.
I hope that makes sense. I've been running around in circle trying to figure this one out. Maybe it can't be done, but if it can, I know some of you know how. As always, thanks for the assistance.
Hi, i know i have done this before but its been so long and i know its real simple so here goes:
Say i have a record in a table and i want to add records to that record in another table does simply creating a relationship between the two tables make sure the information is relevant to that record and how do i add more than 1 tables information to a form so i could show the record from the first table and say all of that persons orders from another table?
hi all, I'm just beginning to know the world of Access so maybe the solution for my problem is pretty simple. I have a huge database with Itemcodes, Costprice of materials and EffectiveDate (date on which new prices are valid). These dates represent past dates as well as future dates. The one that interests me the most is the most recent date in the past. What I would need is a query that filters out the most recent date per itemcode because right now there are several prices with several validity dates. Eventually it should result in a database that shows an item with one validitydate and one price. Right now the same itemcodes are repeated because different validitydates are related to it. I really hope somebody can help me out on this. Thanks in advance.
Hello buddies :D, do you have any idea how to make this work?
To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.
This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only. ([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null) How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
I want a field in a table that is a date but that is the year only. I only want this because I want to be able to filter my charts to show one or a span of years for comparison. I have criteria in a query that states between [forms].[charts].[begin] and [forms].[charts].[end] and the corresponding form that my user puts the desired begginning and ending dates into before pressing buttons that bring up the charts based on the query.
My problem is that I can't even get the table to take a year only date. If I leave the date field as text the between clause in the query won't work. I tried it as a number with >= etc. and that didn't work either.
I have created a database in which I have three Fields. 1. Date Joined 2. Renewal Date 3. Member/Renewal
When I type in the date a person joins our club, I have made the renewal date come up displaying a year ahead, when renewal of membershiip is due.
I would like to be able to put in the date joined date and have the field 3 show Member and then when the renewal date is reached, to have in automatically change to Renewal.
It is probaly a simply formula but I can not get it right.
I have seen some API that gets the serial of the hard drive, but this information is not unique and changes when the volume is formated. Does any one know of a way to return the orignal manufucture's information/serial which is unique(GUID) to the volume. Any Idea will be highly appreciated.
