Query/Update Sales Values For Individual Staff
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 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
Jan 8, 2015
I have a database where I record grades for my students. I would like to create a macro so that every time I record a grade as an "F", the student will receive an email that will contain an attachment of their scores. A lot of the information I want to include in this email is in several different tables. So to make it easier, I have created a query that lists the following:
Students name (SName)
Student's email (SEmail)
ID# of the form I use to record their grades (ID)
ID# of the studen'ts work (WorkID)
Date of their work (CreatedDate)
Date I graded their work (AuditDate)
Grade="F" (Accuracy)
How can I create something that will automate emailing of this information--to the individual student with only their information? I am using Access 2010. If I can't automatically send the emails, I would like to create a button in my audit screen that will send a report containing that student's information to them.
View 1 Replies
View Related
Oct 3, 2007
I have a database where two tables contain information that I need to update based on the 4 right most digits of a field. There are only 11 values that will need to be updated out of a large list of values. I'm not quite sure how to set up the update query so that I can do this.
View 1 Replies
View Related
Feb 3, 2014
I have created a database that generates a report with customer debts. I have a lot of customers and in a report each page is a different customer with individual debts info. What i would like to do is to create a button and distribute individual report page as pdf to individual customer by email. I'm using Lotus notes. I have searched the forums i found something but it was not what i was looking for.
View 1 Replies
View Related
Aug 13, 2007
Hi folks
I have what I reckon is a complex problem that I need to solve for my warehouse.
We hold replacement parts for the machines we sell. Some parts can be subsititued for others, so where we possible we use these parts to simplify our warehousing. Every day we get a file with demand for each part in and we look to see where we could move this demand to for those parts with a common alternative. When we find one we remove the demand from that part in the database and add it to the demand for the common part.
In operational terms we upload the demand file (.xls) to a table of the following structure
Date_added - Date
Part_number - Text
QTY - number
Thus the table holds daily demand by part. What I would like to do is use a update query to:
a) Identify the parts that have common parts to which they can be moved.
b) Add that demand to that of the common part today.
c) remove it from the original part (set to zero).
Idenitfying the parts and restricting to todays date is not a problem, however the additions and deletions are.
Any suggestions?
Thanks in advance!!
View 1 Replies
View Related
May 30, 2005
Hi, not sure if this is possible or the most economical approach but here goes:
I have a lookup table (tblHolidays) with a list of holiday dates (fieldname "HolidayDate") in it.
I need an update query that can check all "StartDate" values on a table called "tblMasterLog" and where this date matches the ones on "tblHolidays", will add 1 day to the "StartDate" and then keep repeating until there are no more dates to change.
Can this be done?!
Thanks in advance
View 2 Replies
View Related
Jul 2, 2015
What I have a a form that my lab supervisor would use. That person selects the records that are to be modified and assigns work, completes work or otherwise updates the status of the records. One of the options is to mark a record as not having a sample here if it has already been marked as having been here. So essentially, I want to modify the record to change the sample arrival date and sample number field (which is a foreign key field) to null or empty. I have created a delete query that deletes the corresponding record just fine.I just am having difficulty updating the two fields mentioned above. When the supervisor selects the record the primary key for that record is also picked up so it is easy to know exactly what record to adjust. The query returns the information to be updated just fine, it just doesn't do that. Here is the sql of the query. As I said, I don't care if the fields are empty or null. Also the fields aren't required.
Code:
UPDATE TestRequestTable SET TestRequestTable.SampleLocation = "", TestRequestTable.SampleArrivalDate = ""
WHERE (((TestRequestTable.TestRequestNumber)=[Forms]![LabScheduleForm]![TRNumberCombo]));
View 3 Replies
View Related
Jan 2, 2014
What query would I use to build a date in m/d/yyyy format using month, day and year values from another field?
View 2 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
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
Sep 23, 2014
Let's say Table (T1) has fields F1 and F2. After a massive update to T1, there are some records with F1 = "" because a Dlookup using F2 as criteria to another Table (T2) resulted in a null. I created a select query to show unique T1F2 values where T1F1 = "". The user can use this query to find out which F2 values need to be added to T2.
How do I create an update query that will update T1F1 with values from T2 using the T1F2 results from the select query to again use the Dlookup to T2 (of course after T2 has been updated to contain the missing F2's)?
View 1 Replies
View Related
Oct 25, 2014
Access doesn't seem to recognize the values in a table I am updating. I am using an update query to revise prices in a column Titled "Price". I am comparing the prices in the Price column to the prices in another column with a simple IIF statement (i.e., IIF([Price]=[Column2Price],"Yes","No"). However, oddly, the formula produces a "No" result, even when the prices are plainly the same values, and all formatting for both columns is the same. Whenever I manually type over the price with the same value, the formula works and Access seems to work as intended. For example, I simply type "12.95" over the same value that is displaying in the column that I updated that is already showing "12.95". Then, after I do that, the formula works and produces a "Yes" value.
View 3 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
Sep 18, 2013
I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN)
SET TableA.HCC = TableB.HCC
WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL
ORDER BY TableB.HCC, TableA.CDN;
View 2 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
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 13, 2007
Hi everybody,
Recently I created a database for staff details in my company and I used OLE Object in Data type to make it. The problem now there are more than 500 employees in the company so it is very difficult to scan one by one photo. Is there any way that once I click on command button photo will be detected from scanner and saved directly in the field instead of doing it manually.
Thanks and regards
View 1 Replies
View Related
Aug 2, 2007
Hi all,
I'm relatively new to Access and would like a few pointers...
I have been asked by work to create a schedule database. We would like to be able to put in dates and see who's working on that day, pull up an individual and see his/hers rota for a week. Also see if they have meetings, annual leave, sickness etc.... We want it to be a very primitive WFM tool. Is this possible and is it relatively easy? The database doesn't have to create the shifts, they will be imported from Excel....
Which leads me to believe I need to think of it differently to excel... Attached is an excel rota, which would need to be importred - but maybe I need to be think a lot differently.
I know thats a big question but any help would be appreciated, or pointers to more resources... Thanks
Neil
View 4 Replies
View Related
Feb 26, 2006
Hi All,
I need a query to tell me how many staff are on holiday on the same day..here what i have so far.
A query with name, startholsdate,endholsdate, totaldays, etc, etc,,
records showing; employee A books 01/01/06 to 14/01/06 and employee B books 10/01/06 to 24/01/06 C books 01/02/06 to 10/02/06 and so on,
I need to show how many employees are off on what days eg employee A and B are on hols between 10/01/06 and 14/01/06, so only 3 more staff may take days off between the 10th and 14th. I have tried all sorts of calculations but to no avail? can anyone help..
thanks
View 2 Replies
View Related
Jun 27, 2006
Hello Everyone,I am trying to perform a sum of records for the table belowSeatType| Ext rent | |Internal rent | Work Transfer 2 2VendorOther 2 3LOB BRF GTI BRF 2 5Displacement so that when I run the query I get the following followingSeatType| Ext rent| |Internal rent| | Total |Work Transfer 2 2 4VendorOther 2 3 5LOB BRF GTI BRF 2 5 7Displaceme What I mean is that from the first table how is it possible to get the results below. The query would generate a new column and place the sum of the data in the each row(record) in the same row in the total column. I would be really grateful. Have been bashing my head all day over this. PS I dont know any VBA. Is it possible without VBA?Thank you.
View 5 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
Jan 29, 2015
I have limited access knowledge and everything I learned about access was from youtube videos and reading online. I have only used the features that do not require coding/programming (tables/queries/reports).
this is my problem. I am the secretary of a social boat club (about 300 members) in charge of producing invoices. I created a my member table with general data, applied a query to create a Dues&Fees Table and then I created an Invoice report from this table..etc. Right now I have a final report, with 300 invoices that i could easily print and mail. However, people are asking to have their invoice emailed and I was wondering if there is a way to mass email each individual invoice to each individual member of the club.
View 2 Replies
View Related
Aug 10, 2005
Hi all,
I'm a bit stuck with a db relationship design.
I need to hold information in a staff table about a person's Line-manager.
My initial idea was to just have the Staff Table and use the PK of a member who is a Line-manager as a FK in the Line-manager field in the same table for their staff.
I then came across a problem.
I need to be able to select all the staff under a Line-manager. For example a head of department may have 3 team leaders who each manage 10 staff. I need to know all of them, not just the 3 team leaders who are directly managed by the HoD but their staff as well.
As it happens there is no rigidgly defined managment structure per se for this organisation which makes things even more difficult.
I assume there is a reasonably standard way of doing this as all organisations have some form of line-manager/staff relationship. Anyone care to enlighten me?
Cheers
TS
View 6 Replies
View Related
