Need a good Querie

[Guest]

*here is the post from 'forums' where not many people responded because I put
it in the wrong area. Thanks in advance!!*

Sorry, let me try this again. It's hard for me to explain everything. Let me
start with the one table. I work at a metal alloys company, and this database
is for documenting the processes that a metal alloy goes through from being
sorted out to cleaned before being shipped out.

"Sorting Rates" is the one table where information will ALWAYS be entered
into because the alloys are always sorted first. The following colums are
information that must be entered into the system: [UR Lot], [Supplier],
[Alloy], [Processing Time], [Starting Weight], [Starting Date], [Start Time],
[End Date], [End Time], [Material Type], [Remarks], [Operator].

From this process of sorting, the alloy may go through 1 or more of 4
processes. Each one of these processes has it's own table that is identical
to "Sorting Rates", and they are called "Degreasing Rates", "Briquetting
Rates", "Torching Rates", and "Roto-Blasting Rates".

Currently there are 5 different queries (1 for each table). They pull
information from the table to generate a query where an expression is added
to calculate the cost for that process for each alloy. Now, we want 1 query
that can be used to show the cost for all the processes on one report, then
have an expression to show the "total cost" for the alloys handling (which
could be sorting + 1 or more other processes).

At this point I'm not sure if it would be better to use the existing 5
queries to collect all of the information, or to work with the tables for all
the information. I would prefer the tables if possible because this way this
new query would be independent of any search criteria that would be entered
to the existing queries. Any help would be greatly appreciated!!!

You've described "how", but not "what".

Without a better idea of what data you have stored in your table structure,
it will be a bit difficult to offer specific advice.

Also, we aren't there, so it's tough to guess what you might mean by "go
thru the 'sorting' process".

And it usually isn't necessary to create empty "dummy" rows in your "child"
("many") tables just to see a row in your parent table in a query. Instead,
open the query in design mode and change the join property to show all the
parent table rows, plus any values (including none) from the related child
tables.

--
Regards

Jeff Boyce
<Access MVP>

Ok I'm going to try to explain this the best that I can. I have a total of 6
different tables with information. These items that are entered are definitly
going to go thu the "sorting" process, then from there can go to 4 other
possible areas. The user needs to be able to document this all from 1 main
form and update it across the multiple tables. 1 table contains the common
fields between all the tables, we'll call this [Main].

I want in my main form the following to occur:
1) user enters in the unique information for the 1 column headings for
[Main] and auto fills in for the other sub forms with the same column
headings (this I can do).
2) across all sub-forms rows I need to have at least 1 row in every subform
containing the unique number, and also automatically put in a default value.

this is needed because otherwise when I do a querie I cannot see the
information if I have empty fields in a record when generating the report.
therefore in evey table I should have the unique id appear with a default
value of anything (0 would be fine) so I can still generate a querie and
report later on.

 

[Margaret Bartley]

There are several different ways to do this. Part of your strategy should
be what
kinds of changes or modifications you will need to do in the future.

One question, does the URLot stay the same for all processess for the same
alloy? You say an alloy may go through 1 or more of 4 processes. I am
assuming the
UR Lot is the field that joins all these tables?

If so, you could use a query to link all five tables, and do the sums on a
header break
in the report.

*here is the post from 'forums' where not many people responded because I
put
it in the wrong area. Thanks in advance!!*

Sorry, let me try this again. It's hard for me to explain everything. Let
me
start with the one table. I work at a metal alloys company, and this
database
is for documenting the processes that a metal alloy goes through from
being
sorted out to cleaned before being shipped out.

"Sorting Rates" is the one table where information will ALWAYS be entered
into because the alloys are always sorted first. The following colums are
information that must be entered into the system: [UR Lot], [Supplier],
[Alloy], [Processing Time], [Starting Weight], [Starting Date], [Start
Time],
[End Date], [End Time], [Material Type], [Remarks], [Operator].

From this process of sorting, the alloy may go through 1 or more of 4
processes. Each one of these processes has it's own table that is
identical
to "Sorting Rates", and they are called "Degreasing Rates", "Briquetting
Rates", "Torching Rates", and "Roto-Blasting Rates".

Currently there are 5 different queries (1 for each table). They pull
information from the table to generate a query where an expression is
added
to calculate the cost for that process for each alloy. Now, we want 1
query
that can be used to show the cost for all the processes on one report,
then
have an expression to show the "total cost" for the alloys handling (which
could be sorting + 1 or more other processes).

At this point I'm not sure if it would be better to use the existing 5
queries to collect all of the information, or to work with the tables for
all
the information. I would prefer the tables if possible because this way
this
new query would be independent of any search criteria that would be
entered
to the existing queries. Any help would be greatly appreciated!!!

You've described "how", but not "what".

Without a better idea of what data you have stored in your table
structure,
it will be a bit difficult to offer specific advice.

Also, we aren't there, so it's tough to guess what you might mean by "go
thru the 'sorting' process".

And it usually isn't necessary to create empty "dummy" rows in your
"child"
("many") tables just to see a row in your parent table in a query.
Instead,
open the query in design mode and change the join property to show all
the
parent table rows, plus any values (including none) from the related
child
tables.

--
Regards

Jeff Boyce
<Access MVP>

Ok I'm going to try to explain this the best that I can. I have a total
of 6
different tables with information. These items that are entered are definitly
going to go thu the "sorting" process, then from there can go to 4
other
possible areas. The user needs to be able to document this all from 1
main
form and update it across the multiple tables. 1 table contains the
common
fields between all the tables, we'll call this [Main].

I want in my main form the following to occur:
1) user enters in the unique information for the 1 column headings for
[Main] and auto fills in for the other sub forms with the same column
headings (this I can do).
2) across all sub-forms rows I need to have at least 1 row in every subform
containing the unique number, and also automatically put in a default value.

this is needed because otherwise when I do a querie I cannot see the
information if I have empty fields in a record when generating the
report.
therefore in evey table I should have the unique id appear with a
default
value of anything (0 would be fine) so I can still generate a querie
and
report later on.