printing text in date field and other questions

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I have a table with date fields that mark the complteion of steps in a
manufacturing process. Not all of these steps are necessary for every job,
so that field can be null while the adjacent fields are filled. This table
is populated through a form where I can indicate whether a field is
applicable through a checkbox (a yes/no field on the same table). I have a
report that runs off a query listing the steps and the dates.

On the form
can I set it so the focus goes to the first empty date field -
regardless of which field is empty?
Can I disable the field whose checkbox is checked and populate "NA" in
the field ?

On the report
Can I Print NA for the fields whose checkbox is checked


Thanks
Brian
 
I have a table with date fields that mark the complteion of steps in a
manufacturing process. Not all of these steps are necessary for every job,
so that field can be null while the adjacent fields are filled.
Click to expand...

Tina's right: your table does not appear to be correctly normalized.
If you have one job with many steps, a better table structure would be
a many to many relationship between a Jobs table and a Steps table,
using a JobSteps table as a resolver table; rather than using multiple
fields for the steps, you would add multiple records, one for each
step.
This table
is populated through a form where I can indicate whether a field is
applicable through a checkbox (a yes/no field on the same table). I have a
report that runs off a query listing the steps and the dates.
Click to expand...
On the form
can I set it so the focus goes to the first empty date field -
regardless of which field is empty?]
Click to expand...

Not at all easily.
Can I disable the field whose checkbox is checked and populate "NA" in
the field ?
Click to expand...

No. Date/time field contain date time values, not text.
On the report
Can I Print NA for the fields whose checkbox is checked
Click to expand...

Yes, using an IIF statement as the Control Source of a textbox:

=IIF([checkboxfield], "NA", [datefield])

John W. Vinson[MVP]
 
Ok
Still a bit confused though
In order to have the manufacturing steps seperate, should I create a
seperate table with the invoice number and processing step (pulled from a
look up table)? If that is true, then how would I query to determine what
orders are finished, and what is where in the process? Currently, my querys
look to see if the field is null thereby indicating the process is not
complete.
I have always had trouble figuring out the whole normalization thing anyway.
 
comments inline.

Brian said:
Ok
Still a bit confused though
In order to have the manufacturing steps seperate, should I create a
seperate table with the invoice number and processing step (pulled from a
look up table)?
Click to expand...

well, as i said, i don't have a clear picture of your data relationships, so
i'll answer the above question as "probably". i could look at your database
and tell you how to set up the tables/relationships correctly - but that
wouldn't do you much good in the long run; the whole "give a man a fish, or
teach a man to fish" issue.
If that is true, then how would I query to determine what
orders are finished, and what is where in the process?
Click to expand...

i couldn't tell you just how to do that without a clear picture of your
(normalized) tables design. but i can tell you that normalization rules only
*rarely* have to be broken in order to manipulate the data; i doubt that
your situation falls into that category. again, i can only suggest that you
start by doing whatever is necessary to set up tables/relationships
correctly; then it will be much easier to work with the data.
Currently, my querys
look to see if the field is null thereby indicating the process is not
complete.
I have always had trouble figuring out the whole normalization thing
Click to expand...
anyway.

well, don't feel bad about that. relational data modeling is not a trivial
subject. for most people, it is the most difficult aspect of database
development to master. it takes study, and practice, practice, practice.
(unfortunately, some people skip that whole step, because they think it's
too hard or time-consuming. we see those folks all the time in the
newsgroups, asking for help as they struggle to build complex workarounds in
their queries/forms/reports to try to compensate for relational design
flaws.) the good news is that 80-90% - maybe more - of processes involve
straightforward relationships. even better news is that once you have a firm
understanding of the subject, it will become easy, and then become second
nature, so that you can handle that 80-90% quickly and easily. so hang in
there, and keep studying! :)
 
Here is a rough sketch of the before and after of my table structure

