Database Design

[SauravRaaj]

Hi all,

I am struggling to create a database with client ID, Names and Service
Offerings for the year on a daily basis.

The user input will be a tick mark if the service was used by the respective
client for a particular day.

I am interested to use access 2003 as the database and VBA (VB 6.5) (Office
XP-Excel).

Any help! Hints are welcome as well.

Thanks.
regards

 

[Jeff Boyce]

You have described a "how" (as in how you want to do something). From your
description, I'm wondering why you don't just use a spreadsheet.

If you are interested in considering other ways to accomplish something,
please provide a more specific description of "what" (not "how") you want to
accomplish.

'I am struggling' doesn't give us much to go on as to what you may have
already tried ... and you probably don't want us to tell you to try
something you already have, right?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Evi]

I don't know what service offering are, but it sounds as if your basic
structure (at the very least) could be something like

TblClient
ClientID (primary Key, Autonumber)
CFirstName
CSurName
CAddress
or whatever other stuff you want which refers only to the client and not to
the Service they have used.

TblService lising all the services available
ServID (PK)
ServiceName
Other fields about the service which have nothing to do with clients

TblClientService
CSID (PK)
ClientID (linked from TblClient)
ServID (linked from TblService
ServDate( the date the service is used)
other fields as required about the client using the service.


If you want to append all clients and services to the table, ready for
ServDate to be filled in when needed,
create a query. Add TblService and TblClient to the query.
Add ClientID and ServID to the query grid.
click the down arrow next to the QueryType button.
Change the query to an Append Query and choose TblClientService. ClientID
and ServID should appear automatically in the Append To row.
Run the query. All combinations of ClientID and ServID will be in your
table.

You may need some form of protection to ensure that you don't add the same
clientID + servID more than once in any one year (depending on your needs)

Evi

 

[SauravRaaj]

Thanks for the inputs. I did as mentioned in the post till this point, when
the choices here came as TblClient and TblService.

 

[SauravRaaj]

Jeff,

Apologies. I dint write much to get help in return. Here is what I am
working on.

I am working on a database which will store products and services offered to
our clients and the revenue collected from each of them. The structure is as
described below:


Client c001 - will have several Projects running in different locations

A Project p001 (for c001) will have
1. Location Details
2. Equipment Availibility on Location
3. Service Offering used by Client
4. People on the Location

Equipment Availability is a count of Products from a list which is provided
by us at the location. Attributes - EquipmentCategory (4), EquipmentSub
Category (may or may not be under each category), Equipment Name and Count.

Service Offering is a Yes/No option for a Service from a list which is
provided by us at the location. Attributes -Service Category (4), Service Sub
Category (may or may not be under each category), Service Name and Count.

People - is the category of Personnel and Qty of each type on the location
for providing the services for the client

Now each client + location combination by a unique JobID (Format YYabcXXXX,
YY is the Year, abc is a constant string, and XXXX is the increment from 0001
and so on) which is a unique field throughout the database. However, a one
Client and one Location can have more than one JobIDs.

Managers for projects will input the data on a daily/weekly basis
1. Tool availibilty on location (daily counts)
2. Personnel on location (daily counts)
3. Services used (daily counts)
4. Revenue (weekly numbers)

Another table stores the total number of tools (inventory) and personnel
available in the base.

I have done most of it using spreadsheets and using VB macros in the
spreadsheets, but the spreadsheets have become slow due to the excessive
number of controls (say almost 31 x 100 tick boxes). The data for each
client+project combinatin is stored in a new sheet added to the workbook and
read from there. Well, I want to implement this using Access, so that all the
data is contained in this file, and different people access it. The present
situation, the workbook needs to be passed around.

The outputs will be reports based on
1. reveue collections, (for every project, client, and all clients)
2. Equipment Utilization (every project, client, and all clients)
3. Service Offering Counts (every project, client, and all clients)

I hope this gives you an idea of what I am trying to accomplish.

I have created tables -
TblClients - ClientID, ClientName, ProjectName, JobNumber, Notes
TblJobDetails - JobID, JobNumber, Location Name, Equipment, Service,
Revenue, Personnel
TblPersonnel - PersonnelID, PersonnelCategory, PersonnelType, PersonnelQty,
PersonnelDescription
TblRevenue - RevID, Revenue
TblLocation - LocationID, LocaitonName, LocationType, LocationStatus
TblEquipment - ServiceID, ServiceCategory, ServiceType, ServiceName,
ServiceDescription
TblEquipment - EquipmentID, EquipmentCategory, EquipmentType, EquipmentName,
EquipmentQty, EquipmentDescription

How do I design a data entry form whcih will capture this information on
daily/weekly basis. I tried the Pages method in Access, but it returned an
error. I opened the saved page in IE but unsuccesful again.

Hope you will be able to help.
Regards