R
rockycagney
Also can i get fields to automatically fill to the left column if field
content is deleted?
content is deleted?
T
tina
to answer the question in the Subject line (btw, it's better to include all
questions in the text of the post), in table design view, put a zero (0) in
the DefaultValue property of each field that you want to default to zero.
i have no idea what you mean by that. can you clarify?
hth
questions in the text of the post), in table design view, put a zero (0) in
the DefaultValue property of each field that you want to default to zero.
i have no idea what you mean by that. can you clarify?
hth
J
Jeff Boyce
Sorry, but the recovering statistics instructor in me couldn't let this
be...
If you have "nothing" (?no content?) in a field, putting a zero in it
implies that it has something in it, a value, and specifically, a value = 0.
I can easily imagine when that zero would be significant, for example, "how
many candy bars are left in my pantry?"
If you'll explain why/how zero means "nothing in there" in your application,
I can learn something new?!
--
Regards
Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Microsoft Registered Partner
https://partner.microsoft.com/
be...
If you have "nothing" (?no content?) in a field, putting a zero in it
implies that it has something in it, a value, and specifically, a value = 0.
I can easily imagine when that zero would be significant, for example, "how
many candy bars are left in my pantry?"
If you'll explain why/how zero means "nothing in there" in your application,
I can learn something new?!
--
Regards
Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Microsoft Registered Partner
https://partner.microsoft.com/
T
tina
recovering statistics instructor
LOL
LOL
Jeff Boyce said:
R
rockycagney
thank you for taking the time to reply to my question. I have posted an
update with the other reply if you would like a look. Thanks
update with the other reply if you would like a look. Thanks
R
rockycagney
I work in a warehouse with about 600 product lines stored on pallets in
defined locations. the back up stock used to be mept above the location but
because of lack of space I created a database to record the locations of back
up stock stored in one area of the warehouse.
The database has 11 coloumns auto number, product code,description,location,
batch in use,1st back up batch, 1st b/up location, 2nd back up batch, 2nd
b/up location and 3rd batch and location.
The zero is mainly for cosmetic purposes as i want all fields to have
something in them it looks better in the database and queries i run.
I also would like to be able to delete the batch in use when it is finished
and have 1st back up move to batch in use and 2nd back up and location
default to 1st back up and location and likewise the 3rd, instead of doing it
all manually.
Thanks for your interest.
defined locations. the back up stock used to be mept above the location but
because of lack of space I created a database to record the locations of back
up stock stored in one area of the warehouse.
The database has 11 coloumns auto number, product code,description,location,
batch in use,1st back up batch, 1st b/up location, 2nd back up batch, 2nd
b/up location and 3rd batch and location.
The zero is mainly for cosmetic purposes as i want all fields to have
something in them it looks better in the database and queries i run.
I also would like to be able to delete the batch in use when it is finished
and have 1st back up move to batch in use and 2nd back up and location
default to 1st back up and location and likewise the 3rd, instead of doing it
all manually.
Thanks for your interest.
J
Jeff Boyce
A couple issues to point out...
When I see repeating fields (1st batch, 1st batch location, 2nd batch, ...),
I think of ... spreadsheets! Your data design is not well-normalized and
will not allow you to make best use of Access' features and functions until
it is. Is there a reason why you are not using a spreadsheet instead?
A zero "for cosmetic purposes" is both unnecessary and potentially confusing
(the "statistics" aspect).
If you want to see a zero, use a query and the Nz() function to turn a Null
into a 0 for display purposes, but think twice before physically changing
the table values -- tables store data, forms/reports/queries display it.
The biggest issue, should you be looking for ways to improve on what you
have already, it the first point, though. Until you do more work on the
normalization of your data structure, you'll be stuck with the work-arounds
you'll have to come up with to handle repeating columns of data.
--
Good luck on your project
Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Microsoft Registered Partner
https://partner.microsoft.com/
When I see repeating fields (1st batch, 1st batch location, 2nd batch, ...),
I think of ... spreadsheets! Your data design is not well-normalized and
will not allow you to make best use of Access' features and functions until
it is. Is there a reason why you are not using a spreadsheet instead?
A zero "for cosmetic purposes" is both unnecessary and potentially confusing
(the "statistics" aspect).
If you want to see a zero, use a query and the Nz() function to turn a Null
into a 0 for display purposes, but think twice before physically changing
the table values -- tables store data, forms/reports/queries display it.
The biggest issue, should you be looking for ways to improve on what you
have already, it the first point, though. Until you do more work on the
normalization of your data structure, you'll be stuck with the work-arounds
you'll have to come up with to handle repeating columns of data.
--
Good luck on your project
Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Microsoft Registered Partner
https://partner.microsoft.com/
M
mnature
You aren't asking for advice on your database design. But since you posted
in the design section, I'm going to give you some advice.
You really need a Products table, something like
tbl_Products
ProductID
ProductDescription
You need this because keeping track of 600 discrete product lines is a lot
of information, though once you have created this table it will probably not
change very often.
Now you need a locations table, because I think what you are doing is moving
your back up stock up to the main location, from a variety of other
locations. I am assuming that your backup locations are not specific for any
particular product.
tbl_Locations
LocationID
ProductID
LocationInfo
LocationDate
This will not be an elegant way to do this, but will fit in with how you
have been doing it, and will be relatively easy to use. All of your backup
locations will be put into this table. I will assume that you are somehow
keeping track of your main locations in some other way.
As you enter a product into a location, you enter the current date. This
date will allow you to sort, to find out which backup stack to use next. You
will use the oldest date first. The oldest date corresponds to your 1st
backup batch. However, you don't have to worry about moving items from one
column to another, because the date will always tell you which batch is the
next one to use, and you don't have to worry about having one backup batch or
ten backup batches, because the date will indicate how to choose the one to
use.
This way of organizing your data will do several things for you. You can
make forms/subforms to either show where all of a particular product is
(which means you will also know how many you have), or to show what is in a
particular location. You can also easily sort out the empty locations,
perhaps printing out a report that can be filled in by hand with where newly
received products should be stored. That report can then be used to update
the database with the new shipments (and can also be signed by those doing
it, so that any mistakes discovered later can be properly dealt with). You
will have to remember to delete products that are moved into their normal
distribution locations, but you could even put in a field (in the location
table) to indicate items to be moved, print out a report based on that field
being marked, have the items moved, and then use the report to update the
database (again having people sign that report to indicate responsibility).
I hope you don't mind my making suggestions. It just seems like you are
going to a lot of work that could more easily be done by the database itself.
in the design section, I'm going to give you some advice.
You really need a Products table, something like
tbl_Products
ProductID
ProductDescription
You need this because keeping track of 600 discrete product lines is a lot
of information, though once you have created this table it will probably not
change very often.
Now you need a locations table, because I think what you are doing is moving
your back up stock up to the main location, from a variety of other
locations. I am assuming that your backup locations are not specific for any
particular product.
tbl_Locations
LocationID
ProductID
LocationInfo
LocationDate
This will not be an elegant way to do this, but will fit in with how you
have been doing it, and will be relatively easy to use. All of your backup
locations will be put into this table. I will assume that you are somehow
keeping track of your main locations in some other way.
As you enter a product into a location, you enter the current date. This
date will allow you to sort, to find out which backup stack to use next. You
will use the oldest date first. The oldest date corresponds to your 1st
backup batch. However, you don't have to worry about moving items from one
column to another, because the date will always tell you which batch is the
next one to use, and you don't have to worry about having one backup batch or
ten backup batches, because the date will indicate how to choose the one to
use.
This way of organizing your data will do several things for you. You can
make forms/subforms to either show where all of a particular product is
(which means you will also know how many you have), or to show what is in a
particular location. You can also easily sort out the empty locations,
perhaps printing out a report that can be filled in by hand with where newly
received products should be stored. That report can then be used to update
the database with the new shipments (and can also be signed by those doing
it, so that any mistakes discovered later can be properly dealt with). You
will have to remember to delete products that are moved into their normal
distribution locations, but you could even put in a field (in the location
table) to indicate items to be moved, print out a report based on that field
being marked, have the items moved, and then use the report to update the
database (again having people sign that report to indicate responsibility).
I hope you don't mind my making suggestions. It just seems like you are
going to a lot of work that could more easily be done by the database itself.
R
rockycagney
Thank you very much for taking an interest in my problem, what you have
suggested looks very interesting and I will be looking at this with great
interest
suggested looks very interesting and I will be looking at this with great
interest