running a query against other quieries

[Mhypertext]

I have queries that i want to run against each other.

they are differant days of the week m,t,w,t,f,

I want to compare each day to the previous days to remove dupes

IE: Tuesday to monday wednesday to tuesday and monday so on and so forth

so that by friday it compairs to every previous day of the week and shows do dupes

My key is player ID on all days and there is play on each but i only want the first day of play and want to filter out multpile days of play

:IE if the guest comes 3 times in the week i only want the first day of play and want to filter out the rest

 

[jal]

Much of this is unclear.

I have queries that i want to run against each other.

So you have already written two queries? Care to paste them here?

they are differant days of the week m,t,w,t,f,

I want to compare each day to the previous days to remove dupes

IE: Tuesday to monday wednesday to tuesday and monday so on and so forth

so that by friday it compairs to every previous day of the week and shows do dupes

A dup is usually the same record twice. If two records differ by day or date, why do you consider them dups?

 

[Datagopher]

It sounds like you're over complicating it. You could use a Weekday function, which would assign numeric values where you could actually identify the earliest day of the week or you could just simply use dates and assign date ranges to week #s.

 

[Mhypertext]

I may be over complicating it

I have 5 ranges of play i work in a casino
50-199,
200-299
300-499
500+

and 5 days of the week

I took the whole DB and ran the first query for each date of the promo against my mail list for the promo to only but i ran it per day so i have daily play for that week

m,t,w,t,f to get each days play but i only want the first trip when they guest came to the casino

so if they came on monday and friday i only want monday
i dont have play dates in my query

if there is a way to get totals with out running it daily and mulitle trips by the guest please fill me in

this way is taking forever

 

[Mhypertext]

SELECT
FROM [50-99/12-14] INNER JOIN ([50-99/12-13] INNER JOIN ([50-99/12-12] INNER JOIN ([50-99/12-11] INNER JOIN [50-99/12-10] ON [50-99/12-11].[Player ID] = [50-99/12-10].[Player ID]) ON [50-99/12-12].[Player ID] = [50-99/12-11].[Player ID]) ON [50-99/12-13].[Player ID] = [50-99/12-12].[Player ID]) ON [50-99/12-14].[Player ID] = [50-99/12-13].[Player ID];


this is a statement i am using and its not working

if i compair one day at a time it works fine

but if i do 3 days it does not see the mutliple player_ids

 

[Mhypertext]

all i want to do is filter out mutliple records in the week

resulting in totals for that week with one Player_id per that week

i thought doing it the way above would work but relized today after 2 days of data imput it does not work

 

[MSAccessRookie]

SELECT
FROM [50-99/12-14] INNER JOIN ([50-99/12-13] INNER JOIN ([50-99/12-12] INNER JOIN ([50-99/12-11] INNER JOIN [50-99/12-10] ON [50-99/12-11].[Player ID] = [50-99/12-10].[Player ID]) ON [50-99/12-12].[Player ID] = [50-99/12-11].[Player ID]) ON [50-99/12-13].[Player ID] = [50-99/12-12].[Player ID]) ON [50-99/12-14].[Player ID] = [50-99/12-13].[Player ID];


this is a statement i am using and its not working

if i compair one day at a time it works fine

but if i do 3 days it does not see the mutliple player_ids

Looks like Normalization would be a big bonus here. If (Instead of 5 tables), there was one table with an additional column for the date, tehn your query would be as simple as this:

Select [Player ID], Min([Play Date]), {Whatever Else} From [50-99]
Group by [Player ID], {Whatever Else}

Additional Normalization Tips:

• Avoid non-AlphaNumeric Characters like Space, "/", '-' and others.
• Use Camelcase for Tables/Columns with names containing more than one word (Example: [Play Date] could become PlayDate).
• Identify Tables and Columns with Descriptive Names if possible, Including tbl for Table, etc. (Example: [50-99] could become tblGroup50to99)

NOTE: A Union Query could have the same effect if there was a column allotted to contain the date.

 

[Mhypertext]

that would work but i dont have the play dates could i just add that colum to the tables

man there has got to be an easy way to do this

I have to perform this every month

so i should add the date to each table to tell me what day it was? then pull a union on all of it then just doa group by player ID

also that Query group by Player_id

min playdate wont add the other data because i dont want it all
all i want is the one play date per week

the porblem i see is that min playdate will still pull there data even if they came on multple dates

 

[MSAccessRookie]

that would work but i dont have the play dates could i just add that colum to the tables

man there has got to be an easy way to do this

I have to perform this every month

so i should add the date to each table to tell me what day it was? then pull a union on all of it then just doa group by player ID

also that Query group by Player_id

min playdate wont add the other data because i dont want it all
all i want is the one play date per week

the porblem i see is that min playdate will still pull there data even if they came on multple dates

It sure looks like you do. While the PlayDate, (as I called it) may not be available as a Date (or any other form) in a Table, it looks like it is contained (as Text) in the Table Name (Example: [50-99/12-10] is from Date 12-10, Right?).

 

[Mhypertext]

It sure looks like you do. While the PlayDate, (as I called it) may not be available as a Date (or any other form) in a Table, it looks like it is contained (as Text) in the Table Name (Example: [50-99/12-10] is from Date 12-10, Right?).

yes it is the table name

but still if the play on monday i dont want they play from anyother day of the week

 

[MSAccessRookie]

yes it is the table name

but still if the play on monday i dont want they play from anyother day of the week

If you convert the Text to a Date, then you can use the WeekDay related Functions to get the Day of the Week. I am sure that you can search the forums for many examples of how to do this.

 

[Mhypertext]

I guess i am just lost here

so if i run it for monday

then run it for tuesday the person that played on monday will not show up in tuesdays query? how does access know i do not want there play only by pulling it by dates

 

[MSAccessRookie]

I guess i am just lost here

so if i run it for monday

then run it for tuesday the person that played on monday will not show up in tuesdays query? how does access know i do not want there play only by pulling it by dates

You will need to do some work here. You need to set the parameters to be from Today a week before Today (or perhaps reversed), using the Date Functions that Access provides. I have not been trying to do the process for you, but rather assist you along by pointing you towards the proper tools.

 

[Mhypertext]

I know man i am very thankful for your help

i just dont know where to look for this info

I have searched all morning on changing the tables from text to dates and then the functions in access i cant seem to find anything on the web

maybe i am looking for the wrong keywords?

 

[Mhypertext]

Ok i added date fields to all my tables .

now each table has a ply date