Lookup Question

[Lookup]

Hi. I have four separate tables of product groups, i.e. Food Products, Non-Food Products, Meat, Produce. Each of these tables have a primary key that is a unique ID#. For instance, within the Food Products table this would be FP01, FP02, etc. In Meat would be M01, M02, etc. Each of these tables and products has a OurPrice field and a OurCost field.



We are using Quickbooks POS software, which exports our end-of-day report to an Excel file. This Excel file contains the ID# of each product (which matches our Access database) and the price of the product which matches the OurPrice field of our Access database tables. It does not contain the cost of the product, as the POS software is not capable of inputing this.

So, we import the end-of-day report to a new Access database table. What I need to know is how to use the Lookup function in this table to lookup the cost of each of the listed products from our other tables.

I've created a relationship between each of our tables with the ID#. Also, each of the tables has the ID# as the primary key.

I don't know where to go from here as each lookup only looks up from one table, not from multiple (do I need to do separate queries on each table???)...

Anyone have suggestions? Thanks for your time!

[June7]

Do the 4 product group tables have identical fields? If yes then should be one table. Would be much easier to relate data.

You have relationships between which tables - the 4 product group tables?

Why are you importing to a new table?

I can see two possible approaches with this data structure.

1. a query joining the 4 tables to the import table and an expression in the query to return price
Nz([Food Products].[ID], Nz([Non-Food Products].[ID], Nz(Meat.ID, Produce.ID)))

2. a DLookup with an IIf expression - never tried this in a query

[Rod]

What I need to know is how to use the Lookup function in this table ...

To be semantically pedantic, there is no such thing as a lookup function in a table. There is however provision to specify that fields of certain data types (text, number, etc.) are lookup fields. Much is written about lookup fields, all of it condemning their use. However they can be useful if used knowingly. Usually a 'lookup' is used to return multiple values for subsequent selection in a combo box or list box. (The detractors argue that this should be done in the form and not the table.) In your case I believe you are looking up a single value and I suspect you want this value to be inserted in your end-of-day table. Unfortunately a 'lookup' field does not do this for you: if you specify it as a text box you can't enter the lookup SQL; if you specify it as a combo or list box then you still have to take action to insert the value in the box, it does not happen automatically.

... as each lookup only looks up from one table, not from multiple ...

Oh yes it can, but that's somewhat irrelevant now.

---

The mere fact you are copying a single value from one place to another suggests your data design is not correct. You should have only one table for product and an attribute (maybe a foreign key) indicating type. You wouldn't design a database with separate tables for Britons, Americans, Canadians, etc. Anyway all is not lost but you've made it more complicated.

There are two situations - you choose which is best for you.

1. Run a routine on the end-of-day table that updates the cost field (this can be expressed as an SQL query); or
2. Base your daily reporting on an SQL query rather than the table itself.

You need to join your end-of-day table simultaneously and separately to each of your four product tables and then select the non null value for cost. Ughh!

[Rod]

June you leapt in ahead of me again! I don't think the DLookup will work as the Domain argument has to be a table name or a query name, unless the query is akin to my suggestion with a nested Nz function.

[June7]

Me first but you sure loaded up your post. You covered what I overlooked such as my suggestions would not save value. My thought on the DLookup was to do IIf on the domain argument, but can't remember ever trying something like that in query so was just a grasped straw.

[Lookup]

Okay. I had the four separate tables because it makes it easier for me to look at data, and yes, they do have identical fields, but if they all need to be in one table, that is easy to achieve. I serve a small nonprofit organization, and it's a one woman office, so time is extremely limited and I apologize that it took me to long to respond to your answer. I'll change into one table, and then get back to you. Since I'm not very experienced in Access a lot of what you and the other responder are saying is really going over my head, but let me take first things first, which is to have one large table rather than 4 small ones. (FYI, I am going to date myself here, but when Access first came out, it was recommended to have data in separate small tables versus one large one. I guess that guideline has gone out with time! I'll get back to you, and thanks so much for your time!

Do the 4 product group tables have identical fields? If yes then should be one table. Would be much easier to relate data.

You have relationships between which tables - the 4 product group tables?

Why are you importing to a new table?

I can see two possible approaches with this data structure.

1. a query joining the 4 tables to the import table and an expression in the query to return price
Nz([Food Products].[ID], Nz([Non-Food Products].[ID], Nz(Meat.ID, Produce.ID)))

