I hope this amendment to the code for the file attachment that doing a cumulative collection of the items purchased, but only "product code and place of storage," where the code works efficiently with the condition class code but I can not be modified to work two conditions together.
View RepliesI have a database that monitors vehicle maintenance, servicing schedules and stuff like that.
My problem is we purchase items for the vehicles and I am trying to link the items purchased against the vehicles, the purchased items need to show description, cost, invoice number our purchase number, supplier.
The problem comes when we have just stock items recieved that do not have any relation to the vehicles in the main table.
I want to know the "cummulative sum of a calculated field".
Given Lookup table : A = U, V, W | B = X, Y, Z
Field C = iif([A]="U",10, iif([A]="V",20, iif([A]="W",30, 0)))
Field D = iif([B]="X",10, iif([B]="Y",20, iif([B]="Z",30, 0)))
Field E = C+D
Field F = Cummulative sum of "Field E". What would be the expression for the cummulative sum here ?
---------------------------------------------------------------------------------
I tried F = Sum(C+D), but it shows me error
---------------------------------------------------------------------------------
I just want to know how to manage items in set and individual item. Suppose my product list are
individual items = A,B,C,D,E,F,G,H,I,J,K
and 5 pc set = A,B,C,D,F
and 3 pc set = G,H,K
How should I design the table. Previously I designed the table for individual items and whenever orders for set is placed user had to enter individual items with quantity.
E.g. order is for 5 pc set = 3000
A=3000
B=3000
C=3000
D=3000
F=3000
Now I need to just say 5 pc set and it should be automatically populated. And also if order have combination of individual and set items.
I have a production application in which i have a table named daily_production with fields as ( prod_id, date, productname, qty ). Now I want a daily production query as
date : xx-xx-xxxx
productname | qty | monthlysum(for thsi product) | daily average |
I want this on a single query so that i can make a report out of this .
I am new to Access. I am creating a simple order/payment database.
I have tables for the orders and payments and have a relationship setup.
I created an order form with a subform for the payments.
[URL] .....
I have a project to take. It relates to school fee payments by students. I have created tables, query and forms for the same. The database is able to calculate the Balance using the query. Now my major issue is how do I go about Calculating the Balance for the second fee payment, given that the amount payable on the next payment will be the previous balance. I have tried to think using the nested if to try it, but invain!
View 4 Replies View RelatedI have created 3 tables for simple inventory.
a) Item details
b) stock in
c) stock out
All I simply want to do is create a new table by item id to show balance of stock.
I have 3 tables :
table 1 empno Employee Nomber
empname Employee name
salary basic Salary
bonus
deduction
iddate month nomber
table 2 is bonus table id
empno
bonus amount
iddate
table 3 is deduction tableid
empno
deduction amount
iddate
Now i need is to transfer the data from bonus table and from deduction table to the table 1 if the empno and iddate are equal on the both tables
What I mean for ex if i create query and when i enter the employee number directly it will give me his bonus if he has and his deduction also if he has, so how can i do it?
This is sort of a general how to fix it question. I have a database that holds all of my companies information regarding policies.
For accounting reasons, they have seperated some policies and created an entire new database. So there is Company 01 and Company 02.
I have been asked to create some reports comparing the data in both Companies. So far I've been able to get quick reports out but merging the companies information into a table and running the report from there. The only thing I don't like about that is the information loses it's "Live" factor. It also seems like a amateur way of manipulating data.
An example (one I'm working on now) is my boss wants to know how many policies have been cancelled by our clients and compare them between the two companies. I can get a Count for Company 01 and a Count for Company 02. But when I compare them in a third query, I only show where clients cancelled in both Company 01 and Company 02. I need to show all the clients and 01 and 02. I'm ok with the field showing up blank or Null.
If someone can help with that immediate problem that would be great. But I'm also looking for some insight on how to have these two tables work better together. If I had created the program, I would have had both companies in one database, and just have an ID for Company 01 and Company 02.
Does it seem right to just set up a Make Table query and make some macros to keep it updated? I would have to append one table to the other. And I would have to do this as much as on an hourly basis if my bosses want up to date information.
Can I just say....Grrrrr? Thanks for listening.
hello guys,
hope you can help cause this is turning me crazy.
it's an inventory issue. i have two tables:
1) StockIN: ProductID, PurchaseDate, PricePurchased
2) StockOUT: ProductID, OrderedDate, QtyOrdered
Throught 01 year, I can make 10 purchases of pens with different prices.
I need to run a query of an individual department's consumption of pens in the month of June for example. I can get the right qty, no problem. But for $$$ purposes, I needed the query to return the most recent PricePurchased as compared to the OrderedDate.
Any ideas??
Thanks in advance!
Tiago.
I have a database that i am trying to clear out old customers that have't bought something in the last 6 months.
I have a table with the customer details in and another table with the purchases in.
I have been using the folowing criteria:
Not Between Date() And #01/05/2007#
But i think this still still shows customers who have actually bought more recent than 01/05/2007.
Is there a way to just show customers who have not purchased anything within the last 6 months of the date the query is run on and to only show their last transaction date.
I have a listbox control on my form which works in conjunction with my search field on the first form. I can add new items with FRM_ItemsAdd. I can delete records by pressing the delete button.
However now I want the ability to Edit items in the list. How can I tell access I want to edit a record?
Attached is my access db.
I have a query that I would like to put a count into but I don't know how
1. count how many line items there are in the query
2 count how many line items there are where the "days late" are
I'm creating a database for my job. I have 4 tables with information. When I need to deliver some items i must know which items to take, They are in those 4 tables.
I want to make it that i can click a menu on my switchboard that i see a report with all the information with a clickbox behind it. If i select this box and click a button generate it creates a report with all the items i've selected with the select box.
So in short:
1.) How do I create a table with all the items from the other tables that are automatic updated when insert a items in one of those 4 tables.
2.) How do I create a report where i can select some items that are generating a report with the selected items.
Im looking at creating an MS Access database for manufacturing items. Ill need to be able to create default items to be made and also use this to assign members of a team a list of items to make for a current day of the week which i can then input back into the database to keep it up to date with what they have made, have they kept to targets etc....
View 9 Replies View RelatedI work in the Insurance Industry and I am having a hard time trying to find the formula for finding the answer to the below issue I am having.
In a table I have 4 columns:
1st: Total # of Claims (Claim Count)
2nd: Total # of Items (Item Count)
3rd: Item % of Claim Count
4th: Average # of Items per Claim (This is the number which I am trying to get)
Is there a formula which could give me the "Average # of Items per Claim"?
I have two versions of the switchboard items table. (A2003 switchboard). I have a login form, and I want to be able to change the switchboard items for different users
so I have code that does this
close the switchboard,
copy the new switchboard items table
reopen the switchboard
And every time, it says it cannot copy the table because the switchboard items table is in use. The code in the switchboard opens recordsets, and I have quadruple checked that they are closing correctly, after use. The switchboard itself is bound to the switchboard items table
I have just tried something different which is to manually close the switchboard - and then I CAN copy the tables.
Might the code be atomic in some way. The switchboard does not release the locks until the code completes?
I thought about it, and got round it now, by just copying the data from the new table to the master table, rather than trying to copy tables, but I am still curious.
I have a form that could have duplicate material descriptions , is it possible to have on that form a field that counts the number of line items for that material and when the material changes the new number will appear .
View 7 Replies View RelatedI have two tables to track our engineer visits, one tracks the visit as a whole and the other tracks the individual instruments the engineer worked on during that visit. This way I can track visits to customer sites separately to the visits made to an individual instrument.
Right now, I create a visit and then add Visit Lines (containing the details of the instrument visited). These instruments, or 'Visit Lines', are being displayed in the Visits Table via a related items box. Visit lines are associated with Instruments in the Instrument Table via a serial number lookup. All this works great...
However, I want to display both the serial number and the instrument description in the related items control in the Visits Table. Since the Visit Lines table only has the instrument ID lookup and not the instrument description I can't display it in the control.
I need to either:
a) Create an instrument description in the Visit Lines table as a lookup and have this automatically pull in the description based on the serial number the user selects.... which I can't figure out how to do - it's just an autocomplete.
b) Create an instrument description in Visit Lines and have a macro grab the corresponding description from the Instruments table based on the serial number input - but just for this record...
I have a table of items for our companies quotes. When we go to print out our quotes to send to the customers, the salespeople would like most of the items to be grouped under certain "headers" for the systems they are part of.
Ex:
Autopilot System
Part #1 Qty 2
Part #2 Qty 1
PA/GA System
Part #4 Qty 4
Part #5 Qty 1
My 'Items' table currently has these fields:
ID (PK)
System_ID (FK for 'Systems' table)
Part_No
Qty
etc...
'Systems' table has these:
ID (PK)
Sys_Description
I have a query using a RIGHT JOIN and a GROUP BY to tie everything together, I'm just not certain how to go about displaying the information the way I did above.
Is there a way to export a query of 14 items to a word report. I have a table of 14 items in that I use as a query but it only exports 1 item to the report.
View 2 Replies View RelatedIm trying to create a database so I can Monitor grocery items from different supermarkets in my area.
I have been doing this on Excel for a while now, and its time to do it properly.
The only issue I would have is when I enter data.
I want to be able to track the price changes on items. I have my own.
So for example. Each time i enter the price of an item, I want a time stamp with it. (being the date when i entered the item, should be automatic).
I have a form with a textbox, listbox and a button.
my textbox is based on one of my tables, and when I enter a value (which is saved in my table) in my textbox and press enter, certain values in my table goes to my listbox, and my listbox will just additem whenever I do same thing in my textbox all overagain.
how can I subtract listbox.column(1) items in my table field "Item_Quantity" where my Listbox.column(0) is equal to my table field "Item_Description".
My Listbox column count property = 4
Row source type = value list
I want a good describe about how to create database for supermarket specially how to build a form for selling item, what is a structure of tables ........
View 1 Replies View RelatedI have two options on the main switchboard page and when I click on the second option it goes to a secondary page that gives me 3 options with the third option being to return to the main screen. When I select that option it does go back to the main screen but then on the main screen it shows the return to main screen option as well and when I select it.... it says " There was an error reading the switchboard items table." But I want the main screen to only show the original two options only.
View 1 Replies View Related