After Date Change TransactionID Regenerate from 1

[yameen2011]

Regards,
I am working on a database,
structure of database tblaccounts: glcode,description.
tbltransaction: transaction ID, glcod, tranxtype, amount


Everything is going well, when i make a transc ,transcid genrated

i,e 1,2,3,to 47,48,49,50 and so on. In this case there are minimum 50 transaction on daily bases, it is difficult to remember the transaction id after two or three months. Here i have an idea that daily after 11.59pm ,i mean after changing date ,when i make a transction, transction id or sequence no start from 1, and my last day data will also remain safe . I am sure that i have to create another table for appending data to that table for reusing of transaction id from 1 but i am failed while making this, any Idea .help will be appreciated .

 

[Mihail]

it is difficult to remember the transaction id after two or three months. Here i have an idea that daily...

... and you hope that someone will remember this new numbers. Ha ha.

It is not necessary to store the numbers.
You can create a query/report that show this consecutive numbers.
For this you need to store the transaction date and time. I can't see a field like this in your DB.

 

[CJ_London]

Not sure what your reasoning is - why do you need to remember a transactionID?

you haven't explained how you are doing your appends, but assuming they are one record at a time then you can calculate a daily sequence number as follows in your query designer:

SeqNo:nz((SELECT count(*) FROM tblTransaction as tmp WHERE tranDate=Date()),0)+1

From your table description you don't appear to have a tranDate in your transactions table so you will need to add one in - you can set its default to Date() so it will always be populated with the date it was created.

 

[yameen2011]

... and you hope that someone will remember this new numbers. Ha ha.
.

Thanks for answering.
Dear Mihailmy idea is remembering transaction Ids is not to remember these numbers
i want to make a transaction sequence numbers , like 1,2,3 and so on , i just want this for when i make a transaction i wont see back dated transaction ids in today,s transaction list, if i made a invalid transaction today, i can easily figure out that. for this purpose i want to make transaction sequence number daily start from 1 after at 11.59pm.
hope that now you understand what i am trying to do.

 

[Mihail]

CJ has write his post in the same time with me.
So, we, both, have advice you to store the transaction date. If you will do this, will be very easy to see the transaction from any time interval (from seconds to years - this include any day ).

 

[yameen2011]

Mihail and Cj London , thanks for helping me , i am sorry that i am not explaining well that what i trying to do and what problem facing , i have a transdate field in my tblTransaction , i have queries qrytransaction and issue is that i have made a form frmtransactiondetail on the base of that query, and i want the query result in frmtransactiondetail form every thing is going good butt i want here in frmtransactiondetail an extra field that show me a sequence number before transactionID. on currently frmtransactiondetail form.

 

[Mihail]

CJ's query (adapted to your data, of course) should do the trick.
If you can't implement it then upload your DB (Access 2003 version) and we'll try to help you.

 

[yameen2011]

I have attached sample
Open frmGLEnquiry Form and Click On Search button the frmGLEnquirDetailQ form open , at this form i want a sequence no that every time when this form open start from 1,2,3,4, and continue so on.
waiting...

 

[yameen2011]

Now i have attached a sample,in this i have created a new field in tblTransaction sequenceNo and in a frmTransaction SequenceNo and i enter a before insert event in frmTransaction and when i type in GLCode Field a sequence number genrated
working for me but i here want when date is change counter start form zero , if this is possible then help me out.

 

[yameen2011]

This is what I am trying to accomplish I need to be able to create a serial number which automatically resets every day so I may use it to easily know how many records were created per day, without running a report.

 

[Mihail]

My friend, I'm afraid that I'm not able to tweak your DB because this:
Database Normalization and Table Structures - Microsoft Access / VBA
Learn very well about the DB's NORMALIZATION concept then redesign your DB.

If you like, I'll help you to create a good structure for yours tables (AFTER you will have a good understanding of normalization).

 

[Mihail]

One more advice:
Recreate the DB in Access 2003 version (.mdb file)

 

[yameen2011]

If you like, I'll help you to create a good structure for yours tables (AFTER you will have a good understanding of normalization).

Kindly help....