Working with Demo and Concatenate() Function Created by Duane Hookom 2

[Accel45]

I have a table “tblEvents” which is a list of scheduled Events from multiple teams.
The fields in the table are:
ItemID - Autonumber
Date - Date/Time
Time – Date/Time
School – Text
Event – Text

I am attempting to create a query that will combine the Events into one record if they occur on the same date.

This is what I have: “qryEvents”
ItemID Date Time School Event
1 1/7/06 5:00 PM LHS Game
2 1/7/06 7:30 PM SWJH Game
3 1/8/06 4:00 PM CJH Practice
4 1/8/06 5:30 PM WJH Meeting
5 1/9/06 4:00 PM CJH Practice

This is what I want: “qryEventsByDate”
Date Description
1/7/06 5:00 PM – LHS Game
7:00 PM – SWJH Game
1/8/06 4:00 PM – CJH Practice
5:30 PM – WJH Meeting
1/9/06 4:00 PM – CJH Practice

I have attempted to use the Concatenate() Function created by Duane Hookom.

http://www.rogersaccesslibrary.com/OtherLibraries.asp

However I have two issues:
1. Hookom’s Function uses two tables and I only have one table.
2. I am trying to use the “Date” field in the WHERE, which I believe is causing the data type errors I am getting.

Any help with getting Hookom’s function to work for me or other solutions will be appreciated.

Thanks
Accel45

 

[dhookom]

Thanks for asking a very well-stated question. You provided just enough specific information to make your question very easy to understand. If we could give stars for good questions, you would get one.

1. who says you have only one table? I should have created a single table example...

Create a query "qgrpDates" like:
SELECT Distinct [Date]
FROM tblEvents;

2.Use this query as your record source for your query

SELECT [Date], Concatenate("SELECT [Time] & ' - ' & [School] & ' ' & [Event] FROM tblEvents WHERE [Date] = #" & [Date] & "#", Chr(13) & chr(10)) as Description
FROM qgrpDates
ORDER BY [Date];

You may want to wrap the [Time] field in a format() function:
Format([Time],'H:NN AM/PM')

BTW: date and time are both function names and should be avoided when creating field or other object names.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[EvilNewt25]

Hi there Duane,
I am also using your Concatentate Function and am having trouble.

What I am trying to do:

I have a form pulling up the table, personel will be talking with clients to find out what cities they are interested in and using a check box to update the table.

CitiesList Interested (Check Box)
Allen Park Yes
Armada No
Auburn Hills Yes
Belleville No
Berkley No
Beverly Hills Yes
Bingham Farms No
Birmingham No
Bloomfield Hills Yes
Bloomfield Township No
(etc... will continue to grow)

I then need the table to Concatenate the Yes values comma delimited and save them specifically for that Contact ID (Auto Number)

I already have the form functioning and would like to have the cities list appear like:

Allen Park, Auburn Hills, Beverly Hills, Bloomfield Hills

when you click on this Text Box it brings up the form and you choose the cities and it updates the record

Sounds impossible and I have been trying for 2 days now. Any help regarding this issues I really would appreciate!

 

[dhookom]

Do you have a table structure you would like to share? I would expect fields like:

ID Numeric
CityName Text
Interested Y/N

My concern is that each city is a field name rather than a record.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[EvilNewt25]

My apologies... You are exactly right...

ID Numeric
CityName Text
Interested Y/N

 

[dhookom]

Try something like:
Concatenate("SELECT CityName FROM tblNoNameGiven WHERE Interested = -1 AND ID=" & [ID])

If this doesn't work, come back with exact table and field names and where you want the concatenated values to display.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[EvilNewt25]

My original table is Contacts:

Contacts

ContactID AutoNumber
FirstName Text
LastName Text
CityLists Text
(there is a lot more data...)

When in form: (direct link) Contacts there is a tab controls... called General, Calls, Additional Info. In the Additional Info tab there is a feild called CitiesList.

When click CitiesList text box I want it to bring up another form: (directly linked to)

tbl_Cities

Id AutoNumber
CitiesList Text
CountyList Text
Interested Yes/No

from there I check the Interested check boxes that apply to that Contact ID. From there I am lost... do I create a make table or can I have it do the Concatenate function right there to update the one Contact Id with that specific data?

(this whole table is a ready made table I got as a template from Microsoft. (Contact management database)

located:

http://office.microsoft.com/en-us/templates/TC010178391033.aspx

what I have done is changed some of the info needed to customize it for my functions.

Thanks so much for your patience with me.

EvilNewt25

 

[EvilNewt25]

Also I am not so VBA and SQL savy... I usually can figure it out... with baby steps...

Again thank you for your patience.

 

[dhookom]

First, I don't think you should store multiple values in a single field. This goes against everything I hold dear in relational databases. You should have a junction table that stores the ContactID and tbl_Cities.ID for each city a contact is interested in.

I wouldn't go any further until this was implemented. The other option is to wait for the next version of Access which will allow you to store "complex data".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[EvilNewt25]

ok so I need to build another make table that is populated like

ContactID Number
CityID Number

Which data will appear like

ContactID CityID
1 1,4,5,6
2 6,15,17,24

(or)

ContactID Number
CityList Memo

ContactID CityID
1 Allen Park, Belleville, Berkley, Beverly Hills
2 Beverly Hills, Clinton, Dearborn Heights, Farmington


I do not understand.

It is really for purpose of knowing if a contact is interested in buying in a specific city.

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EvilNewt25]

So what I gathered from this data.. (thanks by the way)

I need it to appear like:

ContactID CityList (or) CityId
1 Allen Park 1
1 Belleville 4
1 Berkley 5
1 Beverly Hills 6
2 Beverly Hills 6
2 Clinton 15
2 Dearborn Heights 17
2 Farmington 24

Sorry to keep playin tek tips tag... I know how frustrating it can be

EvilNewt25

 

[dhookom]

EvilNewt25,
You are correct with your final junction table structure. You should be storing the primary key values from your two tables in the junction table.

One of the reasons I wrote the concatenate function is so that you can "reformat" results from a junction table into a single "value".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[EvilNewt25]

Could you suggest the best possible way to populate this table. I would like to do it like I wrote above....

Click on form bring up form for

tbl_Cities
CityId AutoNumber
CitiesList Text
CountyList Text
Interested Yes/No

choose this Contacts Interests... but then how do I get the form to populate my new table with a Contact ID included?

New table:
tbl_CitiesInterest

ContactID Number
City Interests Text
CityID Number

 

[dhookom]

You would normally use a subform to enter your cities of interest. Any other method such as a multi-select list box would take a ton of code.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]