CASE/SWITCH SQL in VBA

[Runawaygeek]

Hi ,

In MYSQL, i can use the CASE statement to take 1 column from a table and generate 2 new columns with new data based on that info.

Eg. TBL1.Bike

BIKE
Yammaha R1
Kawasaki H2
Suzuki GSX
Honda FireBlade

I can then CASE this info, with something like

Select Name
, CASE BIKE
WHEN 'Yammaha R1' THEN 'SUPER SPORTS'
WHEN 'Kawasaki H2' THEN 'CRAZY FAST'
ELSE 'Boring'
END AS SPEED_TYPE

, CASE BIKE 
  WHEN 'Yammaha R1' THEN 'Bridestone'
WHEN 'Kawasaki H2' THEN 'Conti'
ELSE 'Michers'
END AS RUBBER

FROM etc..

So how can i do this in SQL with in Access?
Someone mentioned SWITCH, but i cant see how i get SWITCH into the SQL query and then how i name a new column to contain that data under the INSERT TABLE???

Help!
:banghead:

 

[Uncle Gizmo]

I've got a rough idea of what you want, but demonstrating your answer in MySQL code isn't helping. I suggest you provide an example of the data structure you have, and the data structure you want as text. I might be able to see better what you want then.

 

[pbaldy]

I would argue that you shouldn't do what you're doing. You're hard-coding associations that in my view should be in a table. What happens when you add a new bike model? You'll have to find all the places in VBA or SQL where you have this type of thing and add another case. I'd have a form where the users add the bike model to a table with its associated value(s). Simple things like new models shouldn't require design changes.

 

[Runawaygeek]

Hi Pbaldy,

While you would be right given my example, this is part of a wider workflow i am trying to put together. Now Access might not be the right tool for this but....

I have a table in an access database that is a full history of my depts output.
Every day, we get sent 6 new excel files of requests.
These Excel files are automated output from a wider Oracle system, that would cost to develop should i want changes to the data output.

I need to pick up these 6 files, add them to a temp table, compare them to our archive table, compare them further to a live working Excel file (which i am importing to another temp table) then produce an output that's relevant to our systems. (which you helped with the other day!)

Are you suggesting that i create a "look up table" where by, users can add new BIKES, and info about that bike. which i can then reference, when doing my comparison and output the other columns?

If tabl1.col1 = tabl2.col1 THEN tabl2.col2
??

 

[CJ_London]

enterprise scale systems typically output data in flat file format (and usually missing key fields) with the expectation of it being used in Excel or similar based on the descriptions instead.

Access is not excel, so the fact you receive data as a flat file is no reason you need to store it the same way - you might as well use Excel.

If you are going the Access route, you need to break the file into its component parts and import to several tables in Access so Access is doing the job it was intended for.

No idea what your data looks like, but the way this is normally done is to link to the file (or import to a temporary table) then run several append queries to populate the various tables. All this can be automated. You may need to create your own primary and foreign keys along the way.

 

[Runawaygeek]

@Uncle Gizmo

Thankyou for getting back to me,

so, my data is actually an oracle generated Excel file. that contains a column call called Channel.

From this i need to produce the additional columns of Branding and Service.
These are currently "known" things, as in a human knows them..

So for example, If the channel is TLC Denmark. The Branding will be TLC and the Service will be 'IPTV Denmark'

Pbaldy, i think, is suggesting a different approach, one where the user could update a reference table should channels or brands change etc.. which is not a bad idea.

Thoughts?

Thank you for your help.

 

[Runawaygeek]

@CJ_London

Thanks, i am doing that, reasons we cant store in Excel are manily data size, Excel just crashes out about 65k rows.. thats about 3 months work for us.

Long term we are looking for an SQL server based solution, but no one actually spends money in business soo....

I am importing all the Excel files to a temp table, I then have a query to take key info. I dont know how to get a query to MAKE new info based on information within a cell?

thanks

 

[CJ_London]

you are not making data, just storing it 'db style'

lets say your excel file contains data about products and sales - so in principle you need a table for each of these

1. link to the file
for each table (which should have appropriate PK's)
2. run an update query by matching on a suitable key field - might be bike description - this will update changes to the product (except description)
3. run an append query, left joining import data to stored table on same field and setting criteria = null for stored table - this will add new items
4. possible run a delete query (or better to update a 'deleted' flag) by left joining stored table to import table where import is null

the second import would link on description to the first table to pick up the appropriate PK as a FK.

In this way, you are storing data once and recreating something similar to that which is in Oracle and can then use 'proper' db methodology.

A side benefit is that having your data stored properly will make a transition to SQL server simpler and cheaper.

 

[Runawaygeek]

@CJ_London, Thanks for that, i will give that go now.

 

[pbaldy]

Are you suggesting that i create a "look up table" where by, users can add new BIKES, and info about that bike. which i can then reference, when doing my comparison and output the other columns?

If tabl1.col1 = tabl2.col1 THEN tabl2.col2
??

I am suggesting a lookup table, but you wouldn't have a formula like that, you'd join to the new table in a query.

 

[Runawaygeek]

Thanks, I am just not used to working with Access.

So, i have started using the Queries to move data about and on the whole it works.

When looking at True/False flag, its returning duplicate rows to count all 90K records from one of the tables. But i just want a Yes or no against the ID within that row. Not loads of duplicates

I have used an Iif in the Query Expression Builder iif(ID=History.ID,1,0)
I am pulling 37 rows from an imported Excel to a TEMP_Table. I just want to know if they exist on another table, then if they do, do Conditions A and B also match? Should i nest the iif?

 

[Minty]

You should be able to do a left join on the ID and set the target field criteria to Null

 

[Runawaygeek]

You should be able to do a left join on the ID and set the target field criteria to Null

If i do that, then i cant apply further conditions.. SUch as,

Does the ID exists on both tables

Yes

Does the Product Exist on both tables

No

THEN Action ELSE no action..

Does that make sense?

I just need it to flag? not filter


*Update* - I think i need to use IsArray, but i am not sure how to define that in Expression Builder