Queries :: Track Monthly Data For Several Thousand Products
Apr 17, 2014
I have a table that track monthly data for several thousand products.
The idea is to grab the first month and the first three months of sales for each product.
So I simply need to have a statement that scans through a predefined set of fields and seeks the first none zero value. This become the first month of sales (even if it is the 5th month of the year). It then needs to be able to grab the proceeding 2 months to create a sum of sales in the first 3 months. (however that would be for a second field, so presumably that is just a slightly more complex version of the same formula used to find the first month of sales).
View Replies
ADVERTISEMENT
Jun 15, 2014
I'm developing a database that tracks monthly metrics of employees. These employee are at several different locations.
Here's what I would like:
A table of the employees and their location.
A table of the metrics.
A form that you can select an employee and it would autofill the location and then you could fill out the metrics.
the ability to change an employee's location without it effecting past records.
A report, by month and location of the employee's metrics. (pretty sure I can do this on my own, just can't get to this point.)
I've tried to use a auto lookup query but that then changes the employees location on past records.
View 4 Replies
View Related
Apr 16, 2015
I have been trying to get this to work for days and I cant get it to work correctly. I have a query that I need to search only for the records that have 12,22 and 24 so in the fields criteria I have been trying
Total: Where
Criteria: In (12,22,24)
and it works BUT it only works for the first record it finds then it seems to skip and continue to search for all records after that.
View 3 Replies
View Related
Feb 21, 2014
I've been requested to make a bird count database.
The bird count data has been recorded on monthly papers like this:
[bird name] [week 1][week 2][week 3][week4]
The number of birds sighted for a given week is written in the [week x] columns.
What I need to do is make another column that shows whichever number is highest from columns [week 1], [week 2], [week 3], [week 4].
So for example:
Blackbird: Week1: 4, Week2: 2, Week3: 6, Week4: 2
highest: =6
View 5 Replies
View Related
Dec 6, 2013
As you know, the prices are, unfortunately, dynamic "objects".
So, I have a table for products:
tblProducts
ID_Product - Autonumber (PK)
ProductName
and a table for prices
tblProductsPrices
ID_ProductPrice - Autonumber (PK)
ID_Product - Number (FK on tblProducts
Price - Double
PriceDate - Date/Time
The problem: I have a certain date. I need to know the available prices at that date for all products.
I have a solution; but, for each product, I apply an external function that use two aggregate functions: DMax and Dlookup.
For my small tables, this isn't a problem but I like to know if it is a better way.
View 10 Replies
View Related
Sep 14, 2014
find only some clients that has only 4 types of products, but no other type of products.
Just to put it in a much easier way to understand. If I have to find only the client that did buy only 1 or more from the 4 products. Fridge, tv, dvd player, mobile phone. But I'm not interested in the clients that also did buy for example, laptop, pc, video cameras, etc. So if the client has only one of the 4 products, I want to list them all, did they buy another type of product too, then not.
How could I create a query that will show me only those clients?
View 4 Replies
View Related
Apr 8, 2013
Finally got my DB straight so I am not using calculated fields however can't figure out the best way to handle the Combo Box so I do not show closed records ( Item checked out and item returned).
Please see the attached table of materials checked out and materials returned. How to setup a query so that if an item has been returned and matches the item checked out, Both the original check-in and the matching return records are filtered and not displayed in the Combo Box.
Also, I am a little worried about partial returns as when an employee only returns half the amount.
View 14 Replies
View Related
Nov 9, 2014
I am trying to make a dsum for products coming in and coming out. find the example below
Products In
Apple 10
Mango 5
Products out
Apple 5
Now I want to show statement for sum as follows
Product. In. Out. Net in hand
Apple. 10. 5. 5
Mango. 5. 0. 5
But it is showing only
Product. In. Out. Net in hand
Apple. 10. 5. 5
what I understand is dsum cannot find the out field for mango as there is no field found for mango
View 14 Replies
View Related
Apr 1, 2014
I use access 2010. I have a form that allows the user to assign products to operations. Each operation can produce multiple products and each product can be produced at multiple operations. I manage this many-to-many relationship through the use of a third table that stores primary keys for associated products / operations.
tblOperation
tblProducts
tblOperationProducts
The query im trying to design will populate a listbox on the form that contains products which are available to be added to a given operation. In addition to the listbox there is also a combobox that allows the user to select the operation they want to add products to. As the user selects an operation, the query behind the listbox should return records for all products except the ones that have already been assigned to the operation selected in the combobox.
So far I've been able to make the listbox return all products always or return products that haven't been assigned to any operation at all, but I'm struggling to formulate the logic in my mind on how to show only products that have no association (through the tblOperationProducts table) with the operation selected in the forms combobox.
This is the query I have right now, it's not working:
Code:
Select tblProducts.ProductID
From tblProducts
EXCEPT
SELECT tblProducts.ProductID
FROM tblOperation RIGHT JOIN (tblProducts RIGHT JOIN tblOperationProductMM ON tblProducts.ProductID = tblOperationProductMM.ProductIDMM) ON tblOperation.OperationID = tblOperationProductMM.OperationIDMM
WHERE (((tblOperation.OperationID)=[Forms]![frmOperationProducts]![cboOperation]));
View 1 Replies
View Related
Apr 22, 2015
I have a query for loan calculation, fields are;
Loanamount
loandate
monthlyinstalment
what i want is that query to start subtracting lmonthlyinstalment from loanamount on monthly basis
View 3 Replies
View Related
Apr 3, 2014
I have a products form, we are a manufacturing company, with a listbox to show recent inventory transactions. This is based on a query which shows all transactions with the current part id, and that all works well and fine.
The problem is, I would like to limit this query to show only the last 10 transactions in the listbox and not make it editable, ie not enabled. I set the show only in query design view to 10 and it says in the sql statement select top 10, however, the listbox consistently shows all related records. What am I missing?
View 8 Replies
View Related
Jan 5, 2015
I was assigned by my manager to design an Access database system that is able to import all data from excel file monthly and creating charts & tables to analysis how each sales people and industry perform.
We originally have a big excel master sheet that has more than 10 sheets. I tried to import the current excel into access, but then i realized that this is not gonna work. because for next month, there will be new data and I can't do the whole import process over and over. Plus, after this system is designed, the users will be someone who has no knowledge in access, so i need to create a user-friendly system for them to use.
My questions is:since the data is always cumulative number, if I imported current excel file into access, when the next month comes, how to update the new data into excel. p.s. EXP. Mike's sale volume is different each month, and with the access system, for that column, it will be a cumulative number, like the total from the month of November to this month. how do i achieve this kind of update/import goal?I tried to link the excel to access, but by doing that, I will not be able to set relationship or change the attributes of any data type in access.
View 3 Replies
View Related
Mar 11, 2008
I have created a database that I am happy with, and I am about to receive information to populate it with. I know I am about to be asked to about security and logging of changes made to the data, which I know nothing about!!
As a result before I get into this part of my project I would like to know myself what is and what is not possible!!
Currently I have 2 tables and 3 forms, one of which is a sub form........ All changes will be done via the forms, adding new records, editing existing records or even deleting obsolete records.
•Is it possible to record any changes made to the live data?
oFor this to happen I presume that I need to setup users and access rights?
•What type of information can be saved if any?
oWhat form would this be saved in and where (a temp table?)?
While typing this a change of plan – if it is possible I am going to start building it regardless, as it will enhance my database.
Thanks
View 5 Replies
View Related
Apr 24, 2013
I have built a table off a form that is being used for record keeping and my question is.... This table will be constantly updated with new information so how can I make it to where when new info is added the old info will not be removed or written over?
View 7 Replies
View Related
Dec 19, 2012
Im working in MS Access 2003.Im creating an audit trail for users actions in a form, to monitor and keep track of what fields were updated/entered/removed from the database, these actions are essentially done from command buttons (and the audit trail, is coded on the button click)
However, I have a few search screens, that return a subform listing results. These datasheets returned are effectivly like opening the raw table and hence are editable, (permission to change is granted as they are admin only searches, yes, they can change the raw table)
How can I track the change to a raw table field, ie, if someone updates the field SURNAME from 'Smth' to 'Smith', is there something that triggers this (obviously if it was in an interface textbox, you could audit this) is their a field change. The only way i can suggest it make a recordset of the intial results, then compare that to the results in the table as they leve and compare the two.
View 7 Replies
View Related
Dec 26, 2007
I am re-designing a database for 2008 and trying to eliminate my Make Table Queries as I have found them to be somewhat consistant over the last year, particularily when the users do not open the database on a given date. It seems there should be a simple way to accomplish what I want but I am struggling and need some assistance.
I have attached a sample of a few tables from my database, Open Cases, Closed Cases, and Date Today. The Open and Closed tables change daily due to a Corporate download and contain several date fields which have different meanings. As new cases are opened, they go on the open table, and as an open case is closed, it moves to the closed table. The tbl_Date Today is pre-populated with dates of working days only. I have a query called "Count Of Shelf Comb" that counts the number of open cases as of today, which in truth is for all activity through the previous business day. What I want is to have a query that will show each date on the tbl_date today as well has what the total count of open cases was for that date......a permanent history of the amounts.
How can I accomplish this without using a "Make Table Query".
View 2 Replies
View Related
Aug 16, 2014
I want to keep track of when data in a form was last changed.I only need to track this when the form is closed. not every time a record is changed.
View 5 Replies
View Related
Nov 4, 2011
What I am trying to accomplish is import data from a CSV file every month and have it stored as a table and have that table automatically be converted into a printable invoice. I can do the import with no problem. But I am at a bit of a loss as to how I can go about creating the database.
There is only one client that I have to make up an invoice for, but will be more in the future. I need to store the data for each months invoice and then get yearly reports off that data.
#1, How would I go about connecting each months data?
#2, Should I be using a report to create the invoice layout?
#3, How do I go about automating creating the invoice from the imported data? (I am at an intermediate level with VBA, but access seems to be a whole different ball game)
View 5 Replies
View Related
May 5, 2014
I am constructing a database for a friend and need to filter data from a daily log to enable a report to be generated for each months actions. I have created fields of: entry date, month and year with the view of filtering by month and year. How do I perform this task?
View 3 Replies
View Related
Apr 13, 2014
I am looking for a data model to define and track proficiency/currency.
Example:
You earn a licence to do X. You need to renew your licence every 2 years or so. But also, to maintain currency, you need to do what the licence gives you the right to do often enough, or else you need to do a test.
Specifically, you need to do X a specified number of times N within, say, last 3 months at any one time to maintain currency. If you have lost your currency, you need to do a test/check. So at all times, you need to have N times on record within last 3 months, or a test.
In addition, to do X legally, you also need to document that you have done X at least 10 hours within, say, last 6 months, or that you have another test within those last 6 months. This is independet of the previous requirement (concerning the number of times you did X).
Both requirements must be fulfilled at all times for you to do X legally.
So, any suitable model out there? Essentially, something capable of defining and tracking currency for various ratings and license privileges of pilots would do just fine.
View 2 Replies
View Related
Oct 3, 2014
In the case of a system that keeps track of checking in and out (e.g. library books), where is the archive data stored? If a person makes 30 trips to the library, obviously one record will be the current visit - but where do you keep the 29 other visits?
Do you create a separate table for old check-outs: Current_Check_Out and Archive_Check_Out?
Or do you just leave the data in place and as-is: All_Check_Outs?
View 9 Replies
View Related
Jan 22, 2015
I'm building a report which should highlight/change text color in field values in a modified table which have changed relative to field values in an original table. I'm using the same conditional formatting on all my fields: e.g. on the field named [Primary Instructor] in the report on the modified table, I have "Value <> Reports![F2015original]![Primary Instructor]", where I reference an identical report on the original table. Both are loaded.
My problem: it's reading all values as changed/different, even when both visual inspection and copy/paste indicate that the values are identical. Thus my report highlights all values for the field, rather than only those which have changed. The reports have identical non-conditional formatting and data type.
View 3 Replies
View Related
Dec 13, 2014
How to generate either a form or report able to show me the history of data changes made by users in the fields of Access Objects. I'm more interested in Forms, since the users will use Forms to change data in the db.
Let me give an example:
My db has, for instance, a form named Frm1 and a form named Frm2. Both were built including several fields such as text boxes, combo boxes, etc.
So, if the user Paul first changes the field Color of the record Id 235 in the Frm1 from Green to Yellow, and then, down the road George changes the field Size of the record Id 14 in the Frm2 from Big to Small, I need to know what changes were made, by who, and when changes happened. Also, it is important to know if a record Id is added or excluded.
My final intent is to have a report/form that shows some sort of user activity log which I think could be like this:
Date--------Time----------User-----Form------RecId------Action------Field-----From-----------To
Dec12,14---15:12:35-----Paul-----Frm1--------235------Changed---Color----Green-----------Yellow
Dec18,14---07:05:19-----George--Frm2---------14------Changed---Size-----Big--------------Small
Dec22,14---09:23:59-----Sean----Frm2--------116------Deleted
Dec23,14---11:07:03-----Paul-----Frm2--------321------Added
etc...
By sorting this output by date, form and field it will easy to check/audit for user processing mistakes.
I know enough to build tables, queries, forms and reports, but don't know how to structure such thing. What kind of form event should I setup?
View 2 Replies
View Related
Aug 11, 2015
I have a user input form with a text box that stores a number. When the user inputs the number, I'd like it to display the comma separator (e.g. "1234" becomes "1,234". I've tried putting "#,###" and "#,##0." into the format property of the text box, but I still get 1234.
View 4 Replies
View Related
May 23, 2012
How do I create a daily table from monthly data? I have a monthly table and want to split it into a daily table by dividing each monthly value by the number of days in that month. I need this so I can compare the new daily values to other daily values.
View 1 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
