Report to show Sizes Accross

[Hugo]

Hi there, I hope sombody will be able to help me figure out a way to do the following:

I am extracting data from a Visual Fox pro database program and I have link the individual tables in Access 2007. For example I have table Receiving with the following fields:
Purchased Date, Style, Color, Scale, QTY0, QTY1, QT2, QTY3 (here the QTY represent a specific size according to the style, not all style will have QTY0-3)

I also have another table called 'Scale' with the following fields:
Scale, Size0, Size1, Size2, Size3 (here the actual sizes by product are shown, eg: S, M, L, XL, etc)

I want to build a query or a report that will show me each style grouped by the actual size (S, M, L, XL). Currently I get the query or report but it shows QTY0, QTY1,etc and not everyone understand that QTY0 could means 'S' or '32' or 'S' according to the scale.

Can anybody help figure out how to achieve this?

 

[Trevor G]

Hugo,

Look to create a query and use an iif statement to set the size see example below:

Size:IIF([Field Name]="Qty0","S",IIF([Field Name]="Qty1","M",IIF([Field Name]="qty2","L",IIF[Field Name]="qty3,"XL","")))

 

[Hugo]

Thank You Trevor, I will give this a try!!


Hugo

 

[Trevor G]

Hugo if the iif statement doesn't work for you then below is another option, you would add a VBA Module (Alt + F11, then Insert Menu and Module) and use the code below. What you would need to do is add a field to your table in design vew, something like NewSize.

Add this function into the module and save the module, you would look to replace the Combined table name I am using here to yours. Place in the field name in the square brackets, replace the content you want wehre I have marked it in red, then again in the final part you would place in the current field name and the contents to check for as indicated in the final red colour.

You can then run this and then make further functions and run them with the other content, and the new field will be populated.

Function AddSize()

Dim SQL As String

SQL = "UPDATE Combined " 'Replace Combine with your table name
SQL = SQL & "SET Combined.[Add your field name here]="Small" 'The field and content you want goes here
SQL = SQL & WHERE combined.[The field to check]='qty1'" 'The field and the current content goes here

DoCmd.RunSQL SQL

End Function