Insert select query result into a table?

Posted on 2008-10-30
Last Modified: 2011-10-19
I'm making a form for speeding up the process of creating commercial invoices.  I attached screenshots for visualization.

The invoice entry form has an underlying table and each line item has it's own field.  (IE: Line Item 1, Line Item 2, etc).  My goal is that when you click the pencil button next to the corresponding line item, it will pull up another form (which is based on a select query) that allows the user to enter a SKU number and the description of the product is pre-populated.  From there I want the user to be able to click "Add Line Item" to add the product details into the appropriate field (ie: Line Item 1)

How can I achieve this?

I barely know anything about coding so please be as descriptive and simple as possible.

Question by:paintb4707
  • 4
  • 4
LVL 44

Expert Comment

ID: 22840792
It would serve you well to examine the Invoice forms associated with the sample mdb that came with you copy of Access as well as the associated tables.  The mdb is named Northwind.mdb and is normally located in:

C:\Program Files\Microsoft Office\Office\Samples\

for Access 2000.  For Access 2003 swap Office for Office11


Author Comment

ID: 22840847
Just to further explain my question, I basically want to know how to code the "Add Line Item" button so that it adds the description of the product into it's appropriate field in the invoice table.  The select query already works as intended and the user is able to input a SKU to populate the Description field but I don't know how to take this result and insert it into a table.

Author Comment

ID: 22841291
I looked at the Northwind database.  I think it's a nice idea using a drop-down but I feel that it would be too time consuming.  There are THOUSANDS of products that we manufacture and it would just be much faster if you could enter the SKU number instead.
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

LVL 44

Expert Comment

ID: 22842957
You do have a table tblInvoices and another table tblLineItems.  Each record in tblLineItems carries a foreign key from tblInvoices - correct?  That way you could have an invoice with one line item or a hundred - it would not matter.  Are we on the same page?   If not, I would recommend you have another look at Northwind.

As to the reason for the dropdown, you could limit the SKU's to the dropdown, and virtually eliminate typo's.  With auto complete on, you would start to type the name, and very shortly you would narrow it down to a few from which you would click on the correct choice.  A SKU should never be typed except when entering new items.  Just my 5 ¢.

Author Comment

ID: 22843741
I'm only a beginner in Access, picking it up as I go along so I don't really follow what you're saying.  I'm looking at the Northwind database and I don't really see how the line items correspond to each invoice record.  I don't really understand how you can create an invoice and say these items will be listed for this record and another set of items will be listed for the next record.

I'm not trying to make some huge complicated database.  I think it would just be easier for me if I could accomplish my original goal, by taking the results from the select query and importing it into a line item field from the invoice table.
LVL 44

Accepted Solution

GRayL earned 350 total points
ID: 22844109
There is one Invoice record and in another table, InvoiceDetails, there may be several LineItem records, one for each line item.

InvoiceNo - primary key, may be autonumber
CustomerID - foreign key from Customers table
InvoiceDate - date time

DetailID - autonumber
InvoiceNo - foreign key from Invoices - this is where the invoice number gets repeated

In Northwind, the two tables are Orders and OrderDetails.  For some strange reason there is no DetailID field.  If you click on the Order field in OrderDetails, and sort it ascending, you will see several records containing the same order number.  Within each group, the order is the Item Number.  You do not need to store that number,  your form/report can be coded to generate it automatically.  If you open the form Orders and print the first record, you will see the three items in that order.  In this case you see the ProductID.    Some companies insist on seeing something called an Item Number, and if that is your case, sobeit.  Do an Inner Join on the invoice number in these two tables and immediately you have all the data needed for your invoice.

This is all part of what is know as a One-Many relationship.  This is fundamental to creating an Invoice (0ne) with several line items (Many).  As a beginner, you would do your self a service to spend several hours/days poring over the various features of Northwind.  It is a sound design, has been with the Access family of releases since virtually day one with very little change.  

Author Comment

ID: 22853715
Thank you.  I figured out how that works and made a separate table for the line items then insert a subform for it.

I have two questions now.

One being... (as of right now) I have tblProducts with SKU and Description fields.  Now I made the Description field of tblLineItems to be a lookup of tblProducts.  SKU being the first column and Description being the second.  I made it so that it stores the Description (column 2) but when I actually go in and choose a product it inserts the SKU and not the Description like it's supposed to.  Why is this?

My second question is... I'm waiting for a more complete list of products to add to tblProducts.  The complete list will include Total KG and Price per 1000.  How can I make it so that when you choose a product in the Description field it populates the appropriate KG and Price?

Thanks in advance
LVL 44

Expert Comment

ID: 22882581
Why would you want to store the description field in tblLineItems when it is already in tblProducts?  A no-no in db design.

I think you need another table tblProductsPkgs in which your products are broken down into packages.  Here is where you need the SKU, PgkID, TotKg, and PricePerK

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question