how to mark some fields in a table

[Guest]

Hi. This is a bit hard to explain. I have a table with my inventory, in
quantity pricing.

There are only some of my quantities for each products that are in my
catalogue. I want to mark them as Catalogue quantities. The problem is : I
have 15 quantities possible in my table, and sometimes let say quantities 2
to 6 would be in the catalogue and other times it would be quantities 1 to 6,
or 2 to 7.

I want to be able to make queries that will retrive only my catalogue
quantities.

Any suggestion ?

Thanks in advance

 

[RBear3]

Why does your table have more than one quantity field? This sounds like a
normalization issue. If you give us more details, perhaps we can help you
normalize your data, then we can address the catalog issue.

 

[Guest]

I just convert this database from Access2000 to 2007.
My princing has to be by quantity. 15 possible

Ex:
qty 250 500 1000 ...
cost price .15 .10 .07 ...
list price .20 .15 .10 ...
Mark-up 50% 35% 20% ...

 

[Joseph Meehan]

Hi. This is a bit hard to explain. I have a table with my inventory,
in quantity pricing.

There are only some of my quantities for each products that are in my
catalogue. I want to mark them as Catalogue quantities. The problem
is : I have 15 quantities possible in my table, and sometimes let say
quantities 2 to 6 would be in the catalogue and other times it would
be quantities 1 to 6, or 2 to 7.

I want to be able to make queries that will retrive only my catalogue
quantities.

Any suggestion ?

Thanks in advance

I am going to have to think about that table design. I guess I really
don't know the business model so it may be OK

Now about selecting. How are they selecting? How do you know they need
to be catalogue quantities?

I am going to guess that you may want to add two additional fields
(yes-no) for catalogue quantities and the second for selected.

You would set the catalogue quantities to yes then filter what ever
report or query that you are using for input to display only those with the
catalogue quantity set to yes. Allow the user to select by setting the
other yes no filed to yes. There are several variations to this idea.

 

[Guest]

our company is really complicated, trust me, it's really frustrating
sometimes ! I try to simplify things but it seams to make them just worse !

I was going to add a yes/no field, but as my quantity pricing is all in the
same table, and in the same record, I kind of have to add 15 new fields, one
for Qty1, one for Qty2, etc...
(I really hope I don't have to put my quantities in seperated records...it
would change so many things!)

Ex: Product1, Qty1, Qty2,... CatQty1,CatQty2... (where CatQty is my yes/no
field)

Users would have access to check the box to indicate which are in the
catalogue.
Then I need to export the data, but only what is in the catalogue, to send
it to the company who takes care of our website. We only want catalogue
prices on there.

But then I don't know how to use that field in my query...

The more I think of it, the more it seams impossible...

 

[Larry Daugherty]

Hi Julie,

It sure sounds like you need a better understanding of the
fundamentals of RDBMS design. Any time you find repeating value sets
within a record you are seeing a good candidate for a new child table.

The biggest single issue is that you need to understand your entire
application before the schema is designed. Get the schema correctly
and you have a good base for further development. Get it wrong and
every change in requirements requires a major change in the
application - a nightmare! My impression is that you may have one of
those on your hands.

There is a lot to learn in getting things done properly with Access.
Only part of it is due to Access itself. Lots and lots of it has to
do with RDBMS considerations.

I recommend frequent visits to www.mvps.org/access and lurk:

microsoft.public.access (here)
microsoft.public.access.gettingstarted
microsoft.public.access.tablesdesign

Good luck!

HTH

 

[John W. Vinson]

(I really hope I don't have to put my quantities in seperated records...it
would change so many things!)

Well... I hate to say, but I think you do.

The problem you're having here is just one example.

What will you do when the bosses say you need SIXTEEN quantities for one
particular item? or all items, it makes no difference? You'll need to redesign
your Table, rewrite all your Queries, redesign all your Forms, redesign all
your Reports. Ouch!

What will you do when someone wants to know where the item quantity price
break from $10 to below $10 per item falls? Trivial with normalized tables;
search 15 fields with your current design.

There *are* good reasons to normalize. Some of us (I'm certainly guilty!)
treat it almost as an article of faith... but there are good practical
advantages too.

John W. Vinson [MVP]

 

[Joseph Meehan]

our company is really complicated, trust me, it's really frustrating
sometimes ! I try to simplify things but it seams to make them just
worse !

I was going to add a yes/no field, but as my quantity pricing is all
in the same table, and in the same record, I kind of have to add 15
new fields, one for Qty1, one for Qty2, etc...
(I really hope I don't have to put my quantities in seperated
records...it would change so many things!)

Ex: Product1, Qty1, Qty2,... CatQty1,CatQty2... (where CatQty is my
yes/no field)

Users would have access to check the box to indicate which are in the
catalogue.
Then I need to export the data, but only what is in the catalogue, to
send it to the company who takes care of our website. We only want
catalogue prices on there.

But then I don't know how to use that field in my query...

The more I think of it, the more it seams impossible...

I am still having a difficult time understanding exactly what the
business needs are. That being true, I can only do some guessing. Based
on that guessing, it sure sounds like you need to re-organize your data
design to "normalize" the data. This will seem to make it more complex at
first. However when it is done properly it makes everything else simpler
and many things possible that would not be possible is a simple one table
design.

This is the most important thing to lean in database design, it is also
maybe the most difficult as we don't tend to think this way, we tend to
think spreadsheet and Access is a relational database and it works far
different.

I believe the only way you are going to get a good working project is to
doing some reading up on relational table design and normalization, then
while forgetting everything you have done to this point, design a new
database following those principles and importing the data from your current
database.