Queries :: How To Add Sequential Dates With Reference Number With Append Query
Apr 7, 2015
I want to populate a table with field:date and field ref nr.(7 fixed ProductID's)
But only weekday and to 3 years from now. (every weekday)
Example
field:ID | date | fixed.ref.nr
--------------------------------
1 | 7/4/2015 | 1001
2 | 8/4/2015 | 1002
3 | 9/4/2015 | 1003
4 | 10/4/2015 | 1004
5 | 13/4/2015 | 1005
6 | 14/4/2015 | 1006
7 | 15/4/2015 | 1007
8 | 16/4/2015 | 1001
9 | 17/4/2015 | 1002
10 | 18/4/2015 | 1003
.... (ID field is autonumber field and not important..)
How to do this with a append query or code?
View Replies
ADVERTISEMENT
Oct 5, 2013
Basically what I have is a database for tracking/logging parcels that arrive to the office. I want to be able to generate a reference number based on the date of arrival: i.e. the reference number should be ddmmyy### where ### is a sequential number. I know that I could just use the primary key's autonumber, for the sequential number but if I do this then the sequence will not restart at 1 on each date and because we receive a lot of parcels the reference number will grow to be too big to print out on the collection slips in just a few months.
two tables (one with the date and staff on duty that day and the other with the parcel's info') with a one to many relationship
I also have a query (Named: FullLog) that picks up the following data from the tables:
Name - Description - Size - TrackingInfo' - Staff - DateReceived - Count
The field named Count is a DCount function that I used to find out the number of times each date is repeated. This is the Expression that I used:
Count: DCount("*","FullLog","DateReceived = " & [DateReceived]) [Note that DateReceived is first converted into a string using CStr()]
This is as far as I have been able to get, I have been looking for weeks for a solution to this problem but I have yet to find one. I don't even know if the DCount function is the correct way of doing it, I did read somewhere that this produces a very slow query.
Effectively what I want to be able to get is something of that resembles the following
DateReceived - ReferenceNo
051013 051013001
051013 051013002
051013 051013003
061013 061013001
061013 061013002
071013 071013001
071013 071013002
071013 071013003
071013 071013004
071013 071013005
081013 081013001
081013 081013002
091013 091013001
101013 101013001
View 6 Replies
View Related
Feb 15, 2013
I have a rental database and I print several contacts for leases etc. what I want to do is have a unique reference number or something inserted to the report every time that it's printed. What I am trying to achieve is to keep track of which tenant corresponds to the report (Lease) printed by using reference number.
There is a seperate form which holds the tenants details and I would like to have a field on that form which would show the same reference number as the report so I can track which report was printed for who.
Whats the best/easiest way to accomplish this?
View 1 Replies
View Related
Dec 16, 2013
We have a large database table [PRODUCT] with the following fields PROPNUM and P_Date and GAS. We need to normalize the GAS field to the first month of production based on P_Date and sequential months following to average the data. We believe the easiest way is to create a sequential number for each Key PROPNUM that represents the first month of GAS the second month of GAS etc.
We have followed several of the posts on how to sequentially number records, but are struggling to get them to work.The latest effort was to use the make a table query creating an expression with the following.
DCount("*","PRODUCT","[PROPNUM]=" & [PROPNUM] & " AND[P_DATE]<=#" & Format([P_DATE],"mm/dd/yyyy") & "#")
View 2 Replies
View Related
Oct 19, 2013
I have a sample database created by self. It has a table name"List_Member"
I am using delete query to delete all data in that table and append query for append new data from master table name"Allmember
When i append data to "List_member", it should start with S.No.1,every time
View 8 Replies
View Related
Jan 5, 2006
I am trying to create a sequential number field in a query. I cant use an auto number for the fact that i am combining multiple tables. Any help with this would be greatly appreciated.
View 14 Replies
View Related
Mar 18, 2013
I currently have a query of between dates which the user enters, but when I try to get a total count of model numbers it gives totals for each date. I am trying to get a count of model numbers between these dates with the dates excluded in the grouping.
View 14 Replies
View Related
Feb 18, 2015
Basically I have a report that shows any 'Issues' that wasnt closed within the KPI Target.
I have the report working, but I simply want to do a count of how many days the observations overran the 'Target Date of Closure'.
The report also shows observations that are not closed but passed their Target Date of Closure. These observations will not have a 'Actual Closure Date', but I would like to still have a count of how many days its overrun closure.
This is a formula I tried to piece together but obviously not correct as it's not returning anything;
IIf(IsNull([Issues]![Actual Closure Date]),DateDiff("d",[Issues]![Target Date of Closure],
Date()),DateDiff("d",[Issues]![Target Date of Closure],[Issues]![Actual Closure Date]))
View 2 Replies
View Related
Aug 28, 2013
I have a query which gets information from 2x tables where the I'd on one table is the reference number on the second table.I would like to know how I can remove the duplicates on my reference number field?
View 3 Replies
View Related
Apr 4, 2012
I have a scenario where the first three rows of date which have dates of 4/1, 4/4/ 4/6 with ndc 5513026701; next six rows that have dates from 4/8 to 4/20 with ndc 5513014801; next three rows that have dates from 4/25, 4/27, 4/29 with ndc 5513026701.
The issue I am having is I do not know how to have separate min/max dates for ndc 5513026701 since when I group by ndc 5513026701 min = 4/1 ; max = 4/29. I need to have min = 4/1 and max = 4/6 for one row and another row of min = 4/25 and max = 4/29.
Any easy way to sequentially create min/max for each ndc 5513026701? I wasn't sure how to verbalize this so I have attached a sample worksheet.....
View 2 Replies
View Related
Jun 2, 2005
Hi, Newbie Question,
what can I include in my code to prevent a user from entering a date into a textbox (via code via combo boxes)
that is an earlier date than the last or latest date field record entry. The two questions I s'pose I'm asking are how do I reference that
last or latest Record (which maybe the user just inputted via the form) & how do I approach the code. The user will be inputting data
for his club members for a monthly sporting event & I'm trying to prevent him accidentally entering an earlier date than the last meet.
View 5 Replies
View Related
Jul 20, 2005
I am new to this and i may be going about this the wrong way but what i am doing seems to do the job until now.
I am making a form that has a combo box, a few text boxes and a subform subreport on it.
The idea is that the first thing a user does is to select a name form the combo box. this name is then stored as a sring and used to set the forms record source and then the subform's (called window) source object. the string is slightly modified during the process to do this. That part works fine.
The problem arose when i included text boxes to show infomation from the selected Query thatisant show in the window (subform).
After the user selects a name and the name is used to set up the record source and the source object for the text boxes.it then displays in the text boxes the records extra data. however it only displays the first row extra data.And when other rows in the query table are selected the data in the text box stays set as the first row.
I know that this is because i haven't programed in an event that makes the text box data update when the user selects a new row.
i also realise that i would need to have some way of know what row of the quiery table the user is looking at to be able to update the text boxes.
my questions are
Is there a way to tell what row of a query table in the subform is selected ?
Is there an event similar to on selection of row or something like that that i could use to reload the text boxes?
and finaly
am i going about this the right way?
Any input on this would be greatly appreciated :)
View 5 Replies
View Related
Feb 6, 2014
I've taken on the task of transitioning excel reports (and their format) to a database. One report summarizes all the parts of a piece of furniture, and the order of the machines each part goes to.It looks similar to this:
Part# Part Name Machines/Operations
M7264Top Panel 112114116120121325216
M7265Under Top Panel 112114116120121
M7266Base Assembly 411325216311310312316
The table this comes from has both operations and setups, so I used a query to filter only the operations.The query (qryOperations) result looks like this:
PART NUMBEROPERATION ORDER
M7264 112 2
M7264 114 4
M7264 116 6
M7264 120 7
M7264 121 8
M7264 325 9
M7264 216 10
M7265 112 2
M7265 114 4
M7265 116 6
M7265 120 8
M7265 121 9
i need to add sequential numbering (1,2,3,4...) to each line of the qryOperations and use the sequential numbering as the column header.How do I add sequential numbers to the query, that restart after each change in part number? I can do this in a report easy enough, but not a query.This is the SQL of the query I need to have the sequential numbers in...
SELECT tblparts.[PART NUMBER], tblOperationCodes.MachineCode, tblOperationsList.Order
FROM tblparts INNER JOIN (tblOperationsList INNER JOIN tblOperationCodes ON tblOperationsList.OperationCode = tblOperationCodes.OperationCode) ON tblparts.[PART NUMBER] = tblOperationsList.[PART#]
WHERE (((tblOperationCodes.Function)="O"))
ORDER BY tblparts.[PART NUMBER], tblOperationsList.Order;
View 5 Replies
View Related
Jun 5, 2013
I have a query that groups the data by customer. How can I create a "Transaction Number" field where I assign a sequential number to each invoice, starting at 1 with the oldest invoice and increasing the sequential number by 1 with each invoice. I need to create this field in a query without code.
View 3 Replies
View Related
Aug 28, 2012
Are there no arrays in Access? I am looking for a way to put sequential dates onto a calendar. An array would work fine, with an offset for Weekday(FirstDayOfMonth).
View 1 Replies
View Related
May 28, 2014
I just can't seem to get this one to work right. I've got the following query. I need to count the number of Null dates or show zero if there are no Null Dates.
Code:
SELECT DISTINCTROW qryNoticeResponseNew.fldNoticeID, Count(qryNoticeResponseNew.[fldResponseSeen]) AS fldCount
FROM qryNoticeResponseNew
GROUP BY qryNoticeResponseNew.fldNoticeID;
Which is just counting the number of dates so far. It got me to thinking I need to do something like this.
Code:
SELECT DISTINCTROW qryNoticeResponseNew.fldNoticeID, IIf(IsNull(qryNoticeResponseNew.[fldResponseSeen]),1,0) AS fldCount
FROM qryNoticeResponseNew
GROUP BY qryNoticeResponseNew.fldNoticeID;
Which pops a "cannot have aggregate function in expression" error.
View 2 Replies
View Related
Jan 22, 2007
Hello, I am very new to access and I am trying to create a simple sytem to help us create part numbers. We are using a 2 digit descriptor to help define the part some. Then we want to add a sequential 7 digit number on to that.
Example:
01 (is electrical)
02 (Shop supplies)
etc....
So...
01-0000001 would be a part number for an electrical part.
I do not understand how to have access add the zeros to the front. I would also like to be able to jump a bit out of line to reserve part numbers for certain projects and also include some that we already have.
If this make any sense to anyone, any help would be appreciated!
Thanks,
james
View 3 Replies
View Related
Jun 25, 2013
My database includes two separate dates related to a vehicle purchase and default payments resulting in repossession. I need to find out how many weeks passed between those dates.
Example:
03/14/12 Date of automobile purchase
06/06/13 Date of last payment before default
How many weeks in between? And then I need to average all of those figures so my company can see the usual week-term time frame that our customers are defaulting and getting repossessed.
View 5 Replies
View Related
Sep 11, 2006
Hi guys, im new to all this so take it easy
Im trying to create a document register in Access 97 to create and track document numbers, for this i want to assign a 6 number unique id to each record starting at 000001 going up in order to 999999 which will form part of a unique docuement number ...i know this may be a really easy thing to do but can anyone tell me how?
Cheers
Dave
View 1 Replies
View Related
Mar 11, 2008
I have a form on which I need to print a sequential number - increasing by 1 each time the form is printed. I cannot think of a way to do that - perhaps something returned from a query? Any suggestions?
Robert
View 3 Replies
View Related
Nov 10, 2006
I've tried using DMax codes shared by the generous members of this group. But i can't get the results i needed. :-(
My table consists of the following fields:
ColID - PK
PolID - FK
AmountPaid
ORDate
ORNo
I added another field which i named "ColNo" - this field should assign a sequential number to a new payment record for a specific PolID.
My table looks like this:
ColID PolID AmountPaid ORDate
11125 123 2,000 10-Sep-06
11126 123 2,000 10-Oct-06
11127 123 2,000 10-Nov-06
11128 456 1,000 8-Aug-06
11129 456 1,000 8-Sep-06
11130 789 4,000 21-Aug-06
Again, my idea is to create a sequential number for the ColNo field:
ColID PolID AmountPaid ORDate ColNo
11125 123 2,000 10-Sep-06 1
11126 123 2,000 10-Oct-06 2
11127 123 2,000 10-Nov-06 3
11128 456 1,000 8-Aug-06 1
11129 456 1,000 8-Sep-06 2
11130 789 4,000 21-Aug-06 1
I placed the code Nz{DMax("field name","table name"),0)+1 on the control source of the ColNo field and i also tried copying numerous similar codes to the BeforeInsert, OnCurrent, etc.. events, but the result is ALWAYS THE NEXT MAXIMUM NUMBERFOR THE "ENTIRE" POLID.
What i mean is, if i enter a new record, it would give me a sequential number of "4". I figured out this is because 3 is the last maximum number as i have shown on my sample table. Even if i use the PolID "789", the number shown on the ColNo field results to "4".
IT SHOULD HAVE BEEN "2". Considering that the last ColNo used for PolID 456 was "1".
Hope someone could help me on this..
Thanks!
Sheila
View 4 Replies
View Related
Apr 16, 2008
Hi, im pretty new to access, i need some help or a pointer on how to set up a sequential number. i have a orders table with an order number starting GO how to i get access to count up in ones from the starting number.
regards
View 14 Replies
View Related
Nov 21, 2014
I'm trying to calculate the number of days between two dates using the iif statement
Fields:
[LDW] "Last Day Worked"
[ReturnedDate]
DESCRIPTION: If ReturnedDate is null, then calculate the datediff "d" between LDW with Now(), if not, then calculate the datediff "d" between LDW with ReturnDate.
I tried this but it didn't work at all.
Days Absent: Iif(isnull([ReturnedDate],(DateDiff("d",[LDW],Now())),(DateDiff("d",[LDW],[ReturnedDate])))
View 2 Replies
View Related
Mar 19, 2013
I have a table called StockTable with the following fields Location, Status, Serial, Make, Model, LastDate, DotNumber
I also have a table called FCDateRange with three fields
DateStart DateEnd and DotNumber
For example
3/7/13 - 3/13/13 - 1
3/14/13 - 3/20/13 - 2
Im trying to figure how to write a query that if the lastdate from the StockTable falls during the DateStart and DateEnd fields it will assign it the number in the DotNumber field
View 2 Replies
View Related
Jan 25, 2007
Dear All,
I know that there may have been many posts on this and I have searched for a total of about 2 hours in these forums before posting, so please don't shoot me down for asking this.
I simply need to know how I can create a unique, alphanumeric order number. I was going to use Autonumber, but from what I have read on these forums, that is not the way to go, as Autonumber is simply to identify the record.
What I need is an number like so: ST000. It needs to start at ST142 and increase by one each time. Obviously I would like to avoid gaps from records being deleted or not completed etc.....
I really have no clue how to do this, and would appreciate it if anyone can point me to a post that would help, or offer me the solution.
Thanks,
View 13 Replies
View Related
Jul 19, 2015
I have a table like the one below. I need to Auto Sequence based on JobIDOne in the field that says sequence in the manner that I've typed. Hand typing is not an option because by table will be updated regularly. All other fields are updated via a form. I need a either a macro or VBA solution that can reconcile this, preferably through the table. Note, I do not want to use a query to create this sequencing or SQL language as I do not know how to write SQL commands.
I know it can be done but I've seen about a thousand ways to do it that I haven't been able to modify for my specific table.
DateOne
TimeIn
JobIDOne
InitialsOne
Dates Worked One
Sequence
7/17/2015
6:45:10 PM
12345
AR
7/17/2015
1
[code]....
View 14 Replies
View Related
