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]

Perhaps this would be easier to just use a macro?? Like a "case" statement? I could probably figure this out easier after it's exported into Excel, but it would be more efficient in the long run to have this done before the export.

 

[lespaul]

add the field SIZE(Letter Only) to the query.

then add:

INNER JOIN [Sizes Reference] ON Mid([OEORDLIN].[Item_no],10,3) = [Sizes Reference].[Size(Human Readable)]

you should get the information you need in the query now.

HTH

Leslie

 

[ngkatsaras]

Thanks Leslie, that looks like the solution I'm looking for! But I don't know how to add that.. Most likely I'm more of a newbie thank it seems.

Thank you so much!

 

[lespaul]

open the query, switch to SQL view (there are several ways to do this, the one I know is to press the speed button on the right hand side of the toolbar, you have the option of viewing the query in the grid, SQL or datagrid - search the help for "SQL View" and you'll find something!).

You will see the SQL that was generated by the query you built, you can modify it there.

If you need help with the modification, post the SQL here and I'll take a look!

les

 

[ngkatsaras]

SELECT CStr(Left([OEORDLIN].[Item_no],4)) AS STYLE, CStr(Mid([OEORDLIN].[Item_no],6,3)) AS COLOR, CStr(Mid([OEORDLIN].[Item_no],10,3)) AS [SIZE (Human Readable)], [Sizes Reference].[SIZE (Letter Only)], OEORDLIN.item_no
FROM OEORDLIN, [Sizes Reference];

I'm not sure where to put that in the SQL..

I tried this and got a syntax error

SELECT CStr(Left([OEORDLIN].[Item_no],4)) AS STYLE, CStr(Mid([OEORDLIN].[Item_no],6,3)) AS COLOR, CStr(Mid([OEORDLIN].[Item_no],10,3)) AS [SIZE (Human Readable)], [Sizes Reference].[SIZE (Letter Only)], OEORDLIN.item_no
FROM OEORDLIN, [Sizes Reference] INNER JOIN [Sizes Reference] ON Mid([OEORDLIN].[Item_no],10,3) = [Sizes Reference].[Size (Human Readable)];

Thank you so much!

 

[ngkatsaras]

I fixed the case of Size to SIZE and it still doesn't work..

 

[lespaul]

Try this:

SELECT CStr(Left([OEORDLIN].[Item_no],4)) AS STYLE, CStr(Mid([OEORDLIN].[Item_no],6,3)) AS COLOR, CStr(Mid([OEORDLIN].[Item_no],10,3)) AS [SIZE (Human Readable)], [Sizes Reference].[SIZE (Letter Only)], OEORDLIN.item_no
FROM OEORDLIN INNER JOIN [Sizes Reference] ON Mid([OEORDLIN].[Item_no],10,3) = [Sizes Reference].[Size (Human Readable)];

Leslie

 

[ngkatsaras]

Wow, thank you so much! That worked! Now for the hard part.. that was just a very simplified query that I thinned out from the real query I need.. How do I update this one?!

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, [Sizes Reference], [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
WHERE (((Right([ord_no],7))>=[STARTORDERNO] And (Right([ord_no],7))<=[ENDORDERNO]))
ORDER BY Right([ord_no],7), IMITMIDX.upc_cd;

 

[lespaul]

Ok:

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, [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 INNER JOIN [Sizes Reference] ON Mid([OEORDLIN].[Item_no],10,3) = [Sizes Reference].[Size (Human Readable)]
WHERE (((Right([ord_no],7))>=[STARTORDERNO] And (Right([ord_no],7))<=[ENDORDERNO]))
ORDER BY Right([ord_no],7), IMITMIDX.upc_cd;

that should do it!!!

Leslie

 

[ngkatsaras]

I really thought that was going to work.. it looked good to me! But I got this..

Syntax error (missing operator) in query expression '[Category/SubCategory].user_def_fld_2 = IMITMIDX.user_def_fld_2 INNER JOIN [Sizes Reference] ON Mid ([OEORDLIN].[Item_no].10,3) = [Sizes Reference].[Size (Human Readable)]'

 

[lespaul]

OK, try this instead:

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, [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 [Sizes Reference] ON Mid([OEORDLIN].[Item_no],10,3) = [Sizes Reference].[Size (Human Readable)] INNER JOIN IMITMIDX ON [CUSTOMER/ITEM/PRICE].ITEM_NO = IMITMIDX.item_no) ON [Category/SubCategory].user_def_fld_2 = IMITMIDX.user_def_fld_2 WHERE (((Right([ord_no],7))>=[STARTORDERNO] And (Right([ord_no],7))<=[ENDORDERNO]))
ORDER BY Right([ord_no],7), IMITMIDX.upc_cd;

OK, I think the problem may have been that in the first query we were working on the FROM clause included the OEORDLIN table; in this query it's not part of the FROM clause. I moved the INNER JOIN to right after where you join into the OEORDLIN table, maybe that will solve it.

les

 

[ngkatsaras]

I feel like we're really close here.. the first query was taken from the second query.. all I did was remove some columns from the first one!

I tried that last one, but now I'm getting this error:

Syntax error (missing operator) in query expression 'Mid ([OEORDLIN].[Item_no].10,3) = [Sizes Reference].[Size (Human Readable)] INNER JOIN IMITMIDX ON [CUSTOMER/ITEM/PRICE].ITEM_NO = IMITMIDX.item_no'

I wish I could just copy and past in the error box instead of having to type that all in!

Thank you so much, you're really a life saver!

 

[lespaul]

Ok, the way the second (major) query is structured is really different from the first query (since the FROM clause is SO different in the two!) Anyway, I'm not real proficient in Access SQL, it has a real particular way of adding parentheses to the JOIN clauses, and I can't figure out where our "new" join needs to go in the query for it to work correctly. Perhaps one of the real Access Gurus _{(i'm just a wanna be Access Guru!!)} can assist with the proper placement of the join and all the parens!!

Leslie

 

[ngkatsaras]

You have no idea how much I've learned today from all of this.. I've been reading that SQL code and looking at the GUI representation of the relations, I've been reading all about the structure of relational databases.. taking out columns to see how the actual SQL code changes, etc.. I think I need to do some more dissecting and trial and error and I'll get it.. but if anybody can help me solve this finally, I'd still really appreciate it!

Thanks again, Leslie!

 

[lespaul]

Ok, let's try to build it ourselves and let Access fix it the way it wants.

[Category/SubCategory]
Category
SubCategory
user_def_fld2

OEORDLIN
Item_no
qty_ordered
cust_id

[CUSTOMER/ITEM/PRICE]
Price
Cust_no
Item_no

IMITMIDX
upc_cd
Item_no
user_def_fld_2

[Sizes Reference]
SIZE(Letter Only)

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 [OEORDLIN]
INNER JOIN [CUSTOMER/ITEM/PRICE] ON [OEORDLIN].Cust_id = [CUSTOMER/ITEM/PRICE].Cust AND [OEORDLIN].Item_no = [CUSTOMER/ITEM/PRICE].item_no
INNER JOIN [Sizes Reference] ON Mid([OEORDLIN].[Item_no],10,3) = [Sizes Reference].[Size (Human Readable)] 
INNER JOIN IMITMIDX ON [CUSTOMER/ITEM/PRICE].ITEM_NO = IMITMIDX.item_no
INNER JOIN [Category/SubCategory].user_def_fld_2 = IMITMIDX.user_def_fld_2

Ok that's as far as I could get with out more information about the ColumnLabel table. not sure how that relates to the other tables. Why don't you remove the fields from the SELECT statement above that come from the ColumnLabel table and see if we are closer!!





Leslie

 

[ngkatsaras]

I have a table "ColumnLabel" with only one row of data!

ID Ticket/Label Label Name
1 Hang Ticket ONGOSSAMER

The reason for this is because that data never changes, but it needs to be in the exported Excel file. Perhaps there's a better way to accomplish this, but I got it to work.. In any case, I've removed those columns here if it makes it any easier! Also, I've removed Category/SubCategory

SELECT Right([ord_no],7) AS [ORDER#], CStr(Left([OEORDLIN].[Item_no],4)) AS STYLE, CStr(Mid([OEORDLIN].[Item_no],6,3)) AS COLOR, 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, [Sizes Reference], [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
WHERE (((Right([ord_no],7))>=[STARTORDERNO] And (Right([ord_no],7))<=[ENDORDERNO]))
ORDER BY Right([ord_no],7);

Here is the table "Category/SubCategory"

[tt]
ID user_def_fld_2 CATEGORY SUB-CATEGORY
1 B Intimate Apparel Bras
2 C All
3 P All
4 O All
[/tt]
For our UPC ticket printer, Bra's (B) are considered Intimate Apparel, but Camisols (C), Panties (P), and Other (O) are all considered All, with no Sub-Category.. I have no idea how I actually got that part to work, but I guess I just got lucky.

 

[ngkatsaras]

Sorry.. this is with Category/SubCategory removed

SELECT Right([ord_no],7) AS [ORDER#], CStr(Left([OEORDLIN].[Item_no],4)) AS STYLE, CStr(Mid([OEORDLIN].[Item_no],6,3)) AS COLOR, 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 [Sizes Reference], ([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
WHERE (((Right([ord_no],7))>=[STARTORDERNO] And (Right([ord_no],7))<=[ENDORDERNO]))
ORDER BY Right([ord_no],7);

 

[ngkatsaras]

I got this to work!! Notice the parenthesis, I just went crazy adding parenthesis, cutting and pasting, removing and adding things until I got something to work.. now that I see something that works, I can look at it and understand it.. from here, I should be able to update the original SQL!

SELECT Right([ord_no],7) AS [ORDER#], OEORDLIN.item_no, CStr(Mid([OEORDLIN].[Item_no],10,3)) AS [SIZE (Human Readable)], [CUSTOMER/ITEM/PRICE].PRICE, OEORDLIN.qty_ordered AS QTY, [Sizes Reference].[SIZE (Letter Only)] AS Expr1
FROM ((([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) INNER JOIN [Sizes Reference] ON Mid([OEORDLIN].[Item_no],10,3) = [Sizes Reference].[Size (Human Readable)])
WHERE (((Right([ord_no],7))>=[STARTORDERNO] And (Right([ord_no],7))<=[ENDORDERNO]))
ORDER BY Right([ord_no],7);

 

[lespaul]

good for you! glad you figured it out!

If you need more help, I'll be around!

les