Selecting Most Recent Transaction
Jan 22, 2008
I have these fields
ID,InvoiceID,DateEntered,Description
What I need now is to show the most recent transaction per invoice and what was the description for it.
Doing this
SELECT InvoiceID, Max(DateEntered) AS MaxOfDate
FROM TableName
GROUP BY InvoiceID;
Works Perfect. The problem I have is when I display the Description field. As soon as I do that, I see all invoices and not just the most recent. Anyone know how to write this sql statement that will return the invoiceID, dateentered, and Description only for the most recent invoice in the system?
View Replies
ADVERTISEMENT
Jul 16, 2007
Hello, I'm working with SQL and databases in general for the first time, and was wondering: how would I select just the most recent entry for each device? my data looks [roughly] like this:
device1 data_1a 15.2.2000
device1 data_1b 15.2.2001
device1 data_1c 15.2.2002
device2 data_2a 15.2.2000
device3 data_3a 15.2.2001
device3 data_3b 15.2.2002
So what I'm looking for is:
device1 data_1c 15.2.2002
device2 data_2a 15.2.2000
device3 data_3b 15.2.2002
Thanks for any help you can offer!
-Eric
View 2 Replies
View Related
Feb 19, 2008
Hello all
I'm sure the answer is on here somewhere but trying to find it and then getting it to work is a bit of a problem, so i resorted to posting.
I have 5 tables
Partnership Details, Invoices, Funding, Communication, Communication Types.
the field Partnership Name in the Partnership Details table has a one to many relationship with the partnership name on tables: Invoices, Funding and Comunication.
What I want to do is create a query that will show the most recent invoice (detirmined by date) and the most recent funding (detirmined by date) for each partnership.
I can do this using max if I only want the feilds Partnership name, invoice date and funding date. however I want to create a report that will show several feilds from partnership details and I want to show the invoice no. and amount as well as the date and also the amount and notes field for funding
Is anyone able to help?
Thanks
Niyx
View 3 Replies
View Related
Oct 9, 2005
I want to run 10 big queries (append/update/delete) one after another.
If error happens before they all finish (if user press escape button) - I want
to discard all record changes made by any of those queryes. I suppose
begin/end transaction method is the right way to do that. Can you give
me an example of code?
View 1 Replies
View Related
Oct 7, 2005
I'm not sure what to search for to figure this one out. My searches, so far, haven't helped me much.
I'm tracking transactions in a clinic. The amount due for the visit is calculated fine, but sometimes people don't pay right away. (And sometimes pay ahead, which will just be a negative amount due.) I need my transaction to not only show what's due from today's visit, but also to look at previous transactions of the same client and calculate what may still be due from before. Additionally, if I pull up an OLD record, I need it to show what WAS due on that date, not what's due today.
So, I know I need to refer to dates prior to the date on the form, regardless whether it's today's date (for a new visit) or a previous date (for a previous visit). That's what I'm confused about.
How do I refer to [txtDate] on THIS form and not confuse Access with [txtDate] on earlier forms?
Code like this:
SUM[blahblahblah] WHERE [txtDate] < [txtDate] surely won't work.
Does:
SUM[blahblahblah] WHERE [txtDate] < [Me].[txtDate] work?
Should this kind of calculation be in a query or in the Control Source itself, or somewhere else?
And do I need the square brackets or not?
Do I use '.' or '!'?
View 2 Replies
View Related
Jul 14, 2005
Hi,
I don't want to abuse anybody's patience but I have yet a couple of other quick question about transaction table.
I am using a summation query for the stock on hand where by keeping it simple: stock = sum of movement in the table. What hapens if a simple function is used to calculate the stock. ie: Stock=[Qty Declared-(Qty Declared-Qty Received)-Rejects]-Qty Allocated
All those Qty belong to the same field then I can't just run a sum.
My other concern is that:
Qty Declared
Qty Received
Qty Rejected
are three values entered at the same time but in the same (transaction type) field so data like the date would need to be entered 3 times for each item received?
Thank you very much for your time and patience again,
View 3 Replies
View Related
Feb 2, 2005
Hi,
I'm developing a database to handle various aspects of quality control reporting. I've got a working structure however, i think, i have gone off the track slightly... First I'll show you part of the structure:
(please see attached .jpg)
(hope that makes sence)... so effictively I have a many to many relationship.
I have forms for each of these entities (frm_parts, frm_Suppliers). The problem i have noticed is you can assign the part a supplier in frm_Parts but it does not appear in the transaction table as a record. Is this because i have based the field "Part Supplier" (a combo box) in frm_Parts on a query? If this is not the right way to go about it. how do i go about it?!
I have an idea... on this.. If i create a new combo box on the form I can use the wizard to select the supplier ID from the Supplier table (i believe) and "store" it in the transaction table... i think! But I want the user to select the Supplier by drop down list showing Supplier Number and Supplier Name.. not the ID.
Any help is much appriciated!
Next job...
I have a form where I would like the user to be able to enter, for example, a Supplier number and see if a record of the Supplier exists yet. Next I want the user to be able, assuming the Supplier record exists, to search for a record of a Part number from that supplier. Next I want them to be able to "select" that part and be able to open a form with a new record using that part number (this form holds details of a report rasied against that part).
See what I'm getting at?
I've had a go at this but it's been a couple of years since i did any detailed work on an Access database so somethings are a bit hazy!
I'm not asking you to do this for me, but I would much appriciate some pointers on how to create this sort of thing!
Many Thanks
Rob
View 6 Replies
View Related
Mar 17, 2006
hi there...
I have been looking around the forums for a little while now... and cannot seem to find anything which will help me.... heres my query..
I have a stock control database.. which allows for sales transactions via a barcode reader...
my first query is.... when the user scans a product.. how can this be 'looked up' in the (UPC data table)... returning the correct information.. ie.. description.. but then taking the price from the product table.. the query doesnt seem to let me be able to join the two tables.. no idea why!?
secondly.. I want to create a form.. for the 'sales transaction'.. when the user clicks 'pay' i want the form to clear and store all these items in the sales table.. (deducting the units in stock at the same time..)
I hope anyone has any suggestions.. or pointers! Many Thanks
:)
View 7 Replies
View Related
Apr 12, 2015
I want to create a simple data base, in which I have to record Payments received from each customer on daily basis. and on daily basis i want to generate a report for the transaction I have made. example of data table is as follow. payment mode is cash or bank transfer.how to generate reports for day to day transaction.
View 5 Replies
View Related
Jun 5, 2005
I am trying to create an Access application where income recognition is automatically calculated and stored at the end of the month based on established parameters. How can this be done? Does it require VBA programming?
View 1 Replies
View Related
Oct 27, 2005
After a lot of searching, printing and highlighting I want to double check that I have the right idea.
I have a simple inventory control database. From reading the forum, it seems the best approach is to drop the detail lines table of the purchase order (incoming) and the detail lines table of the sale invoices (outgoing) and make one table for all transactions.
The Transactions Table would also keep records of inventory adjustments from inventory counts, etc. Each transaction would be given a type so I know in which direction the stock count moves in (adding or subtracting) when I sum the totals for each item.
Otherwise, I need union queries to ensure all tables are considered in my calculations.
My database is simple (thankfully). Let me know that I am on the right track I like the solution but like any new student, a little reassurance is appreciated.
View 3 Replies
View Related
May 3, 2014
I have a parent record and child records. After creating the parent, there must be at least one child record.
On closing the form I could, in principle, delete the parent and child (or children), if the user changed his mind.
Or, I could presumably wrap this entire enterprise in a transaction, something along the lines referred to here: [URL] ....
View 1 Replies
View Related
Feb 22, 2008
Hi,
I have been using the following query, literally for years, without any changes. I run it from code using db.execute, and I do use the dbfailonerror option.
UPDATE TST3 SET [date] = Mid([timedate],8,2) & '/' & Mid([timedate],10,2) & '/' & Right([timedate],4), [time] = Left([timedate],2) & ':' & Mid([timedate],3,2) & ':' & Mid([timedate],5,2), Serial = [serial] & '3';
One of the things it does is to add a '3' to the end of the [serial]. [serial] is the primary key in the TST3 table. You might think that there would be a problem if, say, I have a list of serials containing
35
56
1
13
and I'm trying to update them to
353
563
13
133
But this has worked OK in the past. NOW I'm getting a KV Error when it tries to update the 1 to 13, because there's already a 13 in the table.
Even stranger, when the query fails, all the rows BEFORE the offending record DO get updated. So the query fails, and I end up with:
353
563
1
13
(and yes, I DO have dbfailonerror set)
So, it looks to me as if update queries are no longer running as transactions.
I am pretty certain that action queries have always been run transaction-wise in the past... if the query fails, the whole thing should fail. WHY is the transaction processing no longer working for this update query? Has anyone else noticed this?
I recently ran microsoft update and am now running
Access 2002 (10.6771.6830) SP3.
View 7 Replies
View Related
Aug 24, 2004
I am trying to create a suitable expression, either on the form itself or in the underlying query, that calculate the balance after each transaction, like a bank statement. What is the best way to do it?
Paul
View 2 Replies
View Related
Jun 26, 2015
I'm trying to extract invoice and payment transaction data from a very old version of quickbooks. This very old version allows for the export of customer and item lists to .iif format which I can view in Excel. However, there is no method of exporting transactions. All of my searches for how to extract the data have come up empty.
I am pretty sure that I once found a post relating to looping through the file line by line and converting the data using vba. I can't find that post anywhere either.
Any method of extracting data from a quickbook .qbw file. I'm willing to do a fair amount of manual parsing if I have to, but I need to get to the data first.
View 4 Replies
View Related
Oct 7, 2013
I have a table that contains records of transactions. I need to find the last record for each day.
DBTransIDtransDay UID
1931219/17/07 8:50 AM128
1932879/17/07 9:13 AM128
1932929/17/07 9:14 AM128
1933049/17/07 9:16 AM128
2388149/18/07 4:54 PM128
2388449/18/07 5:06 PM128
2388459/18/07 5:06 PM128
2388469/18/07 5:07 PM128
2389299/18/07 5:45 PM128
2389309/18/07 5:45 PM128
2389319/18/07 5:45 PM128
2391299/19/07 8:55 AM128
2391389/19/07 8:57 AM128
2391399/19/07 8:57 AM128
2391409/19/07 8:57 AM128
2391419/19/07 8:57 AM128
A script that I found that appeared to do what I needed it to do:
SELECT [TT Transactions - KT].DBTransID, [TT Transactions - KT].transDay
FROM [TT Transactions - KT]
JOIN (SELECT MAX(transDay) Max_transDay_By_Day
FROM [TT Transactions - KT]
GROUP BY convert(varchar, transDay, 112)) t2
ON t1.transDay = t2.Max_transDay_By_Day
But it returns a "Syntax error in FROM clause" that I can't figure out.
View 2 Replies
View Related
Jul 21, 2013
I created a report to show stock level and transactions. there are date, transaction, quantity and balance fields. the balance field show the total balance of the item at the moment. what I need is to show the balance at the transaction date.
View 7 Replies
View Related
Jul 31, 2013
I have a query which is compiling monthly transaction of fee, the students paying through voucher. To every month the query is building records for students (Grouped), my problem is i have to show the arrears in the same query which would be on minus/plus of paid amount from actual fee both are in this query i can populate the arrears by simply subtracting both fields but the thing is that i am looking for a arrears which would be the adding total of previous arrears as to date transaction.
View 5 Replies
View Related
May 11, 2015
I have a login screen that works fine but I need to lock some part of the system from normal users but I can't figure out how to track if the user who is logged on is security level is high and can authorize the transaction!!!
I have users table and in it I record the users, passwords, security level. So when someone logs on I have no way of knowing who they are and in order check their security level. I did it in the past by setting the C drive VOL into a value and search but as we use Citrix XenApp this is not possible the C drive is the server drive and if multiple users login to to that server then the VOL value would be wrong.
View 3 Replies
View Related
Apr 22, 2013
I have a database that I use to put in orders for our shop and keep track of our part informations as well as paint and packing materials. Everything works good on this but I am trying to create a table for adjusting quantities on hand for packing material based on the part quantities and for adjusting paint in stock based on information given to me after the job is run.
There is already a relationship between parts and packing material as well as parts and paint. When I put an order in I would like to have it adjust out that many packing materials that are related to that part. Once a job is run I need to be able to adjust out the amount of paint used.
For the paint side of this I want it to track the paint used by order, we are trying to get a grasp on how much paint we are using for parts so it is important for me to know how much and when.
I am thinking I need a table that connects paint to orders and has quantities in it, then create a query and do the calculations from there... I do not know how to accomplish that but it sounds like it could be right...
View 1 Replies
View Related
May 5, 2014
I wish to open a form based on the transaction table populate one of the foreign key fields with a selected value from a combo box from the switch board (with going to a mainform&subform). I know how to get the value of the combo box on the switchboard. I just know how to then store this value in the foreign key field. stSelectedCustomer = Forms![Switchboard].[CustomerID] gives me the value from the combo box on the switchboard. I then want to open the transaction form to open a new record and have Customer ID on this form be the stSelectedCustomer storing this value in the transaction table when the user enters values in the other fields.
View 2 Replies
View Related
Sep 8, 2013
I'm developing my own personal finance database and the part stumping me on the design is how to setup the categories per transaction. For example a primary category would be "home" and the secondary category would be "furnishings". How do I set this up? I'm getting stuck on how to tie it into the transaction table.
I'm familiar with writing SQL queries, but this is really my first stab at database design.
The relationship diagram is attached. The transaction table and the category tables are to the right of the diagram.
View 10 Replies
View Related
Apr 17, 2013
I want to make inventory fifo(first in first out) db. Example:
February
1- beginning balance : 800 unit @ 6$ per unit
4- received 200 unit @ 7$ per unit
10- received 200 unit @ 8$ per unit
11- issued 800 unit
12- received 400 unit @ 8$ per unit
20- issued 500 unit
25- returned 100 unit to storeroom to be recorded as latest issued prices
28- received 600 unit @ 9$ per unit
The report must be like pic attached
I dont know how to make transaction to do this....
My database in attachment
View 3 Replies
View Related
Aug 25, 2014
I have a form that requires at least a transaction code (from a combo box) and a total value field (numeric) to be entered.I have used the following code but when I click OK on the message box the form is closed.I want it to remain open so the user can enter the fields and properly save the record. As the code is in the Before Update event it does the same thing if I have Cancel = true.
Private Sub Form_BeforeUpdate(Cancel As Integer)
' Check to make sure the TransCode has been selected and the total field doesn't = zero
'If Me.cboName.ListIndex = -1 Then
' MsgBox "A value is required for ...."
' Me.cboName.SetFocus
' Cancel = True
[code]....
View 4 Replies
View Related
Aug 8, 2015
I have 7 fields in a listbox (1st is the ID, hidden) from the single table. 3 of these fields are foreign keys. How do I get them to display their related values instead of the foreign key value?
Background:I'm purpose-building my db to essentially track individually cataloged items, somewhat like a library system would. I have four main tables: tblMediaItems, tblUsers, tblTransactions, and tblLocations. Users wills be spending 90% of their time on the Details form for the particular media item record they're viewing.
I'm trying to show an item's transaction history on the main form. I added a listbox (because I liked that compact presentation style) and got the desired fields to show up from tblTransactions. I figured out how to accomplish this for a single column combo box with the wizard, but so far I don't see how I would do this with multiple fields in a listbox.
View 2 Replies
View Related
Nov 7, 2013
In Access column name is STKITEMNBR and data type is TEXT. 4/5 of data are numeric and 1/5 are alfa-numeric. One of data was 15E10 in Access, but was altered to 1.50E+11 when exporting out to Excel csv file. Because it was Stock Item Number it needed to stay the same as 15E10 in csv file.
View 14 Replies
View Related
