sort matching data in ms access

[arman28]

I have two Import and Export tables as shown below;
Import |Style | Color | Qty |
--------------|-------|------
1/2/03 | A201 | Red | 100
1/9/03 | A201 | Red | 20
1/4/03 | B204 | White | 80
1/7/03 | A201 | Red | 100
1/1/03 | C102 | Green | 100

Export | Style | Color | Qty
---------------|------------
4/2/04 | A201 | Red | 100
4/9/04 | A201 | Red | 100
4/2/05 | B204 | White | 80
5/3/05 | B204 | White | 100
6/2/04 | D402 | Blue | 100

I want to match the data based on the "Style" and "Color", so my tables have no index keys and they are related by draging the "Style" of Import and dumpping it onto the "Style" of the "Export". How am I doing? and Please give me an idea of how to create the following table please.

Import|Style| Color| Qty | Export|Style| Color| Qty
----------------------------------------------------
1/2/03| A201| Red | 100 | 1/2/04| A201| Red | 100
1/3/03| A201| Red | 20 | 1/9/04| A201| Red | 100
1/4/03| A201| Red | 100 | * * * * * * * * * * * *
1/2/03| B204| White| 80 | 1/2/05| B204| White| 100
* * * * * * * * * * * * | 1/2/04| B204| White| 80

Thanks.

 

[dhookom]

This might require several queries. The first two are ranking queries.
qrnkImport

SELECT Import.Import, Import.Style, Import.Color, Import.Qty, Count(Import_1.Import) AS Rank
FROM Import INNER JOIN Import AS Import_1 ON (Import.Color = Import_1.Color) AND (Import.Style = Import_1.Style)
WHERE (((Import.Import)>=[Import_1].[Import]))
GROUP BY Import.Import, Import.Style, Import.Color, Import.Qty
ORDER BY Import.Style, Import.Color;

qrnkExport

SELECT Export.Export, Export.Style, Export.Color, Export.Qty, Count(Export_1.Export) AS Rank
FROM Export INNER JOIN Export AS Export_1 ON (Export.Color = Export_1.Color) AND (Export.Style = Export_1.Style)
WHERE (((Export.Export)>=[Export_1].[Export]))
GROUP BY Export.Export, Export.Style, Export.Color, Export.Qty
ORDER BY Export.Style, Export.Color;

Then a union query to get all possible combinations and ranks:
quniStyleColorRank

SELECT qrnkImport.Style, qrnkImport.Color, qrnkImport.Rank
FROM qrnkImport
UNION SELECT Style, Color, Rank
FROM qrnkExport;

And finally:

SELECT qrnkImport.Import, qrnkImport.Style AS ImportStyle, qrnkImport.Color AS ImportColor, qrnkImport.Qty AS ImportQty, qrnkExport.Export, qrnkExport.Style AS ExportStyle, qrnkExport.Color AS ExportColor, qrnkExport.Qty AS ExportQty
FROM (quniStyleColorRank LEFT JOIN qrnkExport ON (quniStyleColorRank.Rank = qrnkExport.Rank) AND (quniStyleColorRank.Color = qrnkExport.Color) AND (quniStyleColorRank.Style = qrnkExport.Style)) LEFT JOIN qrnkImport ON (quniStyleColorRank.Rank = qrnkImport.Rank) AND (quniStyleColorRank.Color = qrnkImport.Color) AND (quniStyleColorRank.Style = qrnkImport.Style);

The C and D styles are included in the results. You should be able to get rid of them.



Duane
Hook'D on Access
MS Access MVP

 

[arman28]

Thank you for your help. I thought this is so complicated that help would never arrive
Regards;
Arman

 

[arman28]

Duane..
I have tested these queries over and over and over but what I get as my final query does have missing data. Any thoughts please? I hope you get back to me soon.

Arman

 

[dhookom]

I'm having trouble seeing your beginning data as well as your sql and your results. You have to figure out which records don't show and then determine why they are different from the records that do show.

Duane
Hook'D on Access
MS Access MVP

 

[arman28]

so maybe this has something to do with the RANK clause or how the first sql ranks the output. I am thinking the date has something to do with it. It is probably right in my face but I don't see it just like how it is sometimes in life. This is for my wife's company and if I don't figure it out...I will be homeless

 

[dhookom]

Do you have IMPORT and EXPORT of the same Style and Color on the same day?

Duane
Hook'D on Access
MS Access MVP

 

[arman28]

No, not on the same date. This company brings in shoes as "Import" and sends some out as "Export". Every shoe has a style number called "Style" and "Color" and quantity "Qty". Now they may import 50, A4201, red, this year and export 15, A4201, red next year january and 20, A4201, red in march. I want to see this;

2/20/2009 - A4201 - red - 50 | 1/5/2010 - A4201 - red - 15
********************* 3/5/2010 - A4201 - red - 20

Bue what I see in some instances is

2/20/2009 - A4201 - red - 50 |
3/5/2010 - A4201 - red - 20

 

[dhookom]

in some instances is" you need to find when the records don't display.

Duane
Hook'D on Access
MS Access MVP

 

[arman28]

Hello..
I have tried everything but I cannot seem to see the problem. Here is my Import, Export and the output;

Import |Style | Color | Qty |
----------------|-------|------
11/18/09 | A201 | Red | 195

Export | Style | Color | Qty
---------------|------------
9/11/09 | A201 | Red | 30
10/9/09 | A201 | Red | 15
9/10/09 | A201 | Red | 15
9/3/09 | A201 | Red | 15
8/27/09 | A201 | Red | 15
9/3/09 | A201 | Red | 15
9/3/09 | A201 | Red | 15

Result

