Add one per date (1 Viewer)

[Infinite]

Hello! I have lots of shows, and each show has dates. A start date and a end Date. I am trying to figure out how to get access to show me days per show. For instance, if the show start on 6/1/2015 and ended on 6/4/2015, when I create a query that shows that shows dates, and lets say the gross sales, the query currently looks like this:

Show Name Date Gross Sales
State Fair 06-2015 6/1/2015 $89999
State Fair 06-2015 6/2/2015 $999946
State Fair 06-2015 6/3/2015 $12345
State Fair 06-2015 6/4/2015 $15646


I want it to look like this:
Show Name Date Gross Sales
State Fair 06-2015 1 $89999
State Fair 06-2015 2 $999946
State Fair 06-2015 3 $12345
State Fair 06-2015 4 $15646


Is that making any sense in what I want? Thanks for your help!

 

[plog]

Is that making any sense in what I want?

Nope, mostly because I can't parse your data:

Show Name Date Gross Sales

Is that 5 fields or 3 or 4? If 4, is [Show Name] a field or is [Gross Sales] a field?

Please repost your data and parse it with a comma.

With that said, the only difference between those 2 sets of data is that it looks like you want to use DatePart (http://www.techonthenet.com/access/functions/date/datepart.php) to turn 6/1/2015 into 1.

 

[Infinite]

[Show Name]---------[Date] -----[Gross Sales]
State Fair 06-2015--- 6/5/2015-- $89999
State Fair 06-2015--- 6/6/2015-- $999946
State Fair 06-2015--- 6/7/2015-- $12345
State Fair 06-2015--- 6/8/2015-- $15646


I want it to look like this:
[Show Name]--------- Date]-- [Gross Sales]
State Fair 06-2015--- 1------- $89999
State Fair 06-2015--- 2------- $999946
State Fair 06-2015--- 3------- $12345
State Fair 06-2015--- 4------- $15646


Sorry, it looked a lot better before I posted it

 

[plog]

The formattings better, but the data changed. Can you show the starting data that generates that data?

 

[Infinite]

I want to change the date to show what I really wanted. I dont want it turned into "d" I want it turned into a number. 6/5/2015 was the "1st" day of that show, thus it is "day 1"

6/6/2015 is the "2nd" day, thus it should be "day 2"

Does that help?

 

[plog]

That explains it. Let's call your existing query 'ShowQuery_sub1'

What you need to do is create another query on ShowQuery_sub1 to get the first date of each show. So, create a new query and bring in ShowQuery_sub1 as a data source. Then make it an aggregate query (click the Sigma/Summation in the ribbon). For fields, bring down [Show Name] and beneath it, leave it as 'Group By'. Then add this field:

FirstShowDate: [Date]

And under it, change it to Min. Save this query as ShowQuery_sub2

Then make a final query using both those sub queries. Link them via [Show Name] fields and then [Date] to [FirstShowDate]. Bring in all the fields from ShowQuery_sub1 you want to display data for. Then for the field you are calling [Date] in your final query (which you shouldn't and possibly can't do) , you would use this logic:

ShowDay: DateDiff("d", FirstShowDate, [Date]) + 1

However, before you do all that, name your fields and tables better. Only use alphanumeric characters and underscores. This will make coding and communicating about your data easier. Also, 'Date' is a reserved word and makes it difficult to code as well so it should be avoided as a field name.

 

[Infinite]

Thanks! That worked perfectly!