Question Trying to create a database

[dennhop]

Ok, so, before I explain what I'm trying to do, just want to introduce myself so that you all know what you're dealing with...
I'm a complete N00b to Access as a whole. I've been reading through a couple of walkthroughs online, and then stumbled across this forum. I'll try and keep my questions to a minimum, but there's a lot of stuff I don't understand yet. I'm trying to build this in somewhat of a time crunch, and I'm having issues with certain parts of my build.

So, here's what I'm trying to do.

I'm trying to build an interactive database, that will allow us to track our radio assets. Each radio set consists of multiple components, and is assigned to a vehicle. The vehicles are tracked by serial numbers, as well as the radios themselves. I'm also trying to set up the database to track the components to the individual radio sets...ie, an inventory of the radio set itself. Here's what I've got so far, in trying to build a test database.

I have 4 tables right now:

*Table* radio information
(Tabs)
ID Radio Serial#
2 Radio 12345
3 Radio 23456
4 Radio 34567
5 Radio 45678

*Table* Vehicle Info
(Tabs)
Vehicle id Vehicle type Vehicle Serial#
1 D0022 611222
2 D0033 622111

*Table* Radio Set
(Tabs)
ID System Set number
1 VRC-xxx 12345001
2 VRC-xxx 12345002

*Table* Components
ID item qty OH/MI1 OH/MI2 part number price
1 cable 2 *checkbox* *checkbox* 22222222222 100.00
2 antenna 1 *checkbox* *checkbox* 11231234412 200.00
3 base 2 *checkbox* *checkbox* 11243453111 100.00

I've got a couple of different questions concerning what I'm trying to do...
on the Components table, what I'd like to do is associate the yes/no checkbox to the item and qty, as either on hand or missing, then associate that to the price. I cant seem to figure out what I need to do to associate those cells within the same table as a ranging equation, so that when you select that item as being on hand, it automatically calculates the part as being on hand, or if its missing, it adds up the total cost.

Also, I'm a little lost in how relationships are built. I'm trying to set it up so that you can associate as a database user, radio sets, radio serial numbers and the vehicle info as one complete radio set. These items are constantly changing, so the users would need to be able to do this without getting to deep into the database programming. Any ideas would be greatly appreciated. I'm still working through the tutorials I've found, but the biggest issue I'm having currently is trying to get the relationships built within the same table.

 

[PNGBill]

One way may be to consider the finished Radio as a point of reference.
If you have 10 Radios and they have different componets at different times, then you could allocate a RadioID to each part.
If a part is not in a radio, then the RadioID field for that part is zero/blank/null.. or the RadioID is entered.

Code on your RadioForm will prevent two of the same parts being allocated to the same radio.

In a factory situation, you would just deal with Parts being a qty ie 6900 led screens and the Stock on hand is increased or decreased as they are used or restocked.

It sounds like your situation is more "Cotage" in that you have a set number of parts and just want to keep track of what part is in what radio.
This would imply all the parts would be individualy identified so you would have a record for each led screen.

Your description of tables appears to ok. Just watch the names used. Try and avoid ID as a field name. Better with LEDID, AriealID, BatteryID.
You have Junction Tables. These are tables that keep track of current relationships between records.
Instead of having a field in your tblArieals for the RadioID you could have a record in your tblRadioPartsJunc with fields RadioID, PartDesc, PartID, DateInstalled.
This table will keep track of the latest parts in each Radio. A Select Query will only look at the most recent date for each radio/part to return the parts list for each radio.

Another Junction Table could be tblVehicleRadioJunc to keep track of radios installed in vehicles.

Please consider above as ideas only. Your specific needs may not suit any or all of the above.
Consider good database design which avoids redundant data. If a result can be achieved with less tables and more queries then you may be on the right track.

 

[dennhop]

So after doing a little reading (emphasis on the little), the junction table seems like it might be a bit easier to try. That being said, if you could just let me know if I'm on the right track on this.

