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.

View Replies


ADVERTISEMENT

Tables :: Automatic Reference Numbers That Can Serve As Primary Key

Oct 6, 2014

How can i generate automatic reference numbers that can serve as primary key.

I want the prefix to include year. Eg Tr/yyy/incremental number...

View 1 Replies View Related

Auto-numbers Are Not In Order For Tables

Nov 21, 2012

My autonumbers are not in order for tables.As in for example for a customer

customerid customer first name customer last name

17 john parker
18 james johnson
19 alex scott

This is right at the top of the table the first sets of data,i want it to be

customerid
1
2
3
4
5
,etc

Why does it have a random number like "17" when I DO NOT have the first 17 customers !

View 6 Replies View Related

Modules & VBA :: Automate Line Numbers And PO Numbers

Aug 24, 2014

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.

Example

1 1
2 2
3 3

2 1
2 2
2 3

View 5 Replies View Related

Modules & VBA :: Search CSV To Find Specific Statement / Text On Specific Line?

Jul 8, 2015

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).

View 3 Replies View Related

Automatic Rounding Up Of Numbers

Feb 17, 2006

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?

thanks

View 3 Replies View Related

Disable Automatic Change Of Tab Order Index?

Nov 26, 2013

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.

View 10 Replies View Related

Help With Order-line Form PLEASE!

Oct 9, 2006

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.

Can someone please help me?

Thanks so much.

View 2 Replies View Related

Subform Line Numbers

Nov 17, 2004

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 & " ' "))

If Me.Item = 0 Then

If IsNull(vItem) Then

vItem = 1

Else

vItem = vItem + 1

End If

Me.Item = vItem

End If
End Sub

Please help me....

View 3 Replies View Related

Sales Order Line Pricebreaks

Jul 11, 2007

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

Thanks

View 9 Replies View Related

Queries :: Function To Output Line Numbers In A Query - YTD

Apr 18, 2014

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?

View 1 Replies View Related

Reports :: How To Insert Enough Blank Line In Order To Make Each Report The Same Length

Aug 22, 2013

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!!

View 1 Replies View Related

Generating Order Numbers

Dec 12, 2006

Anyone have have any suggestions on how to create personlised job numbers instead of relying on auto numbers?

At the moment I have to freetype them in when creating a record & I would like to work out how to automatically generate them.

maybe something like

1030:101
1030:102
1030:103
1030:104......and so on.

Any suggestions would be much appreciated

thanks. :)

View 13 Replies View Related

Queries :: Order By Letters And Then Numbers

Dec 31, 2013

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

MID(TABLENAME.SORTFIELDNAME, 3, LEN(TABLENAME.SORTFIELDNAME))

View 3 Replies View Related

Using Multi-Line Text Box To Pull Specific Records To A Report

Oct 23, 2014

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?

Example:

Table
ID
DataPoint1
DataPoint 2
DataPoint 3

1
d1
d4
d7

2
d2
d5
d8

3
d3
d6
d9

Text Box (User will enter values and hit OK)
2
3

Report

ID: 2
DataPoint 1: d2
DataPoint 2: d5
DataPoint 3: d8
-------Page Break------------
ID: 3
DataPoint 1: d3
DataPoint 2: d6
DataPoint 3: d9

View 2 Replies View Related

Specific Sort Order

Mar 2, 2005

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.

Colonel
Lt. Colonel
Major
Captain
1st Lt.
2nd Lt.

View 8 Replies View Related

Rounding To Specific Numbers

Feb 9, 2007

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)

Thanks for your help

View 1 Replies View Related

Queries :: Specific Sort Order

Sep 13, 2013

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?

View 3 Replies View Related

Queries :: To Get Numbers With Specific Character

May 12, 2014

I just want to get like if i have criteria like ;

Excellent
Good
Fair
Poor

Then what i need that in table if i select excellent then in query it should display 5, same if select good then 4, then fair 3 and so on..

View 1 Replies View Related

Reports :: How To Set Order For Specific Row As Last In Access Report

Sep 5, 2014

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.

View 3 Replies View Related

Modules & VBA :: Textboxes To Appear In Datasheet View With Specific Order

Aug 18, 2014

I have some textboxes that I want them to appear in datasheet view with a specific order.

How can I control the order given that I tried tab index and tab order but did not achieve the desired result?

View 3 Replies View Related

Queries :: Filtering Data - Results Need To Be In Specific Order

May 21, 2013

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 have an "r" and "s" queries.

These are the fields:

r: [DOCID,NURSEID, WEEK, RESULT]
s: [WEEK, RESULT]

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.

View 4 Replies View Related

Tables :: Automatic Refresh Of Linked Tables - ODBC Connection

Jul 22, 2015

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

View 2 Replies View Related

Automatic Loggin For Liked Tables

Mar 23, 2007

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?

thanks on advance

View 4 Replies View Related

Tables :: Automatic Increment And Reset?

Apr 27, 2015

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.

View 6 Replies View Related

Tables :: Linking Tables With No Common Data And Different Numbers Of Observations?

Apr 15, 2014

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 .

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved