Combining data into a single field

[cheftim]

Hello all,

I hope that the following explination of my problem in clear...

I have a table with a series of dates in individual fields: Day1, Day2, etc.

What I would like to do now is to combine those into another query/table where the dates are all in one field.

ie:
Original Table
[Day1] March 1
[Day2] March 2
[Day3] March 3

New Table
[Date] March 1, March 2, March 3.

Can you help me figure out how to accomplish this? I greatly appreciate your time and sharing of any thoughts that you have on this situation.

t

 

[wallis1905]

Maybe you could create a query for every date, and in each case alias the name of the date field to be "DATE", basicall breaking the table into its seperate columns via queries. I think you can then use a union query to merge the entries into one column. Obvoiusly a bit impractical if you have a lot of dates.
Don't know if it will work but if it does I'm sure there are better ways.
Perhaps you can create the alias in the sql statement for the union query. You could then do it from one Union query. Not sure though.

 

[wallis1905]

Seems to work

Yes the query mehod seems to work ok.

 

[cheftim]

Wallis -

Thank you - sounds like a good way to go... I was unaware of aliasing and just looked it up in the help menu. Sounds like you've put me on the right track. Thanks.

 

[cheftim]

Thank You!

I was only able to use the Alias proprerty to change the names of the tables, not the fields. But you put me on the right track and I found a way to make this happen. (This may or may not have been what you meant by your post)

I've made 15 append queries (one for each DAY field Day1, Day2, etc.)
I typed DATE: Day1 in the field area of the query to change the name of the field and then appended it to a table named dates with a field named DATE.

I think this will be the break through that I need to get this segment of the database project done by my deadline at the end of March. Whew! Wallis you have (along with a number of others on this forum) been a life-saver.

 

[boblarson]

and then appended it to a table named dates with a field named DATE.

STOP!!! RENAME your field right away!! Do NOT use Access reserved words as field names (or object names for that matter) as it WILL come back to bite you at some point. Change it to something like MyDate, ThisDate, or ThatDate, etc.

 

[wallis1905]

Yeah I guess the append way is another method. Well done. Hope it works out. I wasn't aware that using date was a bad idea though. Thanks for the tip boblarson

 

[cheftim]

Thank you Mr. Larson -
I will address the naming as once.
Other than that, dose this seem like the right way to go with this problem?

Thanks,

Chef