Buttons/Check Boxes with Multiple Selections

[Guest]

I want to create an input form where a user can select multiple items via
abutton or check box. I'm developing a financial transaction database. I
have a Products table which includes fields such as ProductID (key field),
Category, Product, ShortName. I have another table for Customers and one of
the fields is CustomerID (key field). Another table is for Transactions. In
it I have a TransactionID field (key), the ProductID and CustomerID. The
result I want from the above described input form is to have records in the
Transaction table that include each product selected for the customer
(ProductID associated to the CustomerID). I hope I've described this
clearly. I initially created a form with combo boxes, but the actual user of
the database preferred entry the above way. Is this possible? Warning, I am
not a programmer. Self-taught with no programming knowledge; I use only the
tools within Access to develop databases. Any help is greatly appreciated.

 

[Nikos Yannacopoulos]

Tracey,

If I'm reading this right, you are using one flat table for
transactions, and when a customer purchases multiple items in a single
transactions, you create one record per item in the table? If so, if
each record has a different trabsaction ID (PK), how can you group all
records on a transaction together?
Unless I have interpreted this all wrong, your design is not correct;
you should have two tables, a transaction header one and a transaction
item line one, with a one-to-many relationship on Transaction ID. Have a
look at the Northwind example which ships with Access, it's probably
very close to what you need.

HTH,
Nikos

 

[Guest]

Thanks Nikos. I've reviewed the Northwinds database and I don't think it
quite captures what I need to do – or I’m totally not following your
suggestion. I am going to try to further explain my database objective since
you indicate my structure may be incorrect. This database is a financial
reporting tool that tracks orders, sales and adjustment transactions. We
sell software that is sold in modules to our customers (customers are in a
Customers table). In pricing our software, we break it down by module and
other costs areas (identified in a Products table). When a Customer
purchases our software, they do so at varying times and at each time, the
order gets assigned a program number (Programs table). Progressively, as
integration of the software occurs, we generate financial transactions
against the products within the program (Transactions table). Data entry
occurs as follows: 1) Upon receipt of an order, we create a program number,
2) We then post the “order†transactions for each product. 2) The next data
entry phase would be entering “sales†transactions against these same
products of the program. 3) Data entry of “adjustments†occur later against
the same products of the program.

The above method is how I am proceeding. By doing it this way, I don't
believe my initial question below about the input form is possible. I
currently have the transaction input form set up with a combo box that picks
the program number with a subform in datasheet view for transactions by
product to be entered. On the subform, I have combo boxes for the
transaction type (order/sale/adjustment) , product type (software module or
cost area), price, etc. It was requested the input form be layed out with
each product listed (about 30) with a check box and then a price field next
to it (price is where the order, sale or adjustment would be input). They
would check the product and input the price.

Maybe this is too much of a question for this venue ... but any input is
certainly appreciated. I'm having a great learning opportunity here. Just
from reading the various posts, I have already learned a lot in a few days!

Tracey

 

[Nikos Yannacopoulos]

Tracey,

I have to admit I had to read through your post repeatedly to grasp the
model, but I think I'm getting the picture now. The main source of my
confusion was the terminology, mostly the "program" part. I then came to
realize this is more like selling a software package (program) made up
of modules, implemented in phases, right? so the "program number"
actually represents a contract? Hopefully I'm not way off this time.

By the way, when I mentioned the Northwind example I didn't mean you
should be using that, I mentioned it as a example of how to handle a
document (order/invoice) with a header and line items, with a
one-to-many relationship between them... I assumed that a transaction
could well include several modules (or whatever products), in which case
it is a direct analogy to the header/line items scheme. Now I realize a
transaction in your model will only ever be made up of a single product?

The program-to-transactions, on the other hand, is definitely a
one-to-many relationship, by your description, but the invoice header /
line item analogy can be confusing here, in that this is not a single
transaction (in the brad sense, not your terminology); the table
structure to handle it is similar, though.

Now, how you handle the form, is a different ball game: if the modules
to be sold to a customer within the scope of a given "program" are known
at the time of the first entry, when the program number is created, then
it makes sense to create a transaction record for each at that time, so
you have a visual representation of the project in one form (program)
with a subform (transactions). You can create the records manually, if
each program is unique, or you can do that automatically if they are
always the same for a given software product. There is quite a lot you
could do to automate the process, but I can't be specific without
knowing your data structure. The creation of the records alone could be
done without any actual programming, but with some code you could do
much more.

HTH,
Nikos

 

[Guest]

Thanks again for your response! Appreciate it.