Date query

[Guest]

Hey can someone give me some advice please, this is a bit of a long explanation

I have created a table for a list of 12 months starting from now using the
expressions Date() and DateAdd('m', 1, Date()).

I have another table containing values, which I want to compare using the 12
months, this table being

Month (date/time, formatted to "mmm yy"), Artist (text), Title (text)

I want to find out which artists have releases coming up so essentially i
want to get all the months from the "12 months starting from now" and get the
artists associated with that month, or null if there isnt any.

I have put all the 12 months into a single column using a union query to
group them together this is done with

SELECT Month0
From [Tbl_12 Months starting now]
UNION
SELECT Month1
From [Tbl_12 Months starting now]
UNION
SELECT Month2
From [Tbl_12 Months starting now]
UNION....(think you get the picture)

this works putting the months into a single column for use in a query but it
wont match up any corresponding months the sql i got is

SELECT [Qry_Artist releases].Month, [Qry_Artist releases].Artist,
[Qry_Artist releases].title
FROM ([Qry_12 month from now] INNER JOIN [Qry_Artist releases].Month
ON [Qry_12 month from now.Month = [Qry_Artist releases].Month)
GROUP BY [Qry_12 month from now].Month, [Qry_Artist
releases].Artist,[Qry_Artist releases].title;

I apologise for the long windedness tried to summarise without missing out
information.

 

[Guest]

If your table has 12 or so month fields across, your table has serious
normalization problems and just will NOT work correctly in a relational
database. Period.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.

 

[Guest]

I do have a seperate table containing months, my database is normalised to
3NF. I thought this would work but hey no, you know trying different things
to solve it myself. Period
--
Thank you

Andy666

If your table has 12 or so month fields across, your table has serious
normalization problems and just will NOT work correctly in a relational
database. Period.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hey can someone give me some advice please, this is a bit of a long explanation

I have created a table for a list of 12 months starting from now using the
expressions Date() and DateAdd('m', 1, Date()).

I have another table containing values, which I want to compare using the 12
months, this table being

Month (date/time, formatted to "mmm yy"), Artist (text), Title (text)

I want to find out which artists have releases coming up so essentially i
want to get all the months from the "12 months starting from now" and get the
artists associated with that month, or null if there isnt any.

I have put all the 12 months into a single column using a union query to
group them together this is done with

SELECT Month0
From [Tbl_12 Months starting now]
UNION
SELECT Month1
From [Tbl_12 Months starting now]
UNION
SELECT Month2
From [Tbl_12 Months starting now]
UNION....(think you get the picture)

this works putting the months into a single column for use in a query but it
wont match up any corresponding months the sql i got is

SELECT [Qry_Artist releases].Month, [Qry_Artist releases].Artist,
[Qry_Artist releases].title
FROM ([Qry_12 month from now] INNER JOIN [Qry_Artist releases].Month
ON [Qry_12 month from now.Month = [Qry_Artist releases].Month)
GROUP BY [Qry_12 month from now].Month, [Qry_Artist
releases].Artist,[Qry_Artist releases].title;

I apologise for the long windedness tried to summarise without missing out
information.