Dupes...Dupes...and more Dupes.... (1 Viewer)

[HaroldIII]

I have 2 tables: Master (A) & Master (B)

There is no unique match between tables. I have numbered the first column in each table 1-whatever....

I have noticed that #'s in Master (A) & Numbers in Master(B) are multiplying on a massive scale. ie; 10 rows in master (A) match to 3 rows in master(B)

is there something i can do to rid this query of dupes??

Please help

 

[boblarson]

If they aren't linked then you get what is known as a Cartesian Product. Which simply means that you get for every one record in the first table you get a record in the other table.

So, one other thing -

The data in the tables are not stored in any particular order. It will attempt to kind of do it if you have an incremented autonumber but there are no guarantees there either. So, in reality you could end up with a different order each time which could mess things up for you.

So, the questions are - why the two tables and why isn't there anything to link them together and what is the purpose of bringing these together?

 

[HaroldIII]

If they aren't linked then you get what is known as a Cartesian Product. Which simply means that you get for every one record in the first table you get a record in the other table.

So, one other thing -

The data in the tables are not stored in any particular order. It will attempt to kind of do it if you have an incremented autonumber but there are no guarantees there either. So, in reality you could end up with a different order each time which could mess things up for you.

So, the questions are - why the two tables and why isn't there anything to link them together and what is the purpose of bringing these together?

One table is a list of Breaks for the month of Janurary, The other table is a list of trades for the corresponding Broker. The issue I beleive is I might have 15 breaks, but A specific broker (Knight) might have placed 3 trades that could have been placed with the same information(date, qty, Buy/sell, symbol....etc..etc) This is giving me 45 results for that item.
Another Broker (V) might have 5 trades that were placed and i get 75 results for that item.

The concept is if I have 15 breaks, between all my brokers i should have 15 trades.
I had an Excel macro that inserted a formula and cleaned everything up. but since they upgraded me to Office 2007, that one report (Knight) that you helped me with, My PC might explode...haha

 

[boblarson]

What is the SQL for your query?

 

[HaroldIII]

Here is the SQL View...However i attached the query & result

If you see the result i highlighted, i have 7 results where i should have 2

