Trying to table data

[sflearn]

I have to Table large amounts of Data.

I get two inventory lists twice a week from 16 stores.
I sort this data in two ways.

I do this by comparing an inventory list to a list of 300 of the best selling styles.

Currently, I input one list of inventory data in excel and then organize the items respectively to a sales data list in columns. Top 25 styles, 50, 100, 200, 300.

When I am finished, I have a certain amount of items in the top 25 styles, a certain amount in top 50, and so on. I color code the columns and write down the quantities and percentage of each category in the list..

I have been trying to create relationships that would sort the data in Access and in Filemaker pro.

So far, I have only been able to organize data in groups but outside of the format I am trying to get.

[MMcCarthy]

I have to Table large amounts of Data.

I get two inventory lists twice a week from 16 stores.
I sort this data in two ways.

I do this by comparing an inventory list to a list of 300 of the best selling styles.

Currently, I input one list of inventory data in excel and then organize the items respectively to a sales data list in columns. Top 25 styles, 50, 100, 200, 300.

When I am finished, I have a certain amount of items in the top 25 styles, a certain amount in top 50, and so on. I color code the columns and write down the quantities and percentage of each category in the list..

I have been trying to create relationships that would sort the data in Access and in Filemaker pro.

So far, I have only been able to organize data in groups but outside of the format I am trying to get.

Have a look at this tutorial and see if it helps.

Normalisation and Table structures

Mary

[sflearn]

that made sense...

so It seems like I have a many to many relationship.

I thought I had a 1 to many relationship.

The sales data is in desc1 which is the style code (text and number... will this be a problem?), desc2 (the full description of item, I didnt use it when I made relationships), and 'rank' which was a level from 1-300.

I 'joined' the 'rank' column to the top 25, 50, 100, 200, and top 300 columns in the relationship database.

I also joined 'styles' in the inventory data and joined it to desc1.

So what does this do to my data?

It seemed I was only able to make the ranks line up on the side of the inventory items, not fill in the boxes in the columns I labeled top 25, top 50, top 100, top 200, top 300.

so... this is where I am stuck.
I appreciate your help.

[MMcCarthy]

In a many to many relationship you would normally have a join table which would have the primary keys of the other two tables. I'm having problems following your structure. Can you post the metadata of the two tables involved.

Here is an example of how to post table MetaData :
Table Name=tblBookings

Field; Type; IndexInfo
 Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Mary

[sflearn]

ill get you the metadata right now. I never accessed it and will try now.

but first a little about the structure.

item | styles | description | color | size Desc1 | Desc2 | Rank
19875 2001 t shirt black XL 2001 t shirt 1
19874 2001 t shirt black L RS32 legging 2
19873 2001 t shirt black m S11 dress 3
10031 RS32 Leggings Green XS 2455 shorts 4
10051 RS32 Leggings Orange M
942 T33 V neck white L
30005 S11 Dress white xs top styles to rank of 300



I get the sheet to look like this in the end:

item |styles |description |color | size|Top 25| Top 50|Top 100|Top 200|Top 300|
19875 2001 t shirt black XL 1 1 1 1 1
19874 2001 t shirt black L 2 2 2 2 2
19873 2001 t shirt black m 25 26 26 26 26
10031 RS32 Leggings Green XS 30 30 30 30
10051 RS32 Leggings Orange M 50 50 50 50
942 T33 V neck white L 63 63 63
30005 S11 Dress white xs 112 112

[sflearn]

ill get you the metadata right now. I never accessed it and will try now.

but first a little about the structure.

. item | styles | description | color | size . Desc1 | Desc2 | Rank
19875 2001 t shirt black XL .2001 t shirt 1
19874 2001 t shirt black L .RS32 legging 2
19873 2001 t shirt black m . S11 dress 3
10031 RS32 Leggings Green XS . 2455 shorts 4
10051 RS32 Leggings Orange M
942 T33 V neck white L
30005 S11 Dress white xs top styles to rank of 300



I get the sheet to look like this in the end:

. item |styles |description |color | size|Top 25| Top 50|Top 100|Top 200|Top 300|.
19875 2001 t shirt black XL 1 1 1 1 1.
19874 2001 t shirt black L 2 2 2 2 2 .
19873 2001 t shirt black m 25 26 26 26 26.
10031 RS32 Leggings Green XS 30 30 30 30.
10051 RS32 Leggings Orange M 50 50 50 50 .
942 T33 V neck white L 63 63 63 .
30005 S11 Dress white xs 112 112.

[sflearn]

so technically I am trying to sort the sales data sheet INTO the columns I have set up AND according to the styles that each piece of data has in common.

how to setup this one situation escapes my understanding...

I have tried this in MS Access and Filemaker pro.
both are similar.

[MMcCarthy]

so technically I am trying to sort the sales data sheet INTO the columns I have set up AND according to the styles that each piece of data has in common.

how to setup this one situation escapes my understanding...

I have tried this in MS Access and Filemaker pro.
both are similar.

I have no idea how you are calculating these Top25-Top300 columns.

Mary

[sflearn]

physically. seriously. is there a way of having the program do the numbers for me?

I am entering the data in excel physically . Is it possible to do this another way?

[MMcCarthy]

physically. seriously. is there a way of having the program do the numbers for me?

I am entering the data in excel physically . Is it possible to do this another way?

I don't know what your calculations are based on. I need more information.

[sflearn]

I have two tables.
one has the style numbers of which several items are in the category of a style number.

and one is the list I want to sort the previous list by. It has 300 styles that sold the best. they are rated from 1 to 300.

I want to organize all my items in each list by the 300 style list.

I have to do this twice a week, for 16 stores, for two separate reports.
ongoing basis.

[MMcCarthy]

I have two tables.
one has the style numbers of which several items are in the category of a style number.

and one is the list I want to sort the previous list by. It has 300 styles that sold the best. they are rated from 1 to 300.

I want to organize all my items in each list by the 300 style list.

I have to do this twice a week, for 16 stores, for two separate reports.
ongoing basis.

I'll need the metadata for the two tables.

[sflearn]

how do I obtain metadata?

and...

I have been reading the website for ways to do this...

is it possible to query?
I dont fully understand querying yet and how I can apply it, I am reading all I can at the moment.

[MMcCarthy]

how do I obtain metadata?

and...

I have been reading the website for ways to do this...

is it possible to query?
I dont fully understand querying yet and how I can apply it, I am reading all I can at the moment.

Open the table in design view where you can see the datatypes and just fill in the template I gave you.

[sflearn]

okay. ill have it soon. I appreciate the level of patience you have had with me.

[sflearn]

can you explain how you retrieved the metadata? I am making an effort to do so on my own but I am missing info on how to do so.

[sflearn]

http://www.imagecabin.com/?view=1764...889c97a67b20be