Date query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
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


Jerry Whittle said:
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.


Andy666 said:
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.
Click to expand...
Click to expand...
 
Back
Top