Need help with table setup best-practices

[kmcbarne]

I have developed and maintain an Access 2010 database for a call center that has the purpose of storing data relating to the results of automated dialer campaigns. Throughout the day, there are a total of 4 independent campaigns per department. Despite the fact that the campaigns are independant, the results (number of calls, number of connects, etc) are shown cumulative throughout the day. As part of the database, it needs to be able to accept the input of the data into a single form, then subtract out the results from the previous campaign to calculate the total during each individual campaign. For example, at 7am we might make a total of 5,000 calls. At 10, we make another 7,000, so the data we enter for each would be 5,000 and 12,000. So the table needs to take that info, and return records showing 5,000 and 7,000. Up until this point, I have used a calculated field, and each record is comprised of a single day, with field names such as sevenCalls, tenCalls, fourteenCalls, nineteenCalls, and totalCalls. While this does work, it also means that the table has something on the order of 25 fields. What I would like to do is to redesign this so that each record reflects a single campaign, and adding a campaignTime field. I know I can do this, but the issue comes in the fact that I need to have all the campaigns for one day showing on a single entry form. I can't wrap my brain around how to do this. I think it would have something to do with relationships, but I can't seem to visualize/plan out how it would work. I am pasting an image of the current form below, I would like to keep it as close to this as possible.



Can anybody help with an approach on how to do this? I thought of having one table for the campaigns, and then another for the daily totals. The main issue I've run into is not being able to calculate the individual numbers between tables. Additionally, half of my team uses this over a VPN, so I'm trying to lessen the amount of data transferred as latency/lag is a major issue for us with our system.

I greatly appreciate any help anyone can offer...all my Access knowledge is self-taught, and while I have a background in programming, it isn't really a direct parallel.

Thanks in advance,

[June7]

If you need to have all the campaigns for one day showing on a single entry form, then I suspect you need to pre-create 4 records for the day being entered and open form to display the 4 records for user to complete entry.

I don't really understand your form. How is it showing 4 campaigns? What is a 'campaign' - a time slot?

Re: needs to be able to accept the input of the data into a single form, then subtract out the results from the previous campaign to calculate the total during each individual campaign
That sounds difficult.

[kmcbarne]

Yes, a campaign is a single timeslot, so in the picture 7,8,10 is one, 2 another, 7pm another, and 9:15/9:30 the last.

I have precreated the records for the day in other forms, and expected to do this on this one. But one thing I've run into is in creating the form, restricting it showing only those 4 is something I haven't figured our how to do. It has always ended up showing all the records for every day.

[June7]

Filter the form to specific date.

[kmcbarne]

Would this need to be a continuous form then? I'd like to keep it separate like it is, but if I need to I guess continuous would work

[June7]

Continuous or Datasheet if you want to display all 4 records at same time. What do you mean 'separate like it is' - your example form shows 4 records.

[kmcbarne]

Just meaning not locked to the top of the form like a continuous form does. In the picture the boxes kind of "float", not pinned to a specific side of the form.

[June7]

You might be thinking of Datasheet View.

In form Continuous View or Single View, can arrange and size controls as you want if they are not 'grouped' in a layout. Creating form with wizard will 'group' the controls. Select all the controls then right click > Layout > Remove.