I am trying to automate a process where we have 1000's units available on a date eg 01/01/06 - 05/01/06 this is produced in a reprot via a prehistoric system we use.
The report shows the start date and the duration the unit is in use. per each order
I then use excel to populate when the unit is in use. With all of 2006 date's along the top and use an if statement to populate when the unit is in use. Then i sum all like for like units together.
Is there an easy way of doing this in access?
where i am failing is populating when an individual unit is available with out using excel. excell is on the vege of falling over now due to the volume so am desperate for alternatives
it's my first time here and the reason is because i have a problem with a query. I have a database of a storehouse. Each storehouse can be in one city. Storehouses have 5 products A, B, C, D, and E. A storehouse may have more, less or equal quantities of a product than it needs. I want to write an SQL query that shares the quantities of a product among storehouses. I tried to write but it was to difficult than i thought, so i ask your help. I attach the database for your convinience. :)
Hi There, I have created this query and i want to return the top 5 suppliers per LOB. It is returning something but it is not the top 5. I want to limit only to return the top 5. Pls. help and thanks. See attached file.
Hi, I'm not even sure how to describe my problem in the proper terms in order to look it up in any faq or tutorial.
I have one table, customers. Their name, number, etc. Another table, products: Name, id, department, etc. And the final table being orders. Customer bought product when, etc.
However, I'm only able to attach one product to each customer per order. I need to be able to attach any number of products, and I don't know how to go about doing that.
Also, when I create a form for entering all the data to these tables, the information doesn't stick. I create a form using the order table, enter the order and customer information, and have the products as a subform. The information will stick for each table, but they won't connect to each other. If that makes any sense...
Any pointers would be great thanks, I don't even know where to start.
I have a form orders with a subform orderdetails where we can order more than one product. When the order is finished you save it with a save button where it will also automathically mark the products ordered as not available. Now, this code works, the problem is that it only marks one product. how do you make it mark all the products that where ordered (in orderdetails - the subform)? :
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 'set values here! 'loop? Dim stAvai As String Dim strSetProd As String
Hi everyone, I am having a lot of trouble doing something that is probably very simple.
My scenario is customers order products, lots of products, now i need to know the quantities of these products for all my customers. It doesnt have to be by customer name, all i need is the product and the Quantities.
I can produce a Report that contains what i need in it but i would like to summarise it.
My report now has the PRODUCT and the QUANTITY for each product that was entered in the order, Problem is the Same product comes up all the time in the report.
For example Product= Tray Lasagne Quantity = 1 Product= Tray Lasagne Quantity = 2 etc...
And it has a continues list in the report, i would like to group the products so that it shows like this on the report:
I am using Access 2010. I have 4 columns in my file. They are "Code", "Item", "Qty", and "Amount" . I have 5 products with my own codes. It means the codes are set by me. For example, 00001 for ruler, 00101 for book, 00201 for pencil, 00301 for rubber, 00401 for pen. My wish is to link the code and product together. It means that when I sell a pen, I must type the code for pen (in the "code column") and I must type pen in "Item Column", so I think it really takes long to do this because I will really make a mistake because the code and item are not linked at all. What I mean is that when I fill or know the code, I will get the product automatically in the "Item Column" or vice versa.
Now I have set the code 00401 for pen.
So when I need to fill the information. I wish to fill the code in the "Code Column" and then the word pen is automatically shown in the "Item Column". In this case, I will be able to get right code linked to its product.
I would love to publish my product catalog from my access database on a website. it should be a browsable catalog with search field on article number range, product group, product type and should be able to display product pictures.Within my access database i have a link to the product picture stored by filename. I'm looking for an easy solution to do this, i'm using mysql as database on my hosting provider.I can do the export to mysql myself (interfacing between access and mysql) but am looking for some hot software to display the catalog easily without programming the front website myself. I already have oscommerce installed but don't want a webshop and don't know how to disactivate all the features within oscommerce to only retain the article browsing.Thanks if you have a hint
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.
Each Product can be in the table mulitiple times depending on how many Build ID's it has. So if Product 123456 has Build ID's G004, E818, N005, F813, D024, C879 it will show up one time each for each Build ID. What I want to query is unique Products that are for Build ID's D024 & C879 only and not for G004, E818, N005, & F813.
How can I accomplish this with a query. I am sure I am making this harder than it is but I sure need help.
I'm experimenting with the database attached (Access 2003) and on the Order Deatails subform I would like to be able to view only the products for the related CustomerID!
Any pointers on how to do this - very much appreciated :D !
Thanks,
Mary
(PS Some non-essential db components have been deleted in order to decrease size for upload)
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?
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.
We have created a database (electrical contractor) of all our customers, invoices, work orders, etc. and been using since 2000. I have created 'not billed' reports and queries for specific types of jobs. Next step for me has been to create a db for our products we purchase from vendors. they can be the same product from different vendors, or same type of product from same vendor but different manufacturer.
We use this to order inventory, check contract prices once we receive invoices from vendor, and provide estimates to our customers so, there are many people looking to search different pieces. I have a good product base entered, and have been looking through sample templates to try to find a way to bring up similar items when you find 'widget1 from vendor1' i would like to click or show somehow 'related items' window?
I am creating an EPOS system for a bookstore and I have a many to many relationship between the transactions table (tbl_transactions) and the products table (tbl_products) using a link table (tbl_linktblproductstransac). The below diagram shows what i mean below:
I made it many to many because 1 transaction can have many products on it and 1 product can be on many transactions.
Now what I need to know is how will i make records with more than one product on one transaction (in the table itself or in a transaction form). I would really appreciate if someone could help. Thanks :) and have a good Easter
I have an Access database that contains Orders and its delivery status with the sub table Products that have relative records of the order, I want to Auto Create the New Order with the Same Products (sub tables that have foreign key of Order Id) on the order due date, how to do that so whenever the order is due, it will be auto created with the same specs which i will change manually!
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]));
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).
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?
I am trying to put together a database that tracks individual clients and what products they selected for a finance company. I have setup the CLIENTS list but because each client will have different (and multiple) services I wanted to add the SERVICES list as a separate item and then link each product to the individuals that each record relates to. There may also be two clients on the one product so again it would be ideal if the product (i.e a Home Loan product) was setup in the SERVICES list, and then I could link One or Two clients to that individual mortgage record using their data which is stored in the CLIENTS list.
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code: Between [StartDate:] And [EndDate:]
And
Code: Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
I have made a database of locations. Each location has a subform which lists the "products" at this location. There are only about 5 sorts of product, and these are in a look-up table.
I would like to add the price of these products, so that when I open a different form/report, I can see how much money is spent at each location, etc etc. When I add a new product to the list at the location, the price automatically is logged somewhere on the database. I would also like to add another product, which is delivery. The price shouldn't be visible on the subform of the location form.
The thing that bothers me, is that the database is already filled with data. How can I add these details so that existing products have prices attached to them without exporting and reimporting all the data? There are other linked tables which would suffer.
I've tried to look at the sample databases on Access but I am, sadly, none the wiser.
I am practising making relational databases as I haven't used them before.
The mock database I am creating is based around a cleaning company.
I am trying to make a form which will display all of the bookings a certain cleaner has in the future. I want it to be able to display a booking along with the services (Windows cleaning, vacuum cleaning etc...) the customer wants.
At the moment, it is displaying each service (Window cleaning, vacuum cleaning etc...) as separate result on the form and it is not combined. They both have the same booking_ID from the booking table. How can I combine them?
It is far easier to understand what I mean by looking at the database (Attached). Take a look at the form I have created and then click the > arrow at the bottom to see what I mean.