So let me toss everything else out the window right now, and start really from the top down, to simplify it down to 2 items to create the radio set. (Just to make sure I"m on the right track for now, and then I'll go back to the other issues later).

So, I'll build a table labeled VRC-xxx, which will be the complete end result, of the radio set married up with the truck.
Then build a table labeled setnumbers, and trucks.

The junction table would then be tbleVRC-xxx, consisting of the ID field, we'll say VRC-xxxID, fldsetnumberid, and fldtruckid. Then, I should be able to build the relationships from there.

Assuming I'm on the right track from there, I could then continue to build the relationships further down, to identify components, with the same table structure.

Does that seem to make sense?

 

[PNGBill]

tblTruck I would say is a Given. This holds the TruckID, RegNum, etc.

tblRadio is the finished radio set table.

A Junction Table would then hold the latest data on what radio is in what truck.

Try and refer to objects like this. Tables (hold raw data) and have a table name (tblTruck)
A table has Fields - TruckID (primary key ussauly), TruckReg, TruckColour ....

You then have Records in a table and of course records in your queries.

Tables have Relationships. This is where you define the relationship between the Trsuck and the Radio or the Radio and the Parts of the radio.

Where there is no clear "join" you can add a Junction Table to create that join. This works wel where the relationship may not be constant. ie the radio is not always in truck xyz.

 

[spikepl]

I'll build a table labeled VRC-xxx

noooooooooooooooo!!!!!

"xxx" is data. Data does not belong in table names. You will have to do some reading on database normalization - google it. The time you "save" on not getting the basics right you will waste multiple times later, battling derived faults.

A table is a container of like items, a bucket, if you like. To discern between the items you tag them as required, but keep them in the same table. Ie. stick a label on each item you put into the bucket. Keeping like items in different buckets requires additional administration of which specific bits are in which bucket (like your xxx) - that plays havoc with queries, forms and reports down the road.

 

[dennhop]

So I'm having an issue creating the relationship right now. I went ahead, and modified the tables. Renamed them as TbleRadio, TbleTruck and TbleSet, Tble Radio being the end product, of a combination of the truck serial number and the set number.

I've been able to create the one to many relationship between the primary fldtruckid and fldtruckid on the TbleRadio. However, when I try to do the same between the primary key fldSetID and fldSetID on Tble Radio, I get an error, saying relationships must be on the same number of fields with the same data types. I'm not sure what I'm doing differently between TbleTruck and TbleSet.

 

[dennhop]

noooooooooooooooo!!!!!

"xxx" is data. Data does not belong in table names. You will have to do some reading on database normalization - google it. The time you "save" on not getting the basics right you will waste multiple times later, battling derived faults.

A table is a container of like items, a bucket, if you like. To discern between the items you tag them as required, but keep them in the same table. Ie. stick a label on each item you put into the bucket. Keeping like items in different buckets requires additional administration of which specific bits are in which bucket (like your xxx) - that plays havoc with queries, forms and reports down the road.

Sorry, I was using xxx as a blank identifier for the numbers that were going to be in there. My fault, I won't use that example again.

 

[spikepl]

The way to communicate table design is either by example of field names and content, like you did, or by describing the tables like this:

tblMyItems ' table name
------------
MyItemID (PK Autonumber -primary key)
MyItemDescription (text)
SomeOther Attribute. (some number)
...
MyMainItemID' (FK Long - foreign key = the PK of the record in tblMainItems to which this record is related)

 

[dennhop]

So I'm having an issue creating the relationship right now. I went ahead, and modified the tables. Renamed them as TbleRadio, TbleTruck and TbleSet, Tble Radio being the end product, of a combination of the truck serial number and the set number.

I've been able to create the one to many relationship between the primary fldtruckid and fldtruckid on the TbleRadio. However, when I try to do the same between the primary key fldSetID and fldSetID on Tble Radio, I get an error, saying relationships must be on the same number of fields with the same data types. I'm not sure what I'm doing differently between TbleTruck and TbleSet.

Disregard, I figured out I had a difference in formatting between the PK of TbleSet and the FK of TbleRadio

 

[dennhop]

Ok, so I'm slowly starting to figure out the very basics of what I need to do. I recreated a DB at work, since I didn't share my work with myself from my home computer. (also, it's probably only a matter of time before I get locked out of this forum here at work)

I created 3 tables.

tbleradioset
tblertserial
tbletruckserial

under tbletruckserial I have
fldtrucktam (the TAM is an identifying number used to denote the model of truck)
fldtruckserial

under tblertserial I have
fldrtid
fldrtserial

and under tbleradioset I have
fldradioid
fldrtid
fldtruckid
fldtruckinfo (which is a dropdown with the truck serial and related TAM)
fldrtserial (which is a dropdown to select radios. RT is used as the identifier for the specific radios *RT* Reciever-Transmitter...sorry, military jargon, I know it can be confusing)

For whatever reason, I couldn't get the fldrtid or fldtruckid to do the dropdowns, so I ended up creating the fldtruckinfo and fld rtser to create the dropdown selection boxes. I honestly don't exactly know what I did, I just know I got it working this far...

I also created a form to use, named radio set.
I'm still working out the bugs to get the set number to populate somewhere on the form, in relation to the corresponding vehicle and radios, ie:

set number 12310001 consists of vehicle TAM D0033, serial 123, as well as RT serials 1234 and 3456.

the form is allowing me to select the truck tam and serial, and then associate the two rt serials with it. I'm not sure now how to publish this as just a user database, which will allow the users to make changes to the sets, without actually changing out the program as a whole.

Thanks to all for your patience and help so far, and hopefully I'll be able to get this completed soon....

 

[PNGBill]

Common advice is do not use Lookup Fields in your Tables.
This can be done on your Form you use to interact with the table.

You appear to have Redundant Data.
tbleradioset only needs one link to tbletruckserial.
You use queries to collect any other Data req'd for a task.

What is tblertserial's purpose ?

Each Table should be the a Unique set of Data.
eg tbltruckserial will hold any data on a truck.
Any other table that requires a link to a truck only needs to have one field. truckID and this will hold the PK of tbltruckserial.

If you choose to use a Junction Table, then you do not even have to have truckID as a field on the other table as tblRadioInTruck will hold the RadioID and TruckID as one record. If you include a DateInstalled Field then you also maitain a history of what radio was in which truck.

A query will just select the latest entry for each Truck and you have the Current radio/truck listing. More then one radio per truck could be covered by adding a field to tblRadioInTruck RemovedDate and your query will only display records where this date is Null and group by TruckID will show all radios in a given truck.
Here only you can complete this part as you are aware of the issues involved.

 

[dennhop]

Ok, so, lets see if I got this right...

the reason that I have a tbleradioset as well as a tblertserial is that each radio set consists of 1 truck, and 2 radios. These three items, which are all individually serialized, make up a larger set...I guess the best way to explain it would be a shelf stereo system.
You have one main control unit, and two speakers, but all three items make up a set. The speakers and the control unit all have their individual serial numbers, but all together, the system has its own serial, which is the manufacturer serial number for the system...

So while I only need to do a one to one relationship between tbletruckserial and tbleradioset, I need to be able to do at least 2-1 for tblertserial to tblradioset.

right now I have a relationship coming from tbltruckid PK going to fldtruckid on tbleradioset. I also have a relationship going from tblertserial PK to fldrtserialid on tblradioset. What I can't seem to do now, is create the drop down boxes on the form that will allow me to select two rtserials for one system, and then disregard those two serials in the future, so I don't accidentaly use them again. Not sure what I did differently, since earlier today I was able to do this.

 

[PNGBill]

You create a query that includes the relevant tables.
Select all items that are not already included elswhere.

When this query works, view it as sql and past it into the appropriate property in your form control - combobox.

set the combobox to use the sql as it's record source.

You can set the number of rows displayed and columns displayed.

You also set the field that is used by the combobox to be it's datasource.

Each time you Click on teh combobox, the sql will run which should mean any Radio already selected, will no longer be included in the recordset.