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.
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 ¢.
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline


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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now