A General Question about Logical IIF Statement

[NewYorkFashionToGo]

I do understand how the function works, It is simple.... If the value is not this then it then it is that, There is 2 possible answers that can be pulled from 2 different locations, My question is How do you pull more possible answers from even more locations, To merge more than 2 queries and/or tables into One column in One query. Is there a different function that can achieve this.

I am building a database that writes HTML code, It is very long and crazy. I have Different templates for HTML products (Each template is slightly different. It was easier for me to seperate it to avoid the confusion of putting it back together again for each product. I would like to combine the finished HTML code of all products Into One Master list, For all products. I can easily do it with two, But How would I get

 

[lespaul]

can you provide some example data of what you are trying to accomplish?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[NewYorkFashionToGo]

Right now everything is open, I do not want to make the mistake of setting it up improperly and having to do it all over again.

The best way I can explain what I am trying to achieve is with an excel formula (because I know it works) I am trying to create something similar as this:

=IF(A4="Jeans",Sheet2!a1,IF(A4="Shoes",Sheet2!b1, IF(A4="Jewlery",sheet2!c1,IF(A4="Lingerie",sheet2!d1,"No Product"))))

Or mabee easier to understand with example that excel help menu supplies:

This example is for a teacher assigning Grades:
=IF(A4>89,"A",IF(A4>79,"B", IF(A4>69,"C",IF(A4>59,"D","F")))) Assigns a letter grade to the third score (C)


I have a column in access that Just says what the product is: Example( Jeans, Jewlery, Shirts/Tops,Shoes, ect ect....

If That column says Shoes. I want to Pull The finished HTML template for shoes, If it says Jeans I want to Pull the finished HTML template for Jeans and so On...

I tried writting an expression as the excel example above, To try to pull the information to get it all on one master sheet..... It was just easier to write the HTML code query By itself for each product.. It was very Long and would be a nightmare to do trying to write an expression combining all different products. So I seperated it, Now I want to take finished results from several queries and make one master product sheet with finished html pulled from several locations.

I hope you understand what I am trying to achieve. Its hard to type it to show you......

 

[PHV]

Perhaps an UNION query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NewYorkFashionToGo]

I didnt try a Union Query Yet, I am Far better in excel formulas than Expression and access, It is a new program for me and still learning. I will try it. See if I can figure it out.

 

[jsteph]

Maybe I missed something, but why not a simple table with 2 fields?

Field1 has values like "Shoes","Jeans", etc., and field2 has the HTML for field1. Field2 could be a Memo field if the html is more than 255 char.
--Jim

 

[NewYorkFashionToGo]

Yes you are right, That is exactly what I am trying to create, I have several queries already and that will be the final result. The queries build the HTML Code for me, I just enter in Basic specifications. Each products template is slighly different, and it was very long to create so I have several different queries (for each template), I am trying to merge all of them now onto one query to create exactly what you said.

 

[jsteph]

I'm still unclear. These queries you're creating for each product--do they exist to output a final html page?

Or do they exist simply to get your project started and load a table (the 2-field table I talked about), so that in production you can just do simple querys on this table?--Jim

 

[NewYorkFashionToGo]

For Each "Type of Product" has its own query which creates the finished HTML, I have several of them One query for each "type of product". Now I would like to take the finished HTML from each Type of Products query. And create a new query or append to a table for a master List. I need to have a primary Key on the finished page or come up with a way to create a new query combining Just the finished HTML column. Just having little difficulty because there are so many relationships involved with creating the HTML. Now it is mixing all of them together and I am getting undesirable results because of it.

 

[jsteph]

Ok, off the bat, you could do as PHV said and make a giant Union query, making sure each query you Union has the same # fields and in the same order.

But where are these querys getting their data in the first place? Is it from some other table, and if so, what's in that table?

For example, in your excel example:
=IF(A4="Jeans",Sheet2!a1,IF(A4="Shoes",Sheet2!b1, IF(A4="Jewlery",sheet2!c1,IF(A4="Lingerie",sheet2!d1,"No Product"))))

What I would envision here is a table with the values:

ProdType  | HtmlCode
----------------------------------------
Jeans       <html>blah jeans blah contents of sheet2!a1 blah </html>
Shoes       <html>blah shoes blah contents of sheet2!b1 </html>
Jewelry     <html>blah jewelry blah contents of sheet2!C1  blah</html>

Then your query would link the prodtype to wherever you get the A4 in your example.
--Jim

 

[NewYorkFashionToGo]

I am looking into the union queries, I havent tried it just yet, I been working on a different query related to everything (the next step) I have had success using the append query. I was having some trouble with the text parsing at 255 characters. I think I may have solved it, I am trying to incorporate the next step , Just to see how it reacts and make some adjustments.

To answer your question about where the data is coming from.
I have several Excel XLS Workbooks using macros and VBA,....... again one for each Type of product. I find I can put it together faster in excel then Import it, dragging and copying from excel .

The reason why I do it this way, is alot of the products are similar. Usually except for sizes or color. Example Blue Shirt|3333| Medium| 9.99 Blue Shirt|3333| Large|9.99 But I dont Just sell shirt, Other products two, that is why I need the different sheets.They have different specifications altogether.

I just enter in specs of Items , sizes where I store it,What I paid for it, manufactures ID. When I pump it into ACCESS run few queries it puts part numbers together and develops the HTML code based on what I enter In the appropriate place. And set up for accounting already. I am rather new to all the encoding and especially new to access, Still have alot to learn with expressions and SQL.

I appreciate your taking the time to help, Thank you for that.

 

[lespaul]

Have you read the fundamentals document linked below? Having tables that meet these rules is essential in being able to easily extract the information you need.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[jsteph]

I sort of see where you're coming from: It appears you are much more comfortable with Excel, and would like to leverage Access' capabilities into your process, while maintaining the core data store in Excel.

While this is possible, in my opinion you'd be better off in the long run to move the data to Access, and build some data-entry forms for working with the data. (SQL-server would also be a good choice for the backend, but that's another discussion; Access should suffice here, if for nothing else then to keep the discussion from clouding.)

Anyway, generating html from database a table is standard fare--but I personally wouldn't want to have Excel complicating the flow. However, as an intermediate step if you want to keep your data-entry/edit in Excel and manually dump into Access, then you'd really just need to set up a Products table, this time with more than just the two fields I'd mentioned.

There are a couple ways to go here--one is to treat it as a standard database table, and build a simple procedure which generates html based on the data in the table. The other is to store 'pre-generated' html in the table. I prefer the former, since it's more flexible in my opinion.

Let me know if I'm understanding your situation more clearly, and if this is the path you wish to take we can certainly help more,
--Jim