Unrelated tables and table missing

[TaiChi56]

I need help with this database. What am I doing wrong too have these errors. The file is attached. Thank you.

test.zip

[LaRetta]

In your Armory file, go to layout mode. Select Layouts > Layout Setup. Layout name is Form View. Based upon table occurence is < unknown >. Reselect your Armory table occurrence here.

If you look (in your relationship graph) at your first relationship from Armory to Gun Log, you will notice that (in Gun Log) it points to the HEADING and not a field. You might have meant to join Armory to Sales Info (on serial) or directly to Gun Log - I'm unclear. But regardless, Serial Number must join Serial #. I am unsure how your Sales Info fits here. It sits between Armory and Gun Log but isn't connected to either of them.

When you are basing a relationship on =, the fields must match on both sides (it appears in your case to serial#). Also, the layout (and it's associated fields) must point to a table occurrence within the same TO group and your Armory layout wasn't pointing to any of them.

Once you've joined serial to serial, create a new record in Armory. You can then enter Date Purchased.

Did you have more problems than that? I stopped looking at that point.

LaRetta

[Fenton]

Your nice new layouts are not attached to any table occurrence. That's why the <unrelated table>. Personally I would do this in only 1 file, with 3 tables, like this.

I don't really know what Gun Log is however.

Armory.zip

[TaiChi56]

Thank you the database looks great. I sure appreciate the help.

[TaiChi56]

Thank you for your help also. This is why I love this forum.

[TaiChi56]

On my Armory layout I have a Date sold when the gun is sold. I want the Sale info file to match so when you go to a particular gun and see that it is sold you can just click on the sold button and the proper sale info comes up. How can I relate it so this happens? Right now when I do it the same info shows up for each file.

[Fenton]

There are questions. I don't really know what an armory is, other than a place to store guns. I know what a sale is; but I don't know in this case whether the same gun could be sold more than once. And I have no real idea what the gun log is for.

So, you need to explain those things, in particular whether there is one or many of a thing in relation to the others, and when the data is entered in each, what the sequence is.

You have overlap of data fields. For example, you have a "date sold" in Armory, as well as a date in the Sales table. The date is likely entered in Sales. It can cause difficulties if that date must also be in Armory, unless you build an iron-clad mechanism to copy the date over. You should not have to manually enter the same data in 2 places; tiresome and prone to errors of omission.

And, once again, Gun Log seems to be conglomeration of fields from the other tables. Perhaps this is intentional. But it is suspicious; and I can't see what it's supposed to do.

Wherever a particular gun is first entered (Armory?), the Serial Number field in that table should be an auto-entered serial ID, increment by 1. Then that value must be entered into that gun's records in the other tables. There is more than one way to do so. Then going to a particular gun should automatically show data about it in the other tables, in portal(s) on that layout.

You might have to enlarge your layouts, which are pretty, but kind of cramped for space. You will need to learn what a portal is. I'll have more time tomorrow, but it's bedtime for bozo -]

[TaiChi56]

Yes, you are correct on the Armory. Just called it that. This layout is where the customer puts all the information when he buys the guns. It is for a gun dealer. You are correct the same type gun may be sold but not the same gun with the same serial number. A gun log is where the dealer logs in when he shoots a particular gun. This is where he would put how many rounds went through it and if there was any problems. Many customers want to know that when they are buying a used gun.

I understand what you are saying about having a date sold on the Armory and Sales info. Maybe I can just put a radio button on the Armory layout like "Sold Yes or NO" and then the dealer could go to that record if the gun was sold.

Biggest thing I need is that if the dealer does look at a record under Armory and sees that he did sell the gun, then he could click on the button that the gun was sold and it would go to the particular record related to that gun. When I do it the record is attached to any gun I put in there. I am obviously doing it wrong. What do you thing would be the best way. Thank you for your help.

[Fenton]

If a gun, with its unique serial number, is a record in Armory, and it is only sold once, then there is no need for the Sales Info table. Those fields could just go into Armory. That would take care of the sale date problem. It is only if the same gun could be sold multiple times (bought back or taken as trade-in, then resold to someone else) that you would want a separate sales tables (remember this is if it could EVER happen, and it seems likely).

Otherwise, if there was a Sales Info table, there would be a link on the serial#. The sales date, etc., would show as related fields in a portal in Armory. If "allow creation of related records" was turned on, typing a sales date into the last portal row (blank) would create a new sale record. Or a button for a "new transaction" (in or out).

Gun Log has some problems. It should NOT have any data which is in the other tables, except the serial#. The common data is all available via the serial# relationship already. And really, it's just a log.

You're getting there, but you need to understand what a relationship is. It will save you lots of work.

Personally I would see another table. In Armory I would have only the actual "types" of guns, the specific model as a record, with common data, such as manufacturor and data about that "gun as a product, not as a specific unique object." Because you'd likely have several guns of one model, at least over time, and this would be the best "overall" view.

I'd have another table, Guns, which would have the specific gun as a record, with its unique serial. These would be related on the model. The guns would appear in a portal. Further processing would use the serial#. But data would be accessible via relationships up and down the line.