Help making a form with tables and querys

[mentose457]

I was wondering if someone could help steer me in the right direction.



I am trying to make an Firearms Acquisition and Disposition database. In this database I need to store information regarding the firearm its self, where I acquired it, and to whom I sold it. The image below is how I imagine the form looking like.



What I have so far:

I have created a table with the information in the first column, FIREARM INFO.

I have two forms, Customer Details and Supplier Details that I imported from the sample Northwind database (I've also imported the dependent tables and queries).

The questions:

How should I make the ACQUISITION and DISPOSITION columns?

I would like the NAME & ADDRESS OR NAME & LICENCE # field, in the ACQUISITION column to be a List linked to the Suppliers table I already have. That way I could select a supplier that has already entered in the Supplier Details form or edit the list and add a new one.

I hope that makes sense.

[NTC]

first figure out your data logic - in how it is going to be stored in the tables. Right now it appears that a single firearm is acquired/disposed one time. And that's it. And if that is the case - then you can use a single table approach - make a form on the table (or reuse one that you have) - and arrange it as you wish.

as a db guy though - I whether this one firearm will be acquired/disposed/acquired/disposed/etc as it is sold over and over...in which case you want to keep the history of that firearm; in which case you have a table for firearms themselves, and a table for their acquis/disp history...linked together by an ID.

If you have repeat customers, it makes sense to make a customer table - just so you don't have to re-enter all their info all the time. that is simple. same is true for manufacturer, etc. You handle these either as lookup fields in your main table - or subforms in your main form.

If you are design a db for the first time - the very first thing you should do is buy an Access textbook easily found at Amazon or a big book store and read thru it.

Hope this helps.

[mentose457]

If you have repeat customers, it makes sense to make a customer table - just so you don't have to re-enter all their info all the time. that is simple. same is true for manufacturer, etc. You handle these either as lookup fields in your main table - or subforms in your main form.

Thats what I was trying to do!