Keeping running total of part numbers entered into seperate fields

[Shannan Casteel via AccessMonster.com]

I have a form for entering part numbers along with the associated quantity
for each part. There are 25 Part fields and 25 associated Quantity fields.
If I go to record 1 and enter part number 1234 for part1, 3 for quantity1,
and 1235 for part2, 8 for quantity2, and on record 2 I enter 1236 for part1,
2 for quantity1, and 1234 for part2, 2 for quantity2, how would I get Access
to spit out a report that says something like this:

Part Number Total Number of Occurrences
1234 5
1235 8
1236 2

In other words, I need Access to search for anything entered in Part1, Part2,
Part3.....Part25, and return the number of occurrences of whatever it finds.
How do I accomplish this?
Thanks in advace for any help.
--
Message posted via http://www.accessmonster.com

[Larry Linson]

Access is _data-based_, not _form-based_. You need to describe how you are
storing your data, and then perhaps someone can describe how you can create
the report you want.

Access reports do not retrieve data from forms (unless you jump through some
hoops to do so to show the criteria you used to select the data you are
reporting, for example); Access reports are bound to a Record Source -- a
table, query, or SQL statement that retrieves the data from the tables where
it resides.

Larry Linson
Microsoft Access MVP

"Shannan Casteel via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:52***********@AccessMonster.com...

I have a form for entering part numbers along with the associated quantity
for each part. There are 25 Part fields and 25 associated Quantity
fields.
If I go to record 1 and enter part number 1234 for part1, 3 for quantity1,
and 1235 for part2, 8 for quantity2, and on record 2 I enter 1236 for
part1,
2 for quantity1, and 1234 for part2, 2 for quantity2, how would I get
Access
to spit out a report that says something like this:

Part Number Total Number of Occurrences
1234 5
1235 8
1236 2

In other words, I need Access to search for anything entered in Part1,
Part2,
Part3.....Part25, and return the number of occurrences of whatever it
finds.
How do I accomplish this?
Thanks in advace for any help.
--
Message posted via http://www.accessmonster.com

[Shannan Casteel via AccessMonster.com]

Larry,

Thank you for your reply. I'm sorry I left that information out. I should
have known to include it. Okay, to the point. Each of the part numbers is
stored in its own field in a table. There are 25 columns or fields for all
of the part numbers. There are also 25 fields for the quantities. For
example, in my table there are fields: PartNumber1,PartNumber2,PartNumber3,..
..,Partnumber25, along with quantities listed the same way: Quantity1,
Quantity2, Quantity3,..., Quantity25.

I need a way to get Access to pull out PartNumber1 - PartNumber25 along with
their associated quantities and keep a running total, so at the end of each
month I could run a report called "All Parts," and Access would give a list
of all part number that have been listed along with their total quantities
from all the records.

Please Help!

As unlikely as it may seem, I have had some experience with Access, and I'm
not afraid to do a little VBA.

Thanks,

Shannan

Larry Linson wrote:

Access is _data-based_, not _form-based_. You need to describe how you are
storing your data, and then perhaps someone can describe how you can create
the report you want.

Access reports do not retrieve data from forms (unless you jump through some
hoops to do so to show the criteria you used to select the data you are
reporting, for example); Access reports are bound to a Record Source -- a
table, query, or SQL statement that retrieves the data from the tables where
it resides.

Larry Linson
Microsoft Access MVP

I have a form for entering part numbers along with the associated quantity
for each part. There are 25 Part fields and 25 associated Quantity

[quoted text clipped - 18 lines]

Thanks in advace for any help.

--
Message posted via http://www.accessmonster.com

[pietlinden]

Long and short of it is that this design is *not* good. Stop now and
save yourself headaches later. I would normalize now before this gets
really ugly.

Any reason you can't have a design like this:

PartNo Text(50)
Quantity Long Integer

If there are several "batches" of parts or something so you have
multiple records per part, then you cannot use PartNo as your primary
key.

but then at least the summing is easy... Just use a totals query.

SELECT PartNo, Sum(Quantity) AS TotalCount
FROM Parts
GROUP BY PartNo
ORDER BY PartNo;

[Shannan Casteel via AccessMonster.com]

I'm not very good with SQL, anyway to do this with VB code?

Thanks for your help

pi********@hotmail.com wrote:

Long and short of it is that this design is *not* good. Stop now and
save yourself headaches later. I would normalize now before this gets
really ugly.

Any reason you can't have a design like this:

PartNo Text(50)
Quantity Long Integer

If there are several "batches" of parts or something so you have
multiple records per part, then you cannot use PartNo as your primary
key.

