Tables :: Come Up With Automatic Line Numbers For Each Specific Order?
Oct 2, 2014
I am trying to come up with automatic line numbers for each specific order. So for example, I have job number 123456 that has ordered 3 items, what I would like is that item 1 has a field with a 1 in it automatically, and item 2 has a 2 in the field and so on. But the trick is that when order 123457 gets entered and has 5 items entered, I would like it to start over at 1 and go to 5. Does this make sense? Is it possible to do this in a table? Or can this happen on the form? That I assign a value to a field. I am using Access 2010, have been for a few years now, but I have lots to learn.
I have 2 fields that I would like to automate if possible
One field is called "p/o number" and another field called "line no"
These fields are part of an ordering database
Let say I have 200 items to purchase form 10 suppliers
And form example 20 items from each supplier
What I do at present is put the order number on each line item and the line number
example
p/o number line no
1 1 1 2 1 3
2 1 2 2 2 3 2 4
What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.
how to read a specific line in a CSV file (using VBA), to see if the phrase "There are no records available." is present.
If it is present, then I'm going to do a debug.print stating that there are no records to load - and then the script will move on to the next file. If the phrase isn't present, then I'm going to upload the file to Access, parse the information, and then upload it to a CRM. (I already have the latter portion of the code up and running....I just need to account for the first part, where I can determine if the file has data or not).
The structure of the file never changes. The first row is composed of eight column headers (Post Date, Card Number, Card Type, Auth Date, Batch Date, Reference Number, Reason, Amount) and (if) the phrase "There are no records available." is present, it will show up on the second row, in the first column (under Post Date).
when enetering data into a field on a form it automatically rounds it up the number up to the nearest whole number. this is really frustrating, does anyone know how i can solve this problem?
When I create a new control, or change the location of an existing one, the tab order index is changed for almost all the controls across the form.
I have many controls in this form. The controls are a combination of textfields and comboboxes. I have carefully selected the tab index for each control manually, and the user can now tabulate through the fields in the correct order. If I later change the position of just one control, or add a new one, all the manually set tab indexes for the controls automatically change so the order is horizontal across the form.
How do I stop the automatic change of tab order index? It is frustrating to manually set this for all fields, when just a small change is needed.
I am having so much trouble with a order-line form.
I have a table that has the following information. The Table is Called ORDER LINE: Its attributes are, Order No, Product No, Quantity Ordered, line-item cost.
I need the line-item cost to automatically update when someone changes the quantity ordered. How would I go about doing this?
Now I am pretty new to SQL statements and I am assuming that this is how I would go about doing that.
Hi! This is my first time writing in. I have been struggling with Access for the past 2 months... I am stuck with a problem. I have a variation of the Order form in the Northwind Example, with a subform called OrderDetailsExtended. The primary keys for the OrderDetails Extended subform and its corresponding table are the orderNumber (which is the link with the mail Orders form) and Item. Basically, my question is.. each time i want to add a new record, I want the Item number to automatically increment (but i am not using autonumber for this). The item field is currently disabled.
However, each time i enter a new record, the item number automatically becomes 0. And if I close the form and reopen it, the number changes to 1... Here is the VBA code i used...
Private Sub ProductCode_Enter()
Dim vItem As Variant vItem = Nz(DMax("Item", "orderDetails", "orderNumber = ' " & Me.orderNumber & " ' "))
I'm not sure if I have a table structure, query or Form/subform problem, so I'll post here and see what happens
Objective - From a Sales Order form (and linked Sales order Lines subform) once the Item to be sold has been selected and quantity entered, I wish to check against a Pricebook table, where each item has 3 price/quantity breaks stored and have the correct price drop into the sales order line
Any suggestions on what is the best technique to achieve this?
ps not too bad using standard query builders etc, but pretty hopeless at code (which is one of the reasons for joining, to improve in this area)
So far found this a great place to be, lots of good advice from lots of helpful people
Is there a built in function which can be used to create line numbers in a query?
I've written a query to calculate year to date (YTD) points for yachts in a series of races and sorted it in descending order - so yacht 1 is coming first, yacht 2 is coming second etc. I'm looking for a way to add sequential numbering (starting at one and increasing by one for each line) into the query to represent their YTD places. Or this this something that should be left to the report which uses the query?
I have a main report / subreport relation, just want to have a fixed length of each printout. e.g the total number of lines of the subreport should be 8 lines. However, lots of subreports contain records less than 8. I would like to know how to insert of blank line in the subreport depending on the records with content at each print, in order to make the total number of lines in each subreport is 8 each print!!
I have a column containing an id that consists of the first two letters of a weekday followed by an incrementing number. For example, for Monday, I have "MoA1" "MoA2" "MoA3" ... "MoA11".
The problem is that when I sort my list, it is ordering it: "MoA1" "MoA10" "MoA11" "MoA2" "MoA3" etc. Currently, my order by property is set to
I'm trying to have users enter multiple IDs from a table into a text box separated by newline chars, and then (once they press a button) have some sort of macro or code read each line and pull a report I've created based on the ID. Is that possible?
Okay, here's my new dilema. I'm in the military and rank is one of the fields in my DB. The only issue I'm having is that I would like to sort by seniority, not alphabetically. For Example, I've listed a few ranks below in the correct order from greatest to least. Is there some way that doesn't involve a page of code to achieve this? Thanks.
I am looking to round numbers to specific values 2, 4 and 6.
I was thinking of a code in a query something like:
if <3 but >0 then 2 if <5 but >3 then 4 if >5 then 6
can anybody convert this into queery code, not SQL tho because I'm rubbish at that. I just want to type it into the criteria on my field box on the query.
I also just tried to do it with:
IIf(>0 And <3,2,IIf(>3 And <5,4,IIf(>5,6,IIf(0,0))))
but this only seems to return results of 4 and doesn't show all entries, like entries with no value entered. I also think i'm missing out the numbers 3 and 5 because it's less than and greater than but not the number itself: if 5 or < or > (to include the 5)
thanks
Ok I've now got IIf(<1,0,IIf(>1 And <3,2,IIf(>3 And <5,4,IIf(>5,6,IIf(0,0)))))
which I think is doing roughly what I need but i have stumbled on a new problem.
These results are based on the average result of another query but i need to add a section before the results are averaged that says
if(=0 or no entry then all results are 0 for that unit else avg but iif(<1 etc. (the original statement above)
I have a query that is returning running conditions for a machine over multiple shifts. the table is set up like this
Code: date shift parameter condition 09/13/13 1 front 500 09/13/13 1 middle 450 09/13/13 1 back 475 09/13/13 2 front 510 09/13/13 2 middle 460 09/13/13 2 back 490
this is just a small example. there are about 24 different parameters reported on in a shift..I want the query to sort by date ascending, shift ascending, and then by parameter in a specific custom that i define. I want it ordered "front, middle, back" for each shift. Again this is just an example, there would be 24 parameters I want in a specific order. Then do I need to define the sort order again for the report based on this query?
I have report which record source is a Union Query, The query contain Order Details and a single row for "Freight Charges", I just want to set Freight Charges show in Report at last row.
I'm taking my first database class and I'm working on a hospital project in which I need to determine which pair (one doctor and one nurse) has a decremental performance as weeks go on.
I would like to find what pair had a "Good" result in week 1, "Average" in week 2, and "Bad" in week 3. My problem is that the results need to be in this specific order.
The professor told us to use a formula and I got the solution after building 6 queries that involved union, difference, and cartesian.
I have linked tables from SQL Server using ODBC connection that their location never changes. I have used certain fields of those tables to create queries and make table queries to derive to the information I needed.
On these tables on SQL Server, there is new data added daily. Every day, midnight, there's new data records added of whatever transactions took place in that working day. how often do I need to refresh linked tables in this case to get the latest data added. I mean, once I am linked, the make table query using those defined fields, would it get the latest data added by default when the query is executed, or I must refresh linked tables using Linked Table Manager and then run make table query.
Also, if I want the access to automatically refresh linked tables, can I use the following code? I have added this code, and executing it through a button, but I don't see anything happen, the database becomes inactive for couple seconds (I guess while it is updating) but I don't know is it updating the tables for sure or not, though I am not receiving any error when executing the code through the button.
Function RefreshLinkedTables() Dim tdf As TableDef For Each tdf In CurrentDb.TableDefs If Len(tdf.Connect) > 0 Then tdf.RefreshLink End If Next td End Function
I have programed an access aplication that downloads some data from ORACLE, to do so it used some linked tables, my problem is that the first time that the user runs the 'insert into local_table (colum_1) select column_1 fom linked_table' access displays the 'Microsoft ODBC for ORACLE Connect' form, I'd like to control this conection programatically and not to show this window. ¿can this be done?
Currently I'm working on an invoicing database in which I can register my customers (or partners) and also make invoices and purchase orders. So far, it does exactly what I want it to do, but I'm struggling with the invoice numbers. What I want to have is that whenever I make a new record, the next invoice number in sequence is automatically assigned to it. Another desire is that this number is prefixed with the current year, and the counter should reset with every new year. So, for example:
2015001 2015002 2015003 etc.
Then
2016001 2016002 etc.
Don't worry about the maximum of 999 invoices, because I won't ever go beyond that limit.
I am doing a project where we are collecting home owner data and information on all the dogs in the household. The data for homeowner has an autonumber primary key because no field is unique or has been consistently collected. I am struggling to link the data for the dogs to the owners because an autonumber primary key will not work since not all homes have dogs. I need to have this set up so that people who are not tech savvy can look up each homeowner (or dog) and get the dog (or home owner) information. And to make things even more fun we need up to 15 potential dogs per household each of whom will have 18 different pieces of data collected.
It looks a little like this (and you can see my not matching ID issue):
Homeowner table ID First Name Last Name...........Total Dogs 1 Max Maximus 5 2 Min Minimus 0 3 Mus Musculus 1
Dogs 1-5 table ID Date Dog 1 Name Dog 2 Name .......Dog 5 Name 1 (Max's) 4/11/14 Bobby Billy Betty 2 (Mus's) 4/11/14 Jojo
Min will have no dog records at all, just home information.how to link the dog's to the homeowners .
