Numerous Invoices On Orders - Averaging By Chronology
Nov 15, 2006
We have a billing scheme in which we partial-bill ceratain customers based on certain events: 30% when they sign the contract, 30% a number of days later, 30% when we ship the goods and the last 10% upon installation. This is a common process, known in many indistries as 'progress billing.'
What I am trying to do is compare the collection performance on the invoices based upon which trigger sent them. For example, I'd like to find the average and standard deviation (plus some percentiles) of the time it takes to collect invoices that are sent... when we ship, let's say.
I have a table that shows order number, invoice number, invoice date, amount and payment date. So, I can have the order number show up to 4 times with a chronology of invoice dates.
My question is this: How do I group for analysis all the FIRST invoices, the SECOND ones, etc...?
Thanks for any help! I can do this in excel... but I was given this extract of 100000+ lines, and hope to do it all in Access. Thanks again.
View Replies
ADVERTISEMENT
Oct 31, 2006
Hi everyone,
I'm sort of new to Access, but over the past few months I've been able to develop an inventory database for my work which turned out quite nicely and does everything I need it to, except for one thing.
I want to also create invoices for my customers, and after looking at examples I understand that most people link the "order" with the invoice, so that there is one invoice per order. However, I need the ability to combine many orders into one invoice. That's where I'm stuck.
I don't know how to arrange my relationships and forms so that I can "pick" the orders I want on my invoice, and not at the time of the Order, but at some point after. Basically my orders will be established, but their allocation to invoices won't, until I come along and put them into their rightful bill.
Does anyone have any thoughts on this? I'm pretty stumped.
Thanks a lot for your help.
View 2 Replies
View Related
Mar 5, 2014
I have a database where I have 3 tables. first is client data, second orders data and third the products data.
What I would like, is to have a button on my form that after selecting from a combobox a product, it wil enter it in the tabel orders in the next available free record (row). I created a button and the combobox in the client form. The Products table has also the price per unit in it. It should take the whole row of data and put it in the Orders table.
Is there a way to do this? I guess it needs to be done with vba, but not sure.
View 9 Replies
View Related
Mar 16, 2012
I am creating a database from an Excel Workbook. Many of the calculated cells are dependent on the values of other calculated cells.There are more than 255 cells for the one sheet so I can't use one table.I was forced to use multiple tables and place the calculation fields in their own tables for the use of their fields in a calculating query, although they won't be seen on any form or report.
What is the best way to relate these tables even though they should really be all part of one table? Should I just use a Cross Join? Will a cross join allow me to properly calculate with a field from an unrelated table? What about an Outer Join?I need to reinforce integrity so that if one set of table data is deleted, so are all the others.I have already tried to place a "Report Number" autonumber field on each table, but it doesn't seem to allow me to reinforce the integrity and gives me, "invalid field name of definition of index or relationship".
View 4 Replies
View Related
Aug 19, 2006
I have the following situation:
1. A Homeroom table which includes student id, academic year, and homeroom.
2. A Student Grades table which includes student id (linked to Homeroom table), term, academic year, progress grade (15 pts), and progress grade (A-F).
The contents of these tables are imported from excel, and that works fine except for one thing: in order to keep the field [progress grade (15 pts)] numeric, I had to put a 0.0 as a grade for any student who got an "I" (Incomplete) or "N/A" as his or her progress grade.
The problem arises when I want to produce an average progress grade for the student for that given term/year. How do I not include the 0.0's of students who got I's or N/A's?
Any help would be much appreciated.
View 7 Replies
View Related
Nov 5, 2007
Not sure if this belongs in Queries/Form/Reports because it has to do with calculating formulas within all of the above but here goes anyway:
1- I've collected data that is entered by week number via a form.
2- The data utilizes fields that are to be used in calculating formulas, which I am familiar with.
3- I WANT to average the most recent 2-x weeks, (primarily, I will use the most recent 3 but would like the ability to calculate any given number), of the CALCULATED FORMULAS mentioned above to be used in reports/queries.
4- In other words for example, field a/ field b = X for one given week. Now I want to know the average of field a/ field b for the most recent x weeks.
Is there a way to perform this action? If so, can anyone help me on completing it?
Thanks,
Doug
View 5 Replies
View Related
Jun 1, 2005
Put simply - when I do a find it displays the first account that matches it, then, when you leave the find screen and hit the "next record" key it simply displays the next account (i.e it no longer matches the search criteria).
So my question is,
How do I search records, then skip through ALL accounts that match the criteria.
example:
100 accounts in a database. 50 accounts are assigned "Mr A" and 50 "Mr B".
Mr A and Mr B sit down to work - hit find, and type in their name.
The form shows 1 account, and when mr A hits next fifty times it should not move on to mr Bs (possibly a "no more matches/records" message?).
I hope this is clear enough - and thanks in advance.
ps - I heard something about one form to search - and a subform to display results - the search criteria is static in form one, where as the subform can be skipped ("next record" pressed). But trust me - Im inexperianced :p and would need a nice newbie friendly description of such technological witchcraft.
View 1 Replies
View Related
Jan 10, 2005
I have a problem that is very tedious. I have 9 separate copies of the same databse (there is a copy of each database for every branch of my company. Each database has small custom features that show what company it belongs to) i was told to make a new feature, and in the making, i had to make 40 new queries, 12 new forms 8 new macros, 12 new macros and 6 new forms. this feature needs to go into all of the databases. As far as i know, im going to need to copy and paste each individual query,form, macro and form. Thats 72 different things i will have to copy and paste into 9 separate databases. Does access provide an easier way to go about this?
any help?
View 4 Replies
View Related
Jun 7, 2007
Ok, here's the scoop. I have the following record setup for tracking shipments:
[Shipment_ID_#], [Date_Loaded],[primary_silo], [secondary_silo], [2_silos]
I need to retrieve average values from another table for certain chemistry values based on the [Date_Loaded] and whether or not one or two silos were used when loading the shipment (as indicated by the [2_silos] checkbox).
Scenario #1 - one silo
I need average values of the last four samples based on the [date_Loaded] and the appropriate silo.
Scenario #2 - two silos
I need average values of the last two samples from each silo based on the [date_loaded], [primary_silo], and [secondary_silo].
Would anyone have any thoughts on this? I've tried crosstab and select queries, but I can't get anything to work.
My ultimate goal is to have a form where users select the shipment and the appropriate chemistry averages automatically populate the data fields.
Any help is greatly appreciated.
Thanks,
Greg
View 1 Replies
View Related
Aug 2, 2007
a request came accross my desk to find holidays sales in relation to the same days of the week for other weeks. For example, Thanksgivings sales compared to each of the 2 thursdays prior and the 2 thursdays after. Anyone know of a way of pulling this data so I have it all in one query - Thanksgiving, Thanksgiving+1, +2 and -1 and -2 (the numbers representing weeks from holiday
Thanks.
Brian.
View 1 Replies
View Related
Sep 18, 2013
I have a form where i select via a combo box if a product has been inspected, if it has, additional controls are then made visible.
If Me.Inspection_Completed = "Yes" Then
Me.Date_Inspection_Comp.Visible = True
Me.Date_Inspection_Comp = Me.Dateinsp
Me.Inspector.Visible = True
Me.Qty_Inspected.Visible = True
Me.OK.Visible = True
[Code] ....
I tried to put this in a function so i could call it on load, or on current etc, but cant seem to get it to work, apparently you cannot use the me. in a function.
View 4 Replies
View Related
Feb 2, 2015
I am using .FormFields to fill a quotation template in Word, but the subform I am retrieving the data from has rows of records under each column and I need to send more than just the first row to fill the required bookmarks in Word.
The code I'm using is
Set doc = appWord.Documents.Open("S:TemplatesQuotation Template - Test.doc", , True)
With doc
.FormFields("FirstName").Result = Me!FirstName
.FormFields("FirstName2").Result = Me!FirstName
.FormFields("LastName").Result = Me!LastName
.FormFields("LastName2").Result = Me!LastName
[Code] ......
View 4 Replies
View Related
Apr 15, 2008
I have a table in which each record includes a field showing the time that an event started and then another field which stores "how long did it take", these two fields are both defined as a short Time. I then have a query that runs through the "how long did it take" fields to produce an average time for the whole sequence of events. Two problems:
1. The average comes out as a decimal like 39553.367942 instead of several hours and minutes.
2. When I try to access this value using DLookup on a form I get "#error"
Any suggestions?
thanks
View 2 Replies
View Related
Sep 16, 2014
I'm using Access 2010 and I'm a novice with databases.I have a table that contains water flow readings taken 4 times a day from multiple wells. I am trying to create a select query that will generate the daily average of the 4 flow readings.
HoleID.....Date....Flow1....Flow2....Flow3....Flow 4....Average
1111.....9-8-14.......0.........null.........7..........4......
1111.....9-9-14.......0.........null.........9..........3......
2222.....9-8-14.......0.........null.........10........7......
Also, my table also has several 0 values and NULLS meaning the well was turned off - I want to exclude the zeros and nulls when I average. How to average these numbers? Do I need to restructure my table?
View 2 Replies
View Related
Feb 5, 2015
Access 2010 database.
I am trying to get an average from several numbered fields, which some may occasionally be blank.
=Nz([JanUS],0)+Nz([FebUs],0)+Nz([MarUS],0)
I get an error message that says "The expression =Nz([JanUS],0)+Nz([FebUs],0)+Nz([MarUS],0) cannot be used in a calculated column. This is a hypothetical expression being used.
The real expression is ...
([GC A1 - 2]+[GC B1 - 2]+[GC C1 - 2]+[GC D1 - 2]+[GC E1 - 2]+[GC A2 - 2]+[GC B2 - 2]+[GC C2 - 2]+[GC D2 - 2]+[GC E2 - 2])/10
This expression works great until a field is left blank, and then the average box is left blank.
The fields I am trying to add are Long Integer. Would that affect it?
View 1 Replies
View Related
Mar 4, 2008
Hello,
Thank you for your time.
I want to set up an automated recurring monthly invoice for clients in my database.
I know this is possible but I am having trouble finding the solution and in fact having problems how to search for a solution.
On the 1st of every month I would like to have a query run that inserts a new row into my invoice table for all current clients invoicing them for that month.
Please please help. Invoicing one at a time using a form is taking far too long each month.
Thank you.
View 3 Replies
View Related
Sep 21, 2005
I have a table called vendors in that table is Company Name,
address, city, state and zip. I also created a form called vendors same information. On the form i have a combo box called bill to party which selects the name that will print on the invoice.
Ex Company Name ABC
Address What Ever
City State Zip
Phone
Billed to party (here is the combo box on the form)
However when i select the bill to party on the form from the combo box it is only showing the name of the billed party on the invoice and not the address city and state.
I dont want to create extra text boxes on the form so when i chose the bill to name on the form will fill in the text boxes I dont want to do this. All i want is to select the bill to party from the drop down menu and when i view the invoice it will automatically show up on the invoice.
View 3 Replies
View Related
Jul 11, 2006
I have a report that produces invoices and gets its data from a multi-table based query. The query has multiple lines for each customer and the report groups and totals them. How can I have the invoices numbered sequentially? Any help please?
View 2 Replies
View Related
Aug 2, 2005
What Im trying to do is make invoices that I have in to data base...
reason for it is to keep a close watch on the price fluctuation since the stuff we get are from different country. We don't really need it but this is my own little project just for myself...I have all the tables made and form ready but I have couple of problems.
1. How do I calculate and save it in the table? I Know this violates 3rd normalization but I need to do this since the price will change almost every week. basic calculation which is total price / quantity.
2. How do I get the previous record on the form automatically? lets say that the date I put in is July,4,2005. How do I get this to show up in form for the new data input? and keep that date until it is updated using form.
3. Since I'll be getting same stuff lots of times, and since they all have item number, How do I get it to auto fill? lets say that I input a item number and rest of the form automatically fills in the rest of the values...and updates are saved to the table and next time I input the same item number updated data will automatically fill in the rest.
Thanks in advance. This is my first post so, please go easy on me...:)
I also search the forum but couldn't find anything that I understood...
Is this a project too advanced for newbies? As I have stated earlier, I have everything layout but I'm just trying to make it easier inputing new data.
and all the codes that are here, I'm lead to believe they are Visual Basic.
and good book for newbies on VB?
Thanks. and sorry for the long post...
View 1 Replies
View Related
Mar 25, 2007
I'm creating a database where I have a customers and invoices table, obviously there is a relationship between the two tables using a customerID field where I have referential integrity enabled. However not all invoices require a customer account as some are cash sales where no customer details are required.
I'm unsure of the correct procedure, all I can think of is to create a separate table for cash sales??
View 3 Replies
View Related
Aug 5, 2005
I have a table with every invoice I have raised to my customers on it (160,000 records so far). Every record identifies the customer and the date of the invoice. I would like a query that returns details of the last 4 invoices raised to each customer based on the date of the invoice. So, if I have 100 customers I would expect 400 records returned. I have spent ages searching and reading the forums re this and have seen several post that I "think" are similar, but I think I'm too daft to understand the solutions put forward. I "think" this involves a module to count but as I have never written a module in my life am a bit confused. Any pointers would be greatly appreciated.
View 7 Replies
View Related
Sep 15, 2015
I have a pretty simple database that we use to track customers, orders, invoices, and payments. I have an order form that shows all of the order details, and has a subform with services, qty, price, etc. Sometimes we need different products that are part of the same order to appear on two different invoices (say an order is for part 100 and part 101. If part 101 ships first it needs to be invoiced, and part 100 will be on a separate invoice when it ships later), so one order can have multiple invoices.
I'd like to create a solution where when a user clicks the invoice button on the order form, they are able to select which lines from the subform should be invoiced on that particular invoice.
View 9 Replies
View Related
Feb 14, 2005
Is there any way we can convert the amount in numeric form to text format Eg: $ 1000 as Thousand Dollars.
I need this for generating invoices.
Thanks in advance for the help...
Rajesh
View 2 Replies
View Related
Mar 21, 2013
I have a table which has sentdate invoices and paiddat invoices. i want to be able to get a monthly count for the amount of invoices sent and paid to compare on a graph. e.g jan 2011 = 5 feb 2011 = 6 ect
View 1 Replies
View Related
Jul 7, 2014
I am importing data from a supplier. The table has many invoices included and varying items on each invoice. I need to find a way to separate each invoice with all the included items. For example:
inv# pieces linedesc amount
11 500 nuts $20
11 100 bolts $15
11 75 washers $10
12 150 cameras $75
13 10 desks $150
13 10 lamps $50
View 4 Replies
View Related
Jul 7, 2014
I am looking to make an invoicing database.
I create 30 invoices a month. each client gets billed the same amount each month (for example Customer A gets billed $100 every month, Customer B gets charged $200 each month). Only two things get changed on the invoice-'Description' (for example the description would be 'services rendered for July 2014' for July invoice) and 'Invoice Date'.
Now please see the image attached, I have an append query that combines information and creates invoices for all 30 clients.
The problem is, the 'Invoice No' field in the invoice table stays empty, because I do not know how to start numbering at a specific point (for example invoice numbering should start at 14150001) and I want it to add the number (+1) automatically when this append query adds data to the invoice table.
View 13 Replies
View Related
