Help with database structure, data being imported in XML

[sjctrumpet]

Hi all,



I'm a new access user and have some general questions on how to structure a database. I'm trying to make a database for viewing, reporting, and grading (adding notes to) trouble tickets. The tickets themselves are generated and used in a 3rd party application, but that app has no quality control or coaching functions so I want to build my own database to handle that.

I can export tickets in XML format, pulling out only the relevant fields, by using the 3rd party application's query builder / exporter.

Here are the features I'd like to implement:

• a nice front end form that can view each ticket and its related notes and other relevant information, and supervisors can use to add notes into comment fields for quality control / ticket coaching.
• generate reports that sort by ticket type, date range, user, rating, etc. Would love to eventually have macro enabled buttons that do all the report parameter changes.
• email reports automatically.

The problem is that each "TicketNotes" entry comes in along with all other ticket information as its own record. Ideally, (I think) each TicketNotes entry would be another column on a single entry (the ticket itself), and there would be one record per ticket. Unfortunately I often see a dozen records for each ticket. This is making building meaningful queries and forms hard. I can't even figure out how to group by ticket. Of course I also suspect that leaving things in the structure that they import into makes for a horribly inefficient database.

So far all I've done is make a form, a few querys to sort tickets relate UserIDs to a Users table (name, userid, department from departments table)--- but when I pull my data into a form, I want each entry to be a ticket, with all related entries showing, not each ticket entry being its own record.

Here is an example table of how the data comes in (Fake but similar data and labels) Thank you so much in advance for your help!

ID	TicketNumber	TicketStartedBy	TicketNotes	UserIDforTicketNotes	TicketNoteCreateTimestamp	CustomerName	TicketResolutionCode	TicketResolutionNotes	TicketStatus	ServiceType	UniqueBillingCode	TicketCloseTimestamp
2	12344	Steve	Notes from first user. Needs to be long text format.	116	3/20/15/19:15:22	Bob Bobson	Trouble Cleared	Repaired bad CAT5e	Open	FTTH	100-150-1795	3/24/2015 14:41:12
3	12344	Steve	More notes on same ticket from another user.	115	3/20/15/19:19:25	Bob Bobson	Trouble Cleared	Repaired bad CAT5e	Open	FTTH	100-150-1795	3/24/2015 14:41:12
4	12345	Jake	Notes from first user on another ticket.	110	3/2/15/00:51:02	Ron Ronson	User Education	Customer had computer off	Closed	VDSL	101-154-1733	3/24/2015 15:41:12
5	12345	Jake	More notes on same ticket from another user.	112	3/2/15/02:21:41	Ron Ronson	User Education	Customer had computer off	Closed	VDSL	101-154-1733	3/24/2015 15:41:12

[June7]

Have you looked at the MS Desktop Issue Tracking database template?

[sjctrumpet]

Have you looked at the MS Desktop Issue Tracking database template?

Looking into it now. Thanks for the pointer! Hopefully I can find a way to get my data to populate the correct tables somewhat automatically.

[sjctrumpet]

I am still having trouble with one thing.

So right now I have records something like this:

Ticket Number 1, comment 1, user 1, timestamp
Ticket Number 1, comment 2, user 5, timestamp
Ticket Number 1, comment 3, user 1, timestamp

And I need to find a way to automatically turn that into records something like

Ticket 1, comment 1, comment1user, entry timestamp, comment 2, comment2user, comment2 timestamp, comment 3, comment3user, comment3 timestamp.

The goal is to display each ticket with all related comments rather than going through different records.

[ssanfu]

I would use grouping in a report instead of recombining the records.

If you really must concatenate records, see Allen Browns' code at http://www.allenbrowne.com/func-concat.html