how to mark some fields in a table

[Julie]

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.

 

[Julie]

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% ...

 

[Julie]

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]