2nd request on how to reset a field each day to start at one.

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I need to reset the count for the amount of deliveries for each day

Each delivery has a deliveryID that follows the delivery.
I also need to counts the number of deliveries each day, the table is
something like this,

deliveryID, DailyDeliveryID,Shipper,Date

the deliveryID would be the key number that would stay with the order,

the dailydeliveryID is just a way to track how many deliveries were made
each
day. now they manually enter 1,2,3 and so on.
I would like to make the program rest the DailyDeliveryID to 01 each
morning.

thank you
michael
 
Query the date to get to count of deliveries on a given date.

If you need to know the sequence use date/time, amending the above query to
convert the date/time field to date only.

You don't need the daily sequnce number this way.

If your date field only contains Date use this,

SELECT Count(Delivery.DeliveryReferenceNumber) AS
CountOfDeliveryReferenceNumber, Delivery.DeliveryDate
FROM Delivery
GROUP BY Delivery.DeliveryDate;

or if you use the date field with Date and Time use this.

SELECT Count(Delivery.DeliveryReferenceNumber) AS
CountOfDeliveryReferenceNumber, Format([DeliveryDate],'Short Date') AS
DelDateOnly
FROM Delivery
GROUP BY Format([DeliveryDate],'Short Date');

The data entry form should contain =Date() or =Now() on the relevant field,
you could/should also set this on the table.

If you insist on the extra field use the DMax function (see help) and add 1
to the result.
 
Thank you Craig,
I did the following SQL, using your spl with my table. I had used English
terms to better understand what the problem was.

I got the results that I think you were trying to achieve, which was the
total count of deliveries for a day


SELECT Count([Registro Entrate].NPedanaID) AS CountOfNPedanaID, [Registro
Entrate].Del
FROM [Registro Entrate]
GROUP BY [Registro Entrate].Del;

I am sorry I did not explain well enough. What I need to do number each
pallet as it arrives. I have to create a barcode for each pallet this would
include the order of arrival and the date. I currently get this number by
using the following code on a text box

[NPedana] & "-" & [Del] This is basically the arrival number of the pallet
and the date so the result for the 1st pallet of the day would be
1-20/07/2005.

Del is gotten by using Date() as the default in the Del field.
NPedana is not entered manually for each pallet that arrives. It is not a
big deal for them to number each pallet as it arrives, but if they forget
the last number and don't look back at the last entry, there might be an
error. If I can automate the number to return to 1 each day it would be the
best solution.

thank you for you help
Michael










Craig Alexander Morrison said:
Query the date to get to count of deliveries on a given date.

If you need to know the sequence use date/time, amending the above query
to
convert the date/time field to date only.

You don't need the daily sequnce number this way.

If your date field only contains Date use this,

SELECT Count(Delivery.DeliveryReferenceNumber) AS
CountOfDeliveryReferenceNumber, Delivery.DeliveryDate
FROM Delivery
GROUP BY Delivery.DeliveryDate;

or if you use the date field with Date and Time use this.

SELECT Count(Delivery.DeliveryReferenceNumber) AS
CountOfDeliveryReferenceNumber, Format([DeliveryDate],'Short Date') AS
DelDateOnly
FROM Delivery
GROUP BY Format([DeliveryDate],'Short Date');

The data entry form should contain =Date() or =Now() on the relevant
field,
you could/should also set this on the table.

If you insist on the extra field use the DMax function (see help) and add
1 to the result.

--
Slainte

Craig Alexander Morrison
Michael said:
I need to reset the count for the amount of deliveries for each day

Each delivery has a deliveryID that follows the delivery.
I also need to counts the number of deliveries each day, the table is
something like this,

deliveryID, DailyDeliveryID,Shipper,Date

the deliveryID would be the key number that would stay with the order,

the dailydeliveryID is just a way to track how many deliveries were made
each
day. now they manually enter 1,2,3 and so on.
I would like to make the program rest the DailyDeliveryID to 01 each
morning.

thank you
michael
Click to expand...
Click to expand...
 
Use the DMax domain function, but first query by date to get the sequence
for deliveries on each day.

If you use DMax on the table directly you will need to include the Date
parameter.

Just create a query that filters the table down to today's date and then
DMax on the relevant field and add 1 to the result use error trapping for
the first one as DMax will return a null.

--
Slainte

Craig Alexander Morrison
Michael said:
Thank you Craig,
I did the following SQL, using your spl with my table. I had used English
terms to better understand what the problem was.

I got the results that I think you were trying to achieve, which was the
total count of deliveries for a day


SELECT Count([Registro Entrate].NPedanaID) AS CountOfNPedanaID, [Registro
Entrate].Del
FROM [Registro Entrate]
GROUP BY [Registro Entrate].Del;

I am sorry I did not explain well enough. What I need to do number each
pallet as it arrives. I have to create a barcode for each pallet this
would include the order of arrival and the date. I currently get this
number by using the following code on a text box

[NPedana] & "-" & [Del] This is basically the arrival number of the pallet
and the date so the result for the 1st pallet of the day would be
1-20/07/2005.

Del is gotten by using Date() as the default in the Del field.
NPedana is not entered manually for each pallet that arrives. It is not a
big deal for them to number each pallet as it arrives, but if they forget
the last number and don't look back at the last entry, there might be an
error. If I can automate the number to return to 1 each day it would be
the best solution.

thank you for you help
Michael










Craig Alexander Morrison said:
Query the date to get to count of deliveries on a given date.

If you need to know the sequence use date/time, amending the above query
to
convert the date/time field to date only.

You don't need the daily sequnce number this way.

If your date field only contains Date use this,

SELECT Count(Delivery.DeliveryReferenceNumber) AS
CountOfDeliveryReferenceNumber, Delivery.DeliveryDate
FROM Delivery
GROUP BY Delivery.DeliveryDate;

or if you use the date field with Date and Time use this.

SELECT Count(Delivery.DeliveryReferenceNumber) AS
CountOfDeliveryReferenceNumber, Format([DeliveryDate],'Short Date') AS
DelDateOnly
FROM Delivery
GROUP BY Format([DeliveryDate],'Short Date');

The data entry form should contain =Date() or =Now() on the relevant
field,
you could/should also set this on the table.

If you insist on the extra field use the DMax function (see help) and add
1 to the result.

--
Slainte

Craig Alexander Morrison
Michael said:
I need to reset the count for the amount of deliveries for each day

Each delivery has a deliveryID that follows the delivery.
I also need to counts the number of deliveries each day, the table is
something like this,

deliveryID, DailyDeliveryID,Shipper,Date

the deliveryID would be the key number that would stay with the order,

the dailydeliveryID is just a way to track how many deliveries were made
each
day. now they manually enter 1,2,3 and so on.
I would like to make the program rest the DailyDeliveryID to 01 each
morning.

thank you
michael
Click to expand...
Click to expand...
Click to expand...
 
Back
Top