Insert select query result into a table?

Posted on 2008-10-30
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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 1400 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

764 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