SELECT [Janurary 2011 Master].[#400], [Citadel Master].[#], [Janurary 2011 Master].[FIRMS NAME], [Citadel Master].[Trade Date], [Citadel Master].[C/P], [Citadel Master].[B/S], [Citadel Master].Qty, [Citadel Master].Symbol, [Citadel Master].[Exp Month], [Citadel Master].[Exp Day], [Citadel Master].[Strike Px], [Citadel Master].Px, [Citadel Master].FC, [Citadel Master].[Firm ID], [Citadel Master].[Clear #], [Citadel Master].[Contra Code]
FROM [Citadel Master] INNER JOIN [Janurary 2011 Master] ON ([Citadel Master].Px = [Janurary 2011 Master].PX) AND ([Citadel Master].[Strike Px] = [Janurary 2011 Master].Strike) AND ([Citadel Master].[Exp Day] = [Janurary 2011 Master].[Exp Day]) AND ([Citadel Master].[Exp Month] = [Janurary 2011 Master].[Exp Month]) AND ([Citadel Master].Symbol = [Janurary 2011 Master].SYMBOL) AND ([Citadel Master].Qty = [Janurary 2011 Master].Qty) AND ([Citadel Master].[B/S] = [Janurary 2011 Master].[B/S]) AND ([Citadel Master].[C/P] = [Janurary 2011 Master].[C/P]) AND ([Citadel Master].[Trade Date] = [Janurary 2011 Master].[Trade Date])
ORDER BY [Janurary 2011 Master].[FIRMS NAME] DESC;

 

[jdraw]

I can not open doc1. So can not see the result list.

What can you tell us about the history of the tables and data?

What do the tables mean/represent in detail?

Why are there no keys/identifiers?

What do you intend to use these tables for?

Will you have another unstructured table for February?

 

[HaroldIII]

I can not open doc1. So can not see the result list.

What can you tell us about the history of the tables and data?

What do the tables mean/represent in detail?

Why are there no keys/identifiers?

What do you intend to use these tables for?

Will you have another unstructured table for February?

One table is a list of Breaks for the month of Janurary, The other table is a list of trades for the corresponding Broker. The issue I beleive is I might have 15 breaks, but A specific broker (Knight) might have placed 3 trades that could have been placed with the same information(date, qty, Buy/sell, symbol....etc..etc) This is giving me 45 results for that item.
Another Broker (V) might have 5 trades that were placed and i get 75 results for that item.

Not sure what kind of history you want on these tables...
I get one list of trades per broker...6 brokers total...
I get one list of breaks
If I have 22,000 breaks I should have 22,000 corresponding trades done by brokers...

Im trying to eliminate most of the manual search "line by line" and use a query to identify who owns what.....

There is no Key identifier because both tables have the same information. Im only adding the broker name on the matches

Yes..I will be getting another unstructured report on a monthly

 

[jdraw]

What exactly is a "BREAK"?

 

[HaroldIII]

What exactly is a "BREAK"?

not exactly sure how that is relevant....

 

[boblarson]

Get rid of the field #400 so it isn't in the query and you should get the result you're looking for (I believe).

 

[HaroldIII]

Get rid of the field #400 so it isn't in the query and you should get the result you're looking for (I believe).

Nope....still getting dupes in the # field...

is there a way to create a query based on the query i ran, that will remove the dupes??

Otherwise i can check over in the excel forums to see if there is a VBA i can run that will clean it up....

 

[jdraw]

not exactly sure how that is relevant....

It may not be relevant, but it is one of the terms/lists involved. I'm just curious what a Break is in the context you're using it.

I'm not familiar with the term.


This is your equivalent SQL as I see it

SELECT [Janurary 2011 Master].[#400]
, [Citadel Master].[#]
, [Janurary 2011 Master].[FIRMS NAME]
, [Citadel Master].[Trade Date]
, [Citadel Master].[C/P]
, [Citadel Master].[B/S]
, [Citadel Master].Qty
, [Citadel Master].Symbol
, [Citadel Master].[Exp Month]
, [Citadel Master].[Exp Day]
, [Citadel Master].[Strike Px]
, [Citadel Master].Px
, [Citadel Master].FC
, [Citadel Master].[Firm ID]
, [Citadel Master].[Clear #]
, [Citadel Master].[Contra Code]
FROM [Citadel Master] 
,[Janurary 2011 Master] 
WHERE
([Citadel Master].Px = [Janurary 2011 Master].PX) AND 
([Citadel Master].[Strike Px] = [Janurary 2011 Master].Strike) AND 
([Citadel Master].[Exp Day] = [Janurary 2011 Master].[Exp Day]) AND
([Citadel Master].[Exp Month] = [Janurary 2011 Master].[Exp Month]) AND
([Citadel Master].Symbol = [Janurary 2011 Master].SYMBOL) AND
([Citadel Master].Qty = [Janurary 2011 Master].Qty) AND
([Citadel Master].[B/S] = [Janurary 2011 Master].[B/S]) AND 
([Citadel Master].[C/P] = [Janurary 2011 Master].[C/P]) AND 
([Citadel Master].[Trade Date] = [Janurary 2011 Master].[Trade Date])
ORDER BY [Janurary 2011 Master].[FIRMS NAME] DESC;

 

[HaroldIII]

It may not be relevant, but it is one of the terms/lists involved. I'm just curious what a Break is in the context you're using it.

I'm not familiar with the term.


This is your equivalent SQL as I see it

SELECT [Janurary 2011 Master].[#400]
, [Citadel Master].[#]
, [Janurary 2011 Master].[FIRMS NAME]
, [Citadel Master].[Trade Date]
, [Citadel Master].[C/P]
, [Citadel Master].[B/S]
, [Citadel Master].Qty
, [Citadel Master].Symbol
, [Citadel Master].[Exp Month]
, [Citadel Master].[Exp Day]
, [Citadel Master].[Strike Px]
, [Citadel Master].Px
, [Citadel Master].FC
, [Citadel Master].[Firm ID]
, [Citadel Master].[Clear #]
, [Citadel Master].[Contra Code]
FROM [Citadel Master] 
,[Janurary 2011 Master] 
WHERE
([Citadel Master].Px = [Janurary 2011 Master].PX) AND 
([Citadel Master].[Strike Px] = [Janurary 2011 Master].Strike) AND 
([Citadel Master].[Exp Day] = [Janurary 2011 Master].[Exp Day]) AND
([Citadel Master].[Exp Month] = [Janurary 2011 Master].[Exp Month]) AND
([Citadel Master].Symbol = [Janurary 2011 Master].SYMBOL) AND
([Citadel Master].Qty = [Janurary 2011 Master].Qty) AND
([Citadel Master].[B/S] = [Janurary 2011 Master].[B/S]) AND 
([Citadel Master].[C/P] = [Janurary 2011 Master].[C/P]) AND 
([Citadel Master].[Trade Date] = [Janurary 2011 Master].[Trade Date])
ORDER BY [Janurary 2011 Master].[FIRMS NAME] DESC;

Think of it as a list with no names...."Breaks"...the list might have 10,000 rows of data...
then the other list, "Trades" is made up of 100,000 rows of data....Same exact Data that the Breaks have, except this "trades table" includes Names.

I have to take the names on the "Trade Table" and put them on the "Corresponding Brakes Table".

The Break list in this context is names Citadel Master....
The trades is called janurary 2011 master

I see how my explanation was obscured.....sorry

Hope this clears things up.....

 

[boblarson]

Nope....still getting dupes in the # field...

is there a way to create a query based on the query i ran, that will remove the dupes??

Have you tried removing the #400 like I said and then using GROUPING? Click the button on the toolbar/ribbon which looks like this: