Bra and Panty sizes database question 1

[ngkatsaras]

I'm hoping that subject will be catchy! But honestly, this is a database for a women's lingerie manufacturer.

I need to automate our UPC ticket printing order form, we've been doing this manually and it's something that has been taking up too much time doing it manually!

In our database, each item has an Item_no

Here are some examples:
Bra
4901-BLK-36D

Panty
4534-RED-S/M

The first part of the Item_no is the style, color, then size, we break this up in a query as follows

|STYLE: CStr(Left([OEORDLIN].[Item_no],4)) | COLOR: CStr(Mid([OEORDLIN].[Item_no],6,3)) | SIZE (Human Readable): CStr(Mid([OEORDLIN].[Item_no],10,3))

I need to add a column that will convert the size from 32A to A, L to Large, M/L to Medium, etc.. as follows:

ID SIZE (Human Readable) SIZE (Letter Only)
1 32A A
2 32B B
3 32C C
4 32D D
5 34A A
6 34B B
7 34C C
8 34D D
9 36A A
10 36B B
11 36C C
12 36D D
13 38A A
14 38B B
15 38C C
16 38D D
17 L Large
18 M Medium
19 M/L Medium
20 ONE ONE
21 S Small
22 S/M Small

I've created the above table in access named "Sizes Reference".
I know that the column headings may seem someone confusing, but this query is exported as an excel file to be imported into the label printer's system, so the column headers need to be what they are.

I can't update the original SQL database, which is why I created the table in access. I couldn't figure out how to create a relationship with the Size (Human Readable) in the query and the same column in my "conversion" table.

I can clarify this further, but I wanted to get this much online for the time being.

I really appreciate any help.

Thanks!

Nick Katsaras

 

[ngkatsaras]

I had too many parentheses before (one extra set).
All I had to do was put a parentheses where highlighted, remove the red and add the blue.

Here is the finished, working, code:

SELECT Right([ord_no],7) AS [ORDER#], ColumnLabel.[Ticket/Label], ColumnLabel.[Label Name], [Category/SubCategory].CATEGORY, [Category/SubCategory].[SUB-CATEGORY], CStr(Left([OEORDLIN].[Item_no],4)) AS STYLE, CStr(Mid([OEORDLIN].[Item_no],6,3)) AS COLOR, IMITMIDX.upc_cd AS UPC, CStr(Mid([OEORDLIN].[Item_no],10,3)) AS [SIZE (Human Readable)], [Sizes Reference].[SIZE (Letter Only)], [CUSTOMER/ITEM/PRICE].PRICE, OEORDLIN.qty_ordered AS QTY
FROM ColumnLabel,[COLOR=red][Sizes Reference],[/color] [highlight]([/highlight][Category/SubCategory] INNER JOIN (([CUSTOMER/ITEM/PRICE] INNER JOIN OEORDLIN ON ([CUSTOMER/ITEM/PRICE].CUST = OEORDLIN.cus_no) AND ([CUSTOMER/ITEM/PRICE].ITEM_NO = OEORDLIN.item_no)) INNER JOIN IMITMIDX ON [CUSTOMER/ITEM/PRICE].ITEM_NO = IMITMIDX.item_no) ON [Category/SubCategory].user_def_fld_2 = IMITMIDX.user_def_fld_2[highlight])[/highlight][COLOR=blue] INNER JOIN [Sizes Reference] ON Mid([OEORDLIN].[Item_no],10,3) = [Sizes Reference].[Size (Human Readable)][/color]
WHERE (((Right([ord_no],7))>=[STARTORDERNO] And (Right([ord_no],7))<=[ENDORDERNO]))
ORDER BY Right([ord_no],7), IMITMIDX.upc_cd;

There is one odd thing.. when I try to view this in design view I get an error that it can't represent it. It's too bad that this SQL couldn't have been generated using the gui. I'm glad I learned some SQL, but it seems that Access could be updated to support this easier.

I'm new to tek-tips, as a member.. but I've been finding answers via google for at least a couple of years.. I really love it! Thank you so much Leslie. And thank you tek-tips!! I learned a whole lot!

Sincerely,

Nick Katsaras

 

[lespaul]

Unfortunately, a lot of SQL can't be represented graphically, especially when you start joining on partial fields. However, it's a great way to learn SQL and not have to depend on the QDE (query design editor). Once you know enough to write your own SQL, you can just write it in the SQL view and Access will add all the ()() it needs.

Tek-Tips is a GREAT place. I thought I knew alot of SQL when I started hanging out here, but I can still learn something new everyday!!

Here's a link to a great article that helps explain how joins work and when to use what kind: 'Understanding SQL Joins'.

Have a great day!

leslie