invoice_tbl
------------------
PK Invoice#
custID
dateOrdered
orderStatus (selected from lookup table ststus_lst)
dateDesigned
dateToShop
dateProcessed
dateAssembled
dateCompleted

custID is the many of a one-to-many with the customerID on the customer_tbl

I tried to normalize the tables by adding a mfgDates_tbl that has the
fields

mfgDates_tbl
-------------------
PK mfgStepID
invoice#
mfgStep (selected from a lookup table procstesp_lst)
dateComplete
and removing the datetoShop, etc fields from the invoice_tbl

I want to be able to run queries to determine what orders are still in
process, which ones are complete, and which ones still need to "go th the
shop (dateToShop = null). Under the old table the query

SELECT customer_tbl.name_last, invoice_tbl.style, invoice_tbl.[invoice#],
invoice_tbl.dateToShop, invoice_tbl.dateProcessed,
invoice_tbl.dateAssembled, invoice_tbl.dateCompleted,
invoice_tbl.OrderStatus
FROM customer_tbl INNER JOIN invoice_tbl ON customer_tbl.custID =
invoice_tbl.customerID
WHERE (((invoice_tbl.dateToShop) Is Not Null) AND
((invoice_tbl.dateAssembled) Is Null) AND ((invoice_tbl.dateComplete) Is
Null) AND ((invoice_tbl.OrderStatus)="Active"))
ORDER BY invoice_tbl.dateOrdered;

produced the results I needed. My other questions involve If I normalized
the table correctly with the generation of the mfgDates_tbl, how do I
reproduce the WHERE (((invoice_tbl.dateToShop) Is Not Null) AND
((invoice_tbl.dateAssembled) Is Null) AND ((invoice_tbl.dateComplete) Is
Null) statement results when I now have these dates as seperate entries in
the the same mfgDates_tbl field?

If you would prefer to e-mail me - TechDoc57@cox(NOSPAM).com (removing the
obvious part)

Thanks
Brian


"tina" <nospam@
address.com> wrote in message
 
comments inline.

Brian said:
Here is a rough sketch of the before and after of my table structure

invoice_tbl
------------------
PK Invoice#
custID
dateOrdered
orderStatus (selected from lookup table ststus_lst)
dateDesigned
dateToShop
dateProcessed
dateAssembled
dateCompleted

custID is the many of a one-to-many with the customerID on the customer_tbl

I tried to normalize the tables by adding a mfgDates_tbl that has the
fields

mfgDates_tbl
-------------------
PK mfgStepID
invoice#
mfgStep (selected from a lookup table procstesp_lst)
dateComplete
and removing the datetoShop, etc fields from the invoice_tbl
Click to expand...

very good.
I want to be able to run queries to determine what orders are still in
process, which ones are complete, and which ones still need to "go th the
shop (dateToShop = null). Under the old table the query

SELECT customer_tbl.name_last, invoice_tbl.style, invoice_tbl.[invoice#],
invoice_tbl.dateToShop, invoice_tbl.dateProcessed,
invoice_tbl.dateAssembled, invoice_tbl.dateCompleted,
invoice_tbl.OrderStatus
FROM customer_tbl INNER JOIN invoice_tbl ON customer_tbl.custID =
invoice_tbl.customerID
WHERE (((invoice_tbl.dateToShop) Is Not Null) AND
((invoice_tbl.dateAssembled) Is Null) AND ((invoice_tbl.dateComplete) Is
Null) AND ((invoice_tbl.OrderStatus)="Active"))
ORDER BY invoice_tbl.dateOrdered;

produced the results I needed. My other questions involve If I normalized
the table correctly with the generation of the mfgDates_tbl, how do I
reproduce the WHERE (((invoice_tbl.dateToShop) Is Not Null) AND
((invoice_tbl.dateAssembled) Is Null) AND ((invoice_tbl.dateComplete) Is
Null) statement results when I now have these dates as seperate entries in
the the same mfgDates_tbl field?
Click to expand...

well, sounds like what you really what to know is "where is each invoice
NOW?", or "what is the most recently completed step for each invoice?". try
using a Totals query on the table mfgDates_tbl. Group By invoice#, and Max
dateComplete, should give you the last step completed on each invoice.

hth
 
Excellent - Thank you !!!

I'll go fish now - :)

Brian

tina said:
comments inline.

Brian said:
Here is a rough sketch of the before and after of my table structure

invoice_tbl
------------------
PK Invoice#
custID
dateOrdered
orderStatus (selected from lookup table ststus_lst)
dateDesigned
dateToShop
dateProcessed
dateAssembled
dateCompleted

custID is the many of a one-to-many with the customerID on the customer_tbl

I tried to normalize the tables by adding a mfgDates_tbl that has the
fields

mfgDates_tbl
-------------------
PK mfgStepID
invoice#
mfgStep (selected from a lookup table procstesp_lst)
dateComplete
and removing the datetoShop, etc fields from the invoice_tbl
Click to expand...

very good.
I want to be able to run queries to determine what orders are still in
process, which ones are complete, and which ones still need to "go th the
shop (dateToShop = null). Under the old table the query

SELECT customer_tbl.name_last, invoice_tbl.style, invoice_tbl.[invoice#],
invoice_tbl.dateToShop, invoice_tbl.dateProcessed,
invoice_tbl.dateAssembled, invoice_tbl.dateCompleted,
invoice_tbl.OrderStatus
FROM customer_tbl INNER JOIN invoice_tbl ON customer_tbl.custID =
invoice_tbl.customerID
WHERE (((invoice_tbl.dateToShop) Is Not Null) AND
((invoice_tbl.dateAssembled) Is Null) AND ((invoice_tbl.dateComplete) Is
Null) AND ((invoice_tbl.OrderStatus)="Active"))
ORDER BY invoice_tbl.dateOrdered;

produced the results I needed. My other questions involve If I normalized
the table correctly with the generation of the mfgDates_tbl, how do I
reproduce the WHERE (((invoice_tbl.dateToShop) Is Not Null) AND
((invoice_tbl.dateAssembled) Is Null) AND ((invoice_tbl.dateComplete) Is
Null) statement results when I now have these dates as seperate entries
in
the the same mfgDates_tbl field?
Click to expand...

well, sounds like what you really what to know is "where is each invoice
NOW?", or "what is the most recently completed step for each invoice?".
try
using a Totals query on the table mfgDates_tbl. Group By invoice#, and Max
dateComplete, should give you the last step completed on each invoice.

hth
If you would prefer to e-mail me - TechDoc57@cox(NOSPAM).com (removing the
obvious part)

Thanks
Brian


"tina" <nospam@
address.com> wrote in message
Click to expand...
Click to expand...
 
you're welcome :)


Brian said:
Excellent - Thank you !!!

I'll go fish now - :)

Brian

tina said:
comments inline.

Brian said:
Here is a rough sketch of the before and after of my table structure

invoice_tbl
------------------
PK Invoice#
custID
dateOrdered
orderStatus (selected from lookup table ststus_lst)
dateDesigned
dateToShop
dateProcessed
dateAssembled
dateCompleted

custID is the many of a one-to-many with the customerID on the customer_tbl

I tried to normalize the tables by adding a mfgDates_tbl that has the
fields

mfgDates_tbl
-------------------
PK mfgStepID
invoice#
mfgStep (selected from a lookup table procstesp_lst)
dateComplete
and removing the datetoShop, etc fields from the invoice_tbl
Click to expand...

very good.
I want to be able to run queries to determine what orders are still in
process, which ones are complete, and which ones still need to "go th the
shop (dateToShop = null). Under the old table the query

SELECT customer_tbl.name_last, invoice_tbl.style, invoice_tbl.[invoice#],
invoice_tbl.dateToShop, invoice_tbl.dateProcessed,
invoice_tbl.dateAssembled, invoice_tbl.dateCompleted,
invoice_tbl.OrderStatus
FROM customer_tbl INNER JOIN invoice_tbl ON customer_tbl.custID =
invoice_tbl.customerID
WHERE (((invoice_tbl.dateToShop) Is Not Null) AND
((invoice_tbl.dateAssembled) Is Null) AND ((invoice_tbl.dateComplete) Is
Null) AND ((invoice_tbl.OrderStatus)="Active"))
ORDER BY invoice_tbl.dateOrdered;

produced the results I needed. My other questions involve If I normalized
the table correctly with the generation of the mfgDates_tbl, how do I
reproduce the WHERE (((invoice_tbl.dateToShop) Is Not Null) AND
((invoice_tbl.dateAssembled) Is Null) AND ((invoice_tbl.dateComplete) Is
Null) statement results when I now have these dates as seperate entries
in
the the same mfgDates_tbl field?
Click to expand...

well, sounds like what you really what to know is "where is each invoice
NOW?", or "what is the most recently completed step for each invoice?".
try
using a Totals query on the table mfgDates_tbl. Group By invoice#, and Max
dateComplete, should give you the last step completed on each invoice.

hth
If you would prefer to e-mail me - TechDoc57@cox(NOSPAM).com (removing the
obvious part)

Thanks
Brian


"tina" <nospam@
address.com> wrote in message
comments inline.

Ok
Still a bit confused though
In order to have the manufacturing steps seperate, should I create a
seperate table with the invoice number and processing step (pulled
from a
look up table)?

well, as i said, i don't have a clear picture of your data relationships,
so
i'll answer the above question as "probably". i could look at your
database
and tell you how to set up the tables/relationships correctly - but
that
wouldn't do you much good in the long run; the whole "give a man a
fish,
or
teach a man to fish" issue.

If that is true, then how would I query to determine what
orders are finished, and what is where in the process?

i couldn't tell you just how to do that without a clear picture of your
(normalized) tables design. but i can tell you that normalization rules
only
*rarely* have to be broken in order to manipulate the data; i doubt
that
your situation falls into that category. again, i can only suggest that
you
start by doing whatever is necessary to set up tables/relationships
correctly; then it will be much easier to work with the data.

Currently, my querys
look to see if the field is null thereby indicating the process is not
complete.
I have always had trouble figuring out the whole normalization thing
anyway.

well, don't feel bad about that. relational data modeling is not a trivial
subject. for most people, it is the most difficult aspect of database
development to master. it takes study, and practice, practice,
practice.
(unfortunately, some people skip that whole step, because they think it's
too hard or time-consuming. we see those folks all the time in the
newsgroups, asking for help as they struggle to build complex workarounds
in
their queries/forms/reports to try to compensate for relational design
flaws.) the good news is that 80-90% - maybe more - of processes
involve
straightforward relationships. even better news is that once you have a
firm
understanding of the subject, it will become easy, and then become second
nature, so that you can handle that 80-90% quickly and easily. so
Click to expand...
hang
in
there, and keep studying! :)


the table description is a little sketchy, but it sounds like your
table
is
not normalized. suggest you read up on table normalization, and make
any
necessary adjustments to your table(s) design before moving further
into
query/form/report development. see
http://home.att.net/~california.db/tips.html#aTip1 for more
information.

hth


I have a table with date fields that mark the complteion of steps
in a
manufacturing process. Not all of these steps are necessary for every
job,
so that field can be null while the adjacent fields are filled.
This
table
is populated through a form where I can indicate whether a field is
applicable through a checkbox (a yes/no field on the same table). I
have
a
report that runs off a query listing the steps and the dates.

On the form
can I set it so the focus goes to the first empty date field -
regardless of which field is empty?
Can I disable the field whose checkbox is checked and populate
"NA"
in
the field ?

On the report
Can I Print NA for the fields whose checkbox is checked


Thanks
Brian
Click to expand...
Click to expand...
Click to expand...
 
Back
Top