Need help setting up and relating tables

[gcrutch]

Hello guys, I need a little help relating my tables. I pretty much know how they should be related but I'm not sure about some of the fields. I have a Cart management system. (This system is to track the Carts (the big green garbage carts everyone uses).

I have 4 main tables. Here is how I have them set up and related as of now.

1.tbl_vAddresses (this table is setup as a view because it is the main City Address database that our GIS dept uses. So everyone hits that database to ensure the addresses being used is valid)
AddressID
Address
Unit
Street Name
Street Type
Quadrant
GarbageRoute

tbl_Service
ServiceID
AddressID
ServiceStatus
NbrOfCarts
ServiceFee
BaseFee
ExtraFee
HURoute
HUAcct
HUClass
BusinessName
Remarks

tbl_ServiceEvent
ServiceEventID
ServiceID
AddressID
ServiceEvent
ServiceDate
ServiceRemarks

tbl_OffStreetService
OSSID
ServiceID
OSS_Status
StartDate
EndDate
CustomerName
CustomerPhone
HU_Tenant
CartLocation
OSS_Remarks

tbl_Carts
CartID
SerialNbr
CartStatus
CartStatusDate
CartEvent
CartEventSubType
CartRemarks

Ok, now I will try to explain how the system works:
For every Address there can only be 1 service. A service can have 1 or more ServiceEvents. A Service can have 0 or 1 Off Street Service.

So here is the tricky part so I'm not really sure how to relate this table to the other tables:
An Address can have 1 or More carts.
But the main field for the Carts is the SerialNbr. The SerialNbr drives everything for the Carts.

I hope I have been somewhat clear on what I'm trying to do. I can't move forward with the design until I get the tables set up right.

Any help is so greatly appreciated.

 

[GinaWhipp]

Hmmm, my thoughts...

tblAddresses
AddressID
Address
Unit
Street Name
Street Type
Quadrant
GarbageRoute

tbl_Service
ServiceID
AddressID
ServiceStatus
NbrOfCarts
ServiceFee
BaseFee
ExtraFee
HURoute
HUAcct
HUClass
BusinessName (REMOIVE)
Remarks
OSS_Status
StartDate
EndDate
CustomerName (A Customer is a Customer whether a Business or not. So I would add a field...)
CustomerTypeID ( ADD - Business, Residental (or whatever and HUTenant)
CustomerPhone
HU_Tenant (REMOVE)
CartLocation
OSS (ADD - Yes/No field)
Remarks


tbl_ServiceEvent
ServiceEventID
ServiceID
AddressID
ServiceEvent
ServiceDate
ServiceRemarks

tbl_OffStreetService (REMOVE entire table)
OSSID
ServiceID
OSS_Status
StartDate
EndDate
CustomerName
CustomerPhone
HU_Tenant
CartLocation
OSS_Remarks

tbl_Carts
CartID
SerialNbr
CartStatus
CartStatusDate
CartEvent
CartEventSubType
CartRemarks

 

[gcrutch]

I think what's making this so difficult is that there is already data out there in the tables from the users. btw this has a sql backend.
This is an Access application that a user created ( a total mess) and I am taking what they had already and doing a redesign to make it more feasible. so trying to get the tables related to accomodate the data that's already out there is somewhat difficult.

 

[GinaWhipp]

Sometimes you have to take down the house to get the foundation right before adding another room...

That siad, the data in the existing tables can be modified/updated to the appropiate field in the appropiate table. I would not focus on the *mess* that was there prior but what is the best way and then adapt the data.

 

[gcrutch]

that is true GinaWhipp. The other thing is, they had only one table. all the fields were in one table. So when I broke the tables down and normalized them. All of the data is just not syncing back up together.

Sometimes you have to take down the house to get the foundation right before adding another room...

That siad, the data in the existing tables can be modified/updated to the appropiate field in the appropiate table. I would not focus on the *mess* that was there prior but what is the best way and then adapt the data.

 

[GinaWhipp]

Ype, been there had that happen but you just work with it and *make it* sync. So when you have data that you don't where (or what table) to put it, just ask that is what we are here for.

 

[gcrutch]

here are a screen shot of the forms that is associated with the tables. it may make more sense.

 

[GinaWhipp]

They can still open forms like that as you can control what shows on the forms via the RecordSource of the form, which will be a query.

 

[gcrutch]

Right, I know that to be true.
the problem is when I create the query using the appropriate tables, I'm having problems retrieving all of the corresponding data because of the fact that in the beginning there was only one table then the tables were broke up and some of the fields were name different. So it's just difficult for me to retrieve all the data. I'm sure it's probably really simple. I'm just lost on how to do it.

 

[GinaWhipp]

Perhaps because you are trying to do this in one query with one form. You can use subforms which no one has to know are subforms. For example, the form pictured below actually has six subforms and one sub/subform...

http://www.regina-whipp.com/index_files/CoilRun.htm

The Users *think* it's all one form. You could apply the same logic.

 

[gcrutch]

each form is using a different query. I would love to be able to do something like you have attached but I wouldn't know where to begin to put all the subforms together like that.

 

[GinaWhipp]

If you want to leave your tables the way they are because you understand them and they are easier to work with, all I can say is... okie dokie then.

Just not sure what you want to do here...