Declared Values

[Kelly]

Help, I'm a rusty MS Access hacker and need some help designing a
database for multiple users and branches.

I would like a User to enter their Name and Branch in order for
information in tables, queries, and reports to do calculations based on
this information and am not sure where to even start.

The hiccup in my thinking was when I went to reference the branch
information and another numerical value related to that specific branch
as part of the calculation, that's where I got lost. I want it to be
transparent to the user and I don't want to do a lot of programming if
more branches are added or deleted down the road.

I sure hope this makes sense as I definitely know what I want.... Any
help that could be provided would be GREATLY appreciated.

 

[John W. Vinson/MVP]

Please give us a bit of information about the structure of your tables. You
can see them, we cannot! I suspect that a Query joining the Branch table
will be the solution, or perhaps a DLookUp, but based on what you've posted
I have no way to guess how.

 

[Kelly]

Please give us a bit of information about the structure of your tables. You
can see them, we cannot! I suspect that a Query joining the Branch table
will be the solution, or perhaps a DLookUp, but based on what you've posted
I have no way to guess how.

I only have a few tables in my mind and they are below. I am developing
a brand new database.

Static Variables Table
Today's Date
User Name
Branch Name

Branch Information Table
% Markup
Address
City
Phone Nbr
etc.

I am not familiar with DLookUp, however, I am familiar with doing a
Query joining different tables. Based on my understanding of DLookUp,
you have to hardcode the values into the database and at some point the
owner of this database will either add a new branch or sell a branch so
I don't want to have to go back in, to hard code data.

The other thing is the % Markup for the branch could change depending
on their costs to ship products wherever so I want the number used in
the original calculations to remain and not change when the value in
the Branch Information table changes.

I hope this helps, Kelly

 

[John Vinson]

I only have a few tables in my mind and they are below. I am developing
a brand new database.

Static Variables Table
Today's Date
User Name
Branch Name

Branch Information Table
% Markup
Address
City
Phone Nbr
etc.

Surely you have the branch name in the branch information table?

I'd strongly suggest using a naming convention in which you avoid
blanks or punctuation in fieldnames: apostrophes (Today's Date) *will*
cause problems! I'd also avoid using the branch name as a key, since
the name of the branch might change.

I'd suggest

StaticVariables
TransactionDate' don't use Date as a fieldname: reserved word
UserName ' or better link to a Users table
BranchID ' long integer to link to the BranchInfo primary key

BranchInfo
BranchID Autonumber Primary Key
BranchName
PctMarkup
Address
City
PostCode
Phone


John W. Vinson[MVP]

 

[Kelly]

Surely you have the branch name in the branch information table?

Agreed. I forget that with programmers you have to be specific. I have
more of a business analyst/project manager background and am familiar
with tables that identify unique information. I do group information
together because that is what I want to end up with but when I get down
to business I separate the unique information with their own tables.
Sometimes, though, my data and naming convention I leave a little bit
to be desired as you have identified below with one field that I hadn't
through. I takes me awhile but I eventually get it. Thanks!

I'd strongly suggest using a naming convention in which you avoid
blanks or punctuation in fieldnames: apostrophes (Today's Date) *will*
cause problems!

Agreed.

I'd also avoid using the branch name as a key, since

the name of the branch might change. Agreed.

I'd suggest

StaticVariables
TransactionDate' don't use Date as a fieldname: reserved word

I hadn't quite figured out that it is the transaction date that I was
after. Thanks for that!

UserName ' or better link to a Users table Agreed.

BranchID ' long integer to link to the BranchInfo primary key Agreed.

BranchInfo
BranchID Autonumber Primary Key
BranchName
PctMarkup
Address
City
PostCode
Phone

Agreed.

One question that you didn't clarify and I think where I am not really
not sure just because of my hacker backgroup. Is it a DLookup field
that I am after or a Query Join which is what you called it. Was my
definition of the DLookup field in a previous reply correct or am I
missing something?

Thanks for your help John, I really do appreciate it. As I said I am
rusty as its been probably 3 years since I last developed a database.
Okay, that was all I was going to ask but I do have one more question
what does MVP mean on your signature?

Cheers, Kelly

 

[John Vinson]

One question that you didn't clarify and I think where I am not really
not sure just because of my hacker backgroup. Is it a DLookup field
that I am after or a Query Join which is what you called it. Was my
definition of the DLookup field in a previous reply correct or am I
missing something?

DLookUp isn't a field. It's a VBA function which you can call from a
query (or from other places), to "look up" the value of a field from
another Table or Query:

DLookUp("ThisField", "SomeTable", "IDField = 34")

will search the table SomeTable for the first record it finds with
IDfield equal to 34, and then the function will return whatever is in
the field ThisField in the record that it finds.

You can use DLookUp as a "calculated field" in a query, and it's
occasionally appropriate to do so, but in general it's better to use a
Query joining the two tables - much more efficient!

Thanks for your help John, I really do appreciate it. As I said I am
rusty as its been probably 3 years since I last developed a database.
Okay, that was all I was going to ask but I do have one more question
what does MVP mean on your signature?

Most Valuable Professional, a generous recognition by Microsoft of my
(and many many other folks!) volunteer efforts in these forums. See

http://mvp.support.microsoft.com/default.aspx

for some background.

John W. Vinson[MVP]

 

[Kelly]

DLookUp isn't a field. It's a VBA function which you can call from a
query (or from other places), to "look up" the value of a field from
another Table or Query: Thanks.

You can use DLookUp as a "calculated field" in a query, and it'suse a
Query joining the two tables - much more efficient!

Thanks. I thought there might be a more proper way. I know how to do
this.

Most Valuable Professional, a generous recognition by Microsoft of my
(and many many other folks!) volunteer efforts in these forums. See

Wow, you're fast at responding to my crazy questions. I just did a
search on MVP and no sooner did I read about it than you had already
responded. Thanks for everything.

Now, for the last question, at least for now.....tee hee

Once I populate the Static Variables table that will identify the
branch information it should then enter the default Customer - Address,
City, Province, Postal Code, and Phone Number fields. I had planned on
doing this with 3 different forms (one for each of the current
branches) but then I thought what if another branch is added or
removed, I don't want to have to create different forms to enter client
information. Surely there is another way that would be better and would
only use one form. Is there? If this doesn't make sense, I'll try to
explain it another way.

I am so happy that I discovered this forum. If only I had known about
it 5 years ago it would have helped me a LOT. Anyhow, you're helping me
get up to speed again and I REALLY do appreciate it.

Thanks again John, Kelly