2. a DLookup with an IIf expression - never tried this in a query

[Lookup]

Quite frankly, I have absolutely no idea what "semantically pedantic" means. I assume you are trying to be helpful, but don't really have any idea what you are trying to say. Thanks anyway!

[June7]

'Semantically pedantic' - refer to a dictionary.

No, 'normalization' is still valid concept in relational database design. But 4 identical tables does not conform to normalization.

Suggest you get a better understanding of data normalization (separate small tables versus one large one). It is not the number of records that determines data structure, it is data relationships. http://databases.about.com/od/specif...malization.htm

The one table isn't really larger - will have the same number of columns and maybe one additional for the food group attribute (Food Products, Non-Food Products, Meat, Produce).

[Lookup]

The reference to Semantically pedantic is to another poster, not to you, but thanks for the reference to a dictionary, never would have guessed that is where I should look - yes, that was sarcasm.

I thought this forum was for people to seek assistance, not for people with more experience to flout their expertise in front of novices and make what is obviously a difficult situation for a novice even more difficult. Obviously I was wrong.

I was referring to the amount of data in the table, not to the number of columns, etc. Really?

I'll hire a professional, and as for all of you "know-it-alls" - get a life.

'Semantically pedantic' - refer to a dictionary.

No, 'normalization' is still valid concept in relational database design. But 4 identical tables does not conform to normalization.

Suggest you get a better understanding of data normalization (separate small tables versus one large one). It is not the number of records that determines data structure, it is data relationships. http://databases.about.com/od/specif...malization.htm

The one table isn't really larger - will have the same number of columns and maybe one additional for the food group attribute (Food Products, Non-Food Products, Meat, Produce).

[June7]

Then what did you mean by '4 smaller tables' if not the reduction in number of records presented when viewing table? If all 4 have the same fields and therefore the same kind of data, then 4 tables unnecessarily complicates data structure (as you discovered in attempting to lookup related cost info). Combine the 4 into 1 table and use filtering to reduce the set of records presented for viewing.

Please be aware that we are volunteers (except for a few site staffers) offering suggestions as best we can, trying to share our experiences. Usually we hit the mark but sometimes not. Wish you the best in your efforts.

[ssanfu]

@Lookup:
Please don't take umbrage at the posts (comments). No offense was meant. The forum gets pretty dry... especially since typing cannot convey meaning that would be obvious if we were hearing voices. A lot of times, posters want someone to develop the dB for free - NOT saying that you do or were trying to get it done. Not defending anyone either.... but some people are a little terse at times and others use humor in their posts - I confess have committed both offenses.

I didn't know what "Semantically pedantic" either. And yes I did google it - before June7's post.

So now I'm going to date myself... I started out with databases in DOS using dBase II/III. A procedural program, I could make it do almost anything. By the time I had begun to understand Access (about a year), Access 2 was "dead" and Access 97 was in full swing. Switching to Access was like wading upstream - in the Mississippi river. What a learning curve!!

The beginning is the table structure - aka Normalization. A lot of time beginning posters will be pointed to
http://access.mvps.org/access/tencommandments.htm and http://access.mvps.org/access/lookupfields.htm
and sites about normalization.

I'll hire a professional, and as for all of you "know-it-alls" - get a life.

Hire someone if you can afford it. The dB will get done faster. But you might not learn as much. You will get help here.. the more specific you are, the better the answers.

This is a great forum. But there are others. "UtterAccess" is another forum to check out...
Good luck with your project...

[Rod]

To all,

Just to set the record straight I was calling myself semantically pedantic, no one else! The use of terms deteriorates over time until the original meaning is somewhat 'watered down' and it is then that confusion and misinterpretation set in. I try to use terms in their original definition which is hard to do when large organisations are themselves guilty of sloppy use.

As Steve has rightly pointed out it is not always possible to read posts in the tone intended by the author. Further this forum is international and idioms vary across the world. I tend to be a little verbose. Every one of the regular experienced posters here finds satisfaction in helping others overcome that learning curve with which in the past they too struggled; it is not their intention to offend; it is not their intention to sneer at someone less experienced, but we all face a difficulty in being expected to immediately assess the competence of the other posters and judge what tone of response would be best appreciated; we don't always get it right.