Import Style Color Qty Export Style Color Qty
8/20/09 A201 Red 195 8/27/09 A201 Red 15
* * * * * ** * * * * * ***9/10/09 A201 Red 15
* * * * * ** * * * * * ***9/11/09 A201 Red 30
* * * * * ** * * * * * **10/9/09 A201 Red 15
* * * * * ** * * * * * ***9/3/09 A201 Red 15

 

[dhookom]

I asked

Do you have IMPORT and EXPORT of the same Style and Color on the same day?

You stated

No, not on the same date

Then you provided this data:
[tt][blue]
Export | Style | Color | Qty
---------------|------------
9/11/09 | A201 | Red | 30
10/9/09 | A201 | Red | 15
9/10/09 | A201 | Red | 15
[highlight]9/3/09 | A201 | Red | 15[/highlight]
8/27/09 | A201 | Red | 15
[highlight]9/3/09 | A201 | Red | 15[/highlight]
[highlight]9/3/09 | A201 | Red | 15[/highlight]
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[arman28]

I am sorry I may have missed that then, my apologies. So based on your opinion, where and what do I need to change so the problem will be resolved?!

Regards;
Arman

 

[dhookom]

Start over and describe your data, provide some sample records that are more characteristic, and then tell us how you expect those records to display in your query.

I spent a fair amount of time building the four queries I suggested earlier which resulted in your desired output. Then I find out your specs were not accurate. If you want me or anyone else to help, the least you could do is provide the correct specifications and sample data.

Duane
Hook'D on Access
MS Access MVP

 

[arman28]

You are absolutely correct and I cannot argue with that. Now, my data consists of two tables, Import and Export. These are shoes that come in and leave the company that is importing them. The Import and the Export tables both have these information that could be unique and could sets them apart, such as; Style, Color, Quantity and now I think I may have to include the Date as well. I match the Import and the Export based on the Style and Color to pull the "like" data by writing queries. The last example of data is a good sample of what I have. Shall I give you more info?

Arman

 

[dhookom]

How about some actual records and actual desired display in the results?

Duane
Hook'D on Access
MS Access MVP

 

[arman28]

sure, let me see if I can attach some data here....be back shortly

 

[arman28]

Import Table
Date Style Color Qty
4/24/09 A4265 White 210
4/24/09 A4265 Black 105
4/24/09 A4265 Citrus 150
3/6/09 A4265 Citrus 105
3/6/09 A4265 Metallic 165
3/6/09 A4265 Terra 90
3/6/09 A4265 White 90
3/6/09 A4265 Black 45
4/24/09 A4265 Terra 120

 

[arman28]

Export Table
Date Style Color Qty
7/10/09 A4265 Black 15
11/19/09 A4265 Metallic 15
4/30/09 A4265 White 15
7/10/09 A4265 Citrus 15
5/7/09 A4265 White 15
7/10/09 A4265 White 15
7/10/09 A4265 Terra 15
5/7/09 A4265 Terra 15
5/7/09 A4265 White 15
4/30/09 A4265 Citrus 30
4/30/09 A4265 Black 30
4/30/09 A4265 White 15
4/30/09 A4265 Citrus 15
4/30/09 A4265 Terra 15
8/21/09 A4265 Terra 15
8/21/09 A4265 Black 15
4/30/09 A4265 Citrus 15

5/7/09 A4265 Terra 15

5/7/09 A4265 Citrus 15

5/12/09 A4265 White 30
5/7/09 A4265 Black 15
4/30/09 A4265 White 30
5/7/09 A4265 Citrus 15

5/1/09 A4265 Citrus 45

5/1/09 A4265 Terra 15
5/1/09 A4265 White 15
5/1/09 A4265 Black 15
5/7/09 A4265 White 15
5/1/09 A4265 White 30
5/12/09 A4265 White 15
5/1/09 A4265 Terra 15
5/1/09 A4265 White 15
5/1/09 A4265 Citrus 15

5/1/09 A4265 Terra 30
5/12/09 A4265 Citrus 15
4/30/09 A4265 Black 15
4/30/09 A4265 White 15
5/1/09 A4265 Black 30

 

[arman28]

The Result Table
Date Style Color Qty Date Style Color Qty
* * * * * * * * * * * * * * * 5/1/2009 A4265 White 15
3/6/09 A4265 Black 45 * * * * * * * * * * * * *
4/24/09 A4265 Black 105 4/30/2009 A4265 Black 30
4/24/09 A4265 Black 105 4/30/2009 A4265 Black 15
* * * * * * * * * * * * * * * 5/1/2009 A4265 Black 30
* * * * * * * * * * * * * * * 5/1/2009 A4265 Black 15
* * * * * * * * * * * * * * * 5/7/2009 A4265 Black 15
* * * * * * * * * * * * * * * 7/10/2009 A4265 Black 15
* * * * * * * * * * * * * * * 8/21/2009 A4265 Black 15

I hope this along with I gave you above before these tables will give you an idea of the actual record.
Please let me know if more is needed.

 

[dhookom]

I copied and pasted your new import and export records into my test tables and ran the same queries as I had above. This the first 8 rows returned in the query

3/6/2009   A4265  Black   45				
4/24/2009  A4265  Black  105  4/30/2009  A4265  Black  30
4/24/2009  A4265  Black  105  4/30/2009  A4265  Black  15
                              5/1/2009   A4265  Black  30
                              5/1/2009   A4265  Black  15
                              5/7/2009   A4265  Black  15
                              7/10/2009  A4265  Black  15
                              8/21/2009  A4265  Black  15

I don't know where your other white and other color records went in your desired results but they were all included in mine (from the above queries).

Duane
Hook'D on Access
MS Access MVP