Please find attached a mdb (2000) which includes 2 tables and 2 querys
If you open "QryProductsComboSelections" You will see that some product ID's have duplicate entries.
what I am trying to do is allow for contract pricing updates to be added with an affective date so they will not display until the affective date but also those old contract prices that have a date less than the current affective date will also be hidden thereby always limiting it to one record even if the product only has one pricing entry.
I can do this with a boolean "PriceClosed" but it would only be a last resort as I would like to automate this so it requires no user intervention other than adding the new price for A given date.
I have spent hours playing with this so rather than through the comp out the window Thought I would do the sensible thing and come and ask the experts.
Hope somebody can help as done a search and got loads of unrelated results.
I am trying to set up a database to track truck maintenance, repair, and inspection records for a trucking company. Parts are ordered from vendors, and I have forms set up to facilitate the entry of the information regarding those orders. The problematic form is based on a query. In that query (this form and query deals with the purchase details - product, price, extended price), the product ID is gathered from the inventory transaction table, the product name and price are gathered from the product table (I did this so that price would enter automatically).
Of course, when I change the price, it is changed across all related orders, including those that have been saved previously. I know that the problem more than likely stems from having the info pulled from the products table. I'm just at a loss as to any other option to have the price automatically pulled.
I'm trying to do a lookup between two tables, whereas table A includes the product ID and table B includes the price. Is it possible to create a field in table A with a DLookup function on the product ID to get the product price from table B?
Whenever I try to type in the DLookup function, it does not calculate.
In my database i have a field called product code which the user enters the code as T0001, T0002, T0003 and so on for each record.
would it be possible when adding a new record for that field to auto fill the product code as T0004 for example, so the user doesnt have to type the code in all the time.
I need to make an order purchase form like i have customer table and product table, i want to make a form which creates order for a specific customer and I can add as much items as i want and than calculate auto the total price, and an option to make a report for it.
I am building a database in Access which I would like to eventually link to my website in order to sell hotel bookings online. I believe I have set all the fields up properly but when it comes time to set the price I'm lost. Each hotel has about 3-5 different prices throughout the year depending on the date. How can I design a table(s) to accommodate this? Any help is appreciated.
I am trying to create a simple database to keep track of computer components that I order for custom PCs. The data is fairly basic Quantity, Description, Stock Code, Delivery Status, Cost, Cost+VAT, Date Delivered. The list is currently 500+ rows of data. I simply cut and paste this info directly from my online invoices.
What I want is to be able find the the last price paid for an item (often the same items have different prices week by week) Eventually I would like to be able to create a rough quote using the latest prices (plus mark up) and also view a price history.
I would also like to include a few pictures as URL links to the suppliers website ????
At present the data is in Excel and linked to a single table in Access (but this can be changed). I realise that I have to group the records somehow but cannot get it to work. At present it lists every record either in date order or product order.
The stock code should not change, but product descriptions do. So any links to pics would be based on stock codes
Someone please tell me how to display the full list of products but only the latest price/date
If you think the current format is rubbish then feel free to suggest an alternative. I much prefer Excel but have never really got to grips with Access (used to be a vba programmer with Excel). To link to pictures and hide data that customers might see Access seems the obvious choice. So I try again!!!!!
I have a table called Books, in that table there is 4 columns ChapterName, Auther, ITEM, Price.
Each book has a item number, and each book has a few records with the same data, just the first column is different where its the ChapterName, each book has a price, but only once, meaning in the first record of each book it will be a price in the column price
Now I want a Query where i can get which book dont have a price at all, and which book has more than once a price, how can i do that?
I need a (group by?) query that shows my items only by the last updated price by date.
Basically i need to show the last date. (MaxDate?)
I keep a database for my restaurant of my food items and change their price everyday. i now have a table that archives individual items by date, price and company in a subform.
I am trying to kick out an item or inventory sheet by creating this query but i cant seem to group by last date. i have all my items showing up multiple times (as many as i entered) i have been easily able to create an inventory report off a query i used with only one date that i would change. but now i want to archive prices for charts.
i have tried max dates on item_id fields and date fields and nothing seems to work.
I have a database I'm creating for DVD/blu-ray sales. I currently have a order system in place in a subform, which uses a combo box to find the customer and fills in all the information in the rest of the subform. One piece of information is the date of sale.
I need to be able to have the ability to change the price of the DVDs and have a history of sales with the right price. The easiest way to do this seemed to be by Date of sale. So the price is linked to the date of sale, when the price changes, all orders before that change use the old price, after use the current price.
I was wondering if there is anyway to have a second subform on the same page and us the date of sale from subform1 and have it display the price in the second subform?
I am a new Access user. I have created a database to track and inventory animals brought to a local animal shelter. The database includes information such as when the animal was brought in, who dropped it off, when it was adopted (Hopefully!), etc. The database is being used by people who have limited computer experience and the challenge has been to automate it and simplify it as much as possible. I have created several Switchboard pages to try and accomplish this.
I have done research through Google and MS support and have found little help with a solution to this problem:
When an animal comes to the shelter, it is held for three days before it becomes available for adoption. I have created seperate fields for Date In Day, Date In Month and Date In Year. There is also a field detailing the animals adoptability status. This field is left blank in the intake process, and is then manually updated to Adoptable, Biter, etc., after the three day period. Is there a way to automate the Adoptability Assessment Field (which is a Lookup Table Field) so that it changes from blank to Adoptable after the three day period? (We have very few animals come in that are marked as Biters, so I just need the field to change over to Adoptable after three days have passed. Unless a solution can be presented to filter animals with Biter status out of the solution.)
Hi there all, I tried the search option on this board but maybe its just that i dont know how to ask the question. I have a text box on my form with and afterupdate event that inputs todays date in to another text box on the same form, can this be set so that once that date is apperas in the text box that no further updates to the date can take place.
If I had a textbox [txtTotalDay] which its days/Time was in decimals and a start textbox [txtStartDate] and a start time and number of shifts the product can run how can I accomplish this?
I would like to create a memo field which automatically enters the date prior to any text input from the user. Also allow dated updates in the same field
I'm trying to build a query that adds up the total sales for a given product per date. The problem I'm running into is some products have $0 sales on a given day and don't appear in the table I'm querying. How would I get the query to add a record for that day for the given product with 0 in the sales field?
To give more details, I currently have one table showing the sales data. It has a record for every sale that includes the product of the sale, the amount, and the date. In the query, I'm summing the sales for a given day and product. Then I plan to export to Excel where I will run additional analysis.
If there are no sales for the product in a day, I'd like it to list the date and product with a $0 in the sales column.
Any way to filter the average price of of a Product within the last 5 occurences (Line Items). It would pull a week back so WHERE: Between Now()-7 and Now(). Example:
I atrying to make a query that shows the price for a product, based on two parameters.
Parameter one is a product code. Parameter two is a date. This date falls between two dates.
I have one list (table) where is product code and invoice date.
The second list (table) I have, contains product code, and price valid from date, and price valid to date columns. This price valid to date is often not filled, and the price I still valid as we speak. If the date is filled there is often a new entry with an updated price. But sometimes, even if there is a new entry in the table, the date 'valid to date' is sometimes also not filled.
I would like Access to show me the valid price for the specific product. What criteria should I give in the macro, in order that Access shows what I want?
For illustration purposes, a small overview of my table:
Product, invoice date, price according to price list AAAA, 02.05.2012, ???? AAAA, 01.08.2012, ???? BBBB, 10.06.2013, ???? AAAA, 31.10.2013, ???? AAAA, 16.11.2013, ????
If you happen to know how this search can be performed in Excel, I am of course also happy to read that. (But my index, or Vlookup functions, give only the first possible result in the table. As I do not know how to give in the date parameter.)
I use the Date and Time Picker. i know that i can get many other calendars, however, my theory is that i would rather use something that is directly supported by microsoft. but as i found out now that may not be the case.
on of my users updated his Office 2003 after he was having problems using my database (created on 2002 sp3). i know that the same database works on 2003 sp1. however, now microsoft is serving 2003 sp2. so after the update he he can't use the database.
so my questions:
1. how do i generally prevent users from getting update errors. i would like to atleast alert them with a meaningful message that their version of Office needs to be updated.
2. is this true that the Date and Time Picker doesn't work anymore? and if so, why isn't backwards compatible? and how do i prevent this from happening with other controls or scripts?
Working in Access 2007 - I would like to have an updatable calendar in the header section of a form, and when this is changed by the user I would like the subforms (there are several) in the details section to be updated with various appointments with dates corresponding to the date selected in the header.
I have a form with subform . I want to calculate purchase quantity with criteria of product and quanties before sale date. If i use with specified date it gives correct result.but if i use field address it ignores date criteria
I create a database through ms access and there have a birth date box and admission date. Another box for Age.I want to see the age in month or year figure in to the age box when I go next field. Which will be calculate from admission date to birth date.
I am building a database to track contract of employees so that I can know which contracts are valid and which are expired.
My table has the following fields:
ID (Primary key) Employee ID (Foreign key to link to the employee table) Start_Date End_Date Status (Either valid or expired) Challenge
I want when I enter the end date, the system checks the end date against the current date and fills in the status field with either valid or expired as appropriate. For instance if the contract end date is March 10,2016, the status must be filled in the word valid.
We use access to enter our service tickets in at work.What we have are three date fields.
Call Date Start Date End Date
We are 24/7 operation.Currently all 3 just autopopulate with the current date.What i would like to do is ADD a CHECKBOX next to each Date Field.And make it work like this.
1. let them autopopulate as they are currently 2. if you end the call AFTER MIDNIGHT (the next day). CHECKING the box would automatically populate yesterdays date in each of the fields that has the check box CHECKED
Good day all. I am very new to Access and have a question ref date/time. I would like to have two fields fill with the "current" date/time when a user clicks on the empty field. Any help would be great!! Thanks all and take care.
