Sales Transaction Query
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 Replies
ADVERTISEMENT
Oct 11, 2007
Am creating a Product-Sales Database, and I would like the corresponding Sales made in the Sale Table to be automatically deducted or to be reflected in the Product Table. The product table contains all my stock and has a relationship with the Sales Table. The Sale Table does not necessarily include the Stock. How can I create possibly a Sales Form that will be used as an entry point for all the products (stock) sold and automatically register the sold products in the Sales Table and at the same time make the required adjustments in the Products Table.
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
Mar 30, 2013
i'm trying to create a query of all employees doesn't have any transaction for a certain range of date and will also shows the last transaction date they have.i have two databases one is the transaction file and the other is the user file.
View 7 Replies
View Related
Jan 16, 2006
Good afternoon
I have two tables - sales 2004 and 2005 - both showing account number, product, quantity, sales and margin. When I link account number and try to show both years the results are incorrect.....I know I am doing something wrong!! I have tried creating new table with blank fields for 2005 basing the table on 2004 and then updating 2005 - but no go.....
Help please
Many thanks
Catherine
View 4 Replies
View Related
Dec 8, 2014
I'm receiving an error indicating there is a data type mismatch when running a query named qappInventoryTakeOn.
Data is entered into the Inventory Transaction Form. If the transaction type is "Take On", when the update button is clicked the record will be saved to tblInventoryMovements and then qappInventoryTakeOn should run to update tblInventory, but I keep running into the aforementioned error.
View 2 Replies
View Related
May 29, 2006
Hello,
I need to write a query based on the higher MTD sales in the series of each fabrics within series of Sales Group and Prod Group . All info is from one table
Order by: Sales Group (alphabetical ord) , Prod Group (alphabetical ord) , sort Fabric Group based on the TOP MTD sales
Sales Gr: Active
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: a,b,c,d,e,f...
StyleNum: 1,2,3,4,5,6...(PK)
Sales Gr: Dance
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: a,b,c,d,e,f...
StyleNum: 1,2,3,4,5,6......(PK)
Sales Gr: Yoga
Prod gr: Adult, Girls, Plus
Fabric Gr: a,b,c,d,e,f...
StyleNum: 1,2,3,4,5,6......(PK)
Thank you
View 1 Replies
View Related
Mar 3, 2007
I have created a cross tab quarterly query to sjow the 4 quarters sales.
Problem is if there is no sales in one of the quaerters, the report based on that query gives an error as per attached photo and do not open.
any work around
View 1 Replies
View Related
Feb 27, 2008
I was wondering if there is a way to combine sales by month for a year where it would show the product then for say January and the total sales and so on for each month.
View 7 Replies
View Related
Oct 16, 2006
Hey, I am currently doing an A-level ICT project and seem to be stuck at the first hurdel. My problem being that i wish to update the 'Number of sales' field for all the staff in the 'Staff' table. I have tried serveral queries to count the number of sales, which are in the 'Tickets' table for each and indidual 'Staff ID'.
This was suscuessful although i am wondering how to transfer these values into the Staff.[Number of Sales]. Also some staff had the value of 0 so did not come up in results of the querie.
Is there any possible solution or is there a more simple way to solve the problem.
Many Thanks in advance Dan Parker
View 1 Replies
View Related
Feb 18, 2015
I'm making a query that has the following Fields
Product (Table PDZRN1)
Whse (Table PDZRN1)
Description (Table PDZRN1)
On Hand (Table PDZRN1)
Unit Cost (Table PDZRN1)
Unit Price (Table PDZRN1)
LastDate: Date (Table Sales_History) (using the Max function)
Where I am stuck is I want 4 additional columns to pull the last 3 fiscal years and total sales for each Product for the 3 previous fiscal years. The Field with sales amount is "Sales" it is located in Table "Sales_History", and the dates for the invoices are in the field "Date". I tried the following query, but I'm sure it didn't work because I failed to push the data back to the date field:
Field - FY2013Sales: Sales
Table - Sales_History
Total - Sum
Criteria - <= 04/01/2012 and <= 03/31/2013
how to link my criteria back to the "Date" field, or if I'm even going down the write path.
View 14 Replies
View Related
May 4, 2014
I have a table of end of week sales with ProductID, Volume_Sold, Year and WeekNo. I am about to create a historical table of RRP.
What is the best way to set this out so that I can query the two tables to that when I run a query over the two tables I get the correct price depending on the year and week number I am working with.
tblSales
Year int,
Week int,
CountryCode nvarchar (2),
StoreNo, nvarchar (35),
ProductId nvarchar (15),
Volume_Sold int;
My new table
"tblRRP"
Could contain
Year int,
Week int,
CountryCode nvarchar (2),
ProductId nvarchar (15),
RRP float;
The table is only appended to when the price changes. So some products may have a price increase 2 or 3 times a year others once every 18 months. And if the price changes any calculations need to allow for the 2 or 3 different RRPs the Product may have had during the queried period.
So that when I do year on year revenue calculations it works properly.
View 6 Replies
View Related
Jan 2, 2014
I'm trying to create a simple sales query by manufacture by month. I want the month as the column headers (with a total YTD Column). Mfg's are the row headers. The query is already completed and I figured out how to do the row total but can't figure out how to create total column. Can I not add a total column in design view? How would I do that?
View 10 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
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 1 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
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
