How to specfy ALL in a list. (1 Viewer)

[dz2k7]

I have two tables.

Main Table
Branch, ProductGroup, Product

Table of Parameters
Branch, Product Group, Parameter

I need to make report
Branch, ProductGroup, Product, Parameter

Now.
The manager can give directions in three ways

1. Make parameters such and such for these branches
2. Make parameters such and such for these ProductGroups
3. Make parameters such and such for these ProductGroups in these branches only.

Lets assume those directives never overlap.

I don't want to make 3 adjustment tables and three queries to manage process.
I want one adjustment table.

There is no problem with #3, but there is a problem with # 1 and #2.

The only thing I don't know is:
What should I put in parameter table if I say something like
"For ALL ProductGroups of this branch", or
"For this ProductGroup in ANY Branch"

I tried using Asterisk - it does not work.

Any idea?

 

[John Big Booty]

.......

I tried using Asterisk - it does not work.

Any idea?

Try;

Like "*"

as your criteria

 

[dz2k7]

I can't do that as a criteria.
I need it in adjustment table as data entered.

 

[John Big Booty]

Sorry in that case, I seem to have complete misunderstood your intent

Perhaps you could explain once again; slowly for the hard of reading a sample of the data might help too.

 

[dz2k7]

I'll give you another example.

Let's say I have a form where I specify a branch I'm looking at.

So I have a query where critreia for the branch is like
[forms]![form1]![Branch]

Now
Let's say I want to see data for all branches not changing anything in queries.
I only want to enter something tricky in this form but not branch name when I run it.
Something like * or "all" or anything from hell.

Leaving it empty does not help as I figured.

What should I put there?

 

[John Big Booty]

OK I think I see what you are after.

I'll presume that you have a combo box to select you Branch from. So as part of the row source for this combo you might have a record "Select all Branches", you would assign this record an ID that will be outside the normal range of ID's say 99 for example, then when that is select in the combo, and you run you query, as part of that queries criteria for branch you could put something like;

Iif(Froms!FRM_Name!ComboName = 99, Like "*", Froms!FRM_Name!ComboName)

I'm not 100% on the Like portion of the statement, it may need to enclose the whole Iif statement

Like (Iif(Froms!FRM_Name!ComboName = 99, "*", Froms!FRM_Name!ComboName))

 

[DCrake]

Iif([B][COLOR="Red"]Froms[/COLOR][/B]!FRM_Name!ComboName = 99, Like "*", [B][COLOR="red"]Froms[/COLOR][/B]!FRM_Name!ComboName)

Dyslexia rules KO

 

[John Big Booty]

A fingergraphical error

 

[dz2k7]

I got the concept!

I put
Like (IIf([Forms]![form1]![Branch]="~*","*",[Forms]![form1]![Branch]))

And it works when i put * in a form.

Thanks a lot, I guess i'll figure out how to treet those tables in a beginning.

 

[John Big Booty]

Good work

 

[dz2k7]

Does not work with table Join though.

 

[John Big Booty]

Does not work with table Joint though.

Sorry I can't comment further without seeing you table and query structure.

 

[dz2k7]

Main
Branch, PGSG, Product

Parameters
Branch, PGSG, Parameter



Query
SELECT Main.Branch, Main.PGSG, Main.Product, Parameters.Parameter
FROM Main INNER JOIN [Parameters] ON (Main.PGSG = Parameters.PGSG) AND (Main.Branch = Parameters.Branch);

 

[John Big Booty]

Any chance of posting a copy of your DB?

 

[dz2k7]

I Uploaded the DB.
Temp.mdb

Product group HD50 is missing and i tried to make parameter 10 for this PGSG in all branches.

 

[John Big Booty]

OK I've had a look at the DB and it would seem not to be properly normalised.

Could you please run me through what each of the fields represent, and most importantly how they relate, so I can get a better handle on how this should be set up?

 

[gemma-the-husky]

assumimg you are using a parameter you will need an "or" in there

so effectively

select * from sometable where SomefFeld = somevalue OR SomeExternalVariable = true

 

[dz2k7]

you're right. Our system never been normalized. But we can't change that. We just download what is there and crunch numbers as good as we can.

Ok
WXP is a branch name (I picked 3 branches only WXP, WXQ and WXY
WX part is a division name
The system is partually normalizad within each division, so i picked just 3 branhces from the same division to make an example of DB.
All together i have about 250 branches in 9 divisions and about 2 million lines in main table (don't tell me to set up SQL Server - my bosses do not want that).

Item - is just a product code that product file group sets up.
Description is not really important.

PGSG - ProductGroup/SubGroup - it's part of product file setup too.
I only picked 4 different PGSG. I have about thousand of them.

There are much more columns in that table but they are irrelevant.

Now i need to manage calculations of "suggestion to buy" for replenishment group.
So I can increase or decrease what we call Min-Max that generate ordering.

So I'm using a setup table organized by branch and PGSG.

In the end I need to have a parameter for each item in each branch to upload in a system itself.

PGSG list and Branch list might change every day and i might not know about it.
Still I need some PGSG to be on in this case +10, so orderd qtys will be higher.
So I'm trying to force the system to react on task like
Do all products of HD50 +10.

So I need to say ALL branches in a table of parameters.
Instead of listing all PGSG of all branches in it.

 

[John Big Booty]

Just because you are receiving data from an external source doesn't mean it can't be normalised.

The first step would be to receive that data into a temporary file, and from there manipulate it into a normalised form, using a combination of Matched Queries and Unmatched Queries, before inserting it into your tables.

Have a look at the example I posted in this thread.

In the mean time I'll have a look at your sample and see what I can do.

 

[John Big Booty]

How does the PGSG relate to the Item? I'm presuming that each Item will appear in only one PGSG, is this correct?