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?
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:
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
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?
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 '!'?
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,
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!
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 :)
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.
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?
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.
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.
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.
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?
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.
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.
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.
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.
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...
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.
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.
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....
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
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.
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.
