This is driving me crazy 1

[MrMajik]

I need help with this database design. It is straight-forward and is driving me crazy. Here is what I am trying to acomplish:

I need to track computer hardware used by several agencies. The agencies are in several cities and need to also be grouped by county.

I have these tables so far:
County
City
Agency
Assets

How do I make the relationships with these tables so all of this can be viewed on one form?

Thank you.

 

[annsolomon]

You haven't made your case for why you have a separate table for county and another for city. Are these simply lookup tables? That would make sense. I really see the need for only two tables here (tblAgency which includes their address and tblAssets). Again, it you're using tblCity and tblCounty as lookup tables, that's fine.

Assuming the assets you're tracking actually belong to the specific agencies, I'd include a field [AgencyID] in tblAgency and tblAssets and link on that field. On the other hand, if you're tracking assets that you loan/check out to various agencies, you'll probably want a third table (e.g., tblCheckOut) to tie tblAgency and tblAssets together. Ann

 

[MrMajik]

annsolomon; Thank you for the fast reply!

We own the hardware and lease it to them and support it.

I am begining to see why this is driving me crazy. I am making it too diffucult. Will let you know how your suggestion works out.

Thanks again.

 

[MrMajik]

Hi Ann;

After redesigning these tables I think you need to know something else.

Countys - Names are unique.
Cities - Names are unique.
Agencies - Can have several agencies in different cities with the same name.
Assets - Assets are unique by an asset tag number.

My question:
I would like to navigate through this on a form by city first, then Agency and finally asset. County can be a lookup table.

I hope this better explains what I am trying to do than my original post.

Thank you.

 

[annsolomon]

Okay, so tblAgency consists of an AgencyID field and an AgencyName field. tblLocation consists of the AgencyID field and any address fields you may want. The city and county fields in tblLocation can still be lookup fields.

Relate these two tables in a query on AgencyID then you can group on the city if you'd like. Ann

 

[MrMajik]

Hi Ann;

I must have a mental block on getting this to work because I am missing something here. Could you code me a quick example and send the mdb to:

greinesm@michigan.gov

Thank you for all of your patience on this.

 

[annsolomon]

It's on the way. Certainly not complete (e.g., I'd have a lookup table for zip codes)

Hope it gets you started. Ann