but then at least the summing is easy... Just use a totals query.

SELECT PartNo, Sum(Quantity) AS TotalCount
FROM Parts
GROUP BY PartNo
ORDER BY PartNo;

--
Message posted via http://www.accessmonster.com

[Shannan Casteel via AccessMonster.com]

The database is for warranty claims. Parts are reimbursed. Every record
will represent a claim. Multiple parts can be listed on each warranty claim.
I kinda figured my designs was bad, but I don't know how to correct it and
make it work. I need to be able to run reports at the end of each month that
tell me all the parts that have been listed and how many times each was
listed. I'm sorry I didn't understand your post. I thought I was pretty
good with Access, but after this I've figured out that I've only scratched
the surface.

Anyway, thanks for your help.

Shannan

Shannan Casteel wrote:

I'm not very good with SQL, anyway to do this with VB code?

Thanks for your help

Long and short of it is that this design is *not* good. Stop now and
save yourself headaches later. I would normalize now before this gets

[quoted text clipped - 15 lines]

GROUP BY PartNo
ORDER BY PartNo;

--
Message posted via http://www.accessmonster.com

[Bob Quintal]

"Shannan Casteel via AccessMonster.com"
<fo***@AccessMonster.com> wrote in
news:53***********@AccessMonster.com:

The database is for warranty claims. Parts are reimbursed.
Every record will represent a claim. Multiple parts can be
listed on each warranty claim. I kinda figured my designs was
bad, but I don't know how to correct it and make it work. I
need to be able to run reports at the end of each month that
tell me all the parts that have been listed and how many times
each was listed. I'm sorry I didn't understand your post. I
thought I was pretty good with Access, but after this I've
figured out that I've only scratched the surface.

Anyway, thanks for your help.

Shannan
Access is a relational database tool, the right structure for
this job is to have two related tables, the first is your claim,
the second is part-claim table, which is related to the claim
table..

Your first table has everything except the part numbers and
quantities, I assume the claimant, date claimed, etc.

The second table should have the claim number, the part number
and the quantity. If you replaced two parts on a claim there
will be two rows in this table. If you replaced four parts on a
second claim, you would have four rows more.

example of this table:
Claim_number Part_Replaced qty_replaced.
============ ============= ============
1 abcde123 2
1 fdsa765 1
2 abcde123 1
2 preasd001 3
2 ghjkl333 1
2 zzzzzz3 4

This way you can sumarize on the claim number or the part number
as you wish, just by writing different queries.

Shannan Casteel wrote:

I'm not very good with SQL, anyway to do this with VB code?

If you can do it in VBA you can do it in SQL easier.

--
Bob Quintal

PA is y I've altered my email address.

[Shannan Casteel via AccessMonster.com]

Bob,

Thank you for your time. I set up two tables (one with the regular claim
info and another with ClaimNumber, PartNumber, and QuantityReplaced). The
ClaimNumber is an autonumber and the primary key in both tables. I made a
one to one relationship between the two tables. I have a form for the parts.
It includes 25 text boxes for both the part numbers and the quantities, so 50
total. I set the control sources for each of the part number text boxes to
PartNumber from the PartsReplaced table. I set the control sources for each
of the quantity text boxes to QuantityReplaced from the PartsReplaced table.

When I enter a part number and press tab to enter the quantity, every part
number text box on the page changes to match the first. In other words, if I
enter "1234" in the first box all other 24 text boxes change to read "1234".
The same thing happens for the quantities.

Should I make the relationship one - to - many? Not really sure how to do
this...

Please help...Thanks for your time,

Shannan

Bob Quintal wrote:

The database is for warranty claims. Parts are reimbursed.
Every record will represent a claim. Multiple parts can be

[quoted text clipped - 9 lines]

Shannan

Access is a relational database tool, the right structure for
this job is to have two related tables, the first is your claim,
the second is part-claim table, which is related to the claim
table..

Your first table has everything except the part numbers and
quantities, I assume the claimant, date claimed, etc.

The second table should have the claim number, the part number
and the quantity. If you replaced two parts on a claim there
will be two rows in this table. If you replaced four parts on a
second claim, you would have four rows more.

example of this table:
Claim_number Part_Replaced qty_replaced.
============ ============= ============
1 abcde123 2
1 fdsa765 1
2 abcde123 1
2 preasd001 3
2 ghjkl333 1
2 zzzzzz3 4

This way you can sumarize on the claim number or the part number
as you wish, just by writing different queries.

I'm not very good with SQL, anyway to do this with VB code?

If you can do it in VBA you can do it in SQL easier.

--
Message posted via http://www.accessmonster.com