Solved

Access Append New Items Query

Posted on 2016-09-19
7
41 Views
Last Modified: 2016-11-03
I have the attached access database and have 2 tables.

tbl_import -  is all new information directly from companies.
tbl2 - is the table i need to append the import to.

Is there a way to add criteria to my qry_app to make it so it checks for the vendor name and record type not equal to "Direct"? For example, i want all the items in tbl_import to go to table 2 since the record type is "Direct". If it were "AP", company 1 & 5 would not be appended.

I cannot add a column to my import.

Kelly
Database71.accdb
0
Comment
Question by:kwarden13
  • 3
  • 3
7 Comments
 
LVL 35

Expert Comment

by:PatHartman
ID: 41805325
Your example as defined doesn't make any sense.  For starters, your terminology is inconsistent.  A customer is not necessarily a vendor.  The two are not interchangeable and there is no Record type in the test with a value of "direct" so that is also confusing.

1. How do you determine the record type when you are adding a new record to tbl2?  Running the query appends a new row with the record type being empty.
2. Joining two tables on two non-key fields will result in duplicaterows being returned.  The only reason the query is not doing that now is because you are using a left join and checking for a null value.  If you remove that condition in order to satisfy your request, the results will increase exponentially as you run the append query multiple times.
0
 

Author Comment

by:kwarden13
ID: 41805678
I was trying to build a quick example so its not perfect. Anything in the import table assume is "Direct". Essentially I need a query to check if the combination of vendor name and record type is unique. I have a situation where multiple vendors have the same name but different record types.

FYI, do not jump down my throat about vendor id or creating a key. My actual data has one. This is just an example.
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 41805702
Examples need to reflect real life.  What is the actual primary key of each table since that does make a difference in this example?  If the primary key is an autonumber, then you will need a unique index on the combination of vendor and rate type.

I still don't know what default you want for rate type when you insert a row.  If rate type isn't in the input, how does it get populated?  If the field is part of a compound primary key it cannot be null.  It can be null as part of a unique index but that will cause other problems so make it a required field.
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:kwarden13
ID: 41816881
Hi Pat-

I modified my example so it is easier to understand. Also, came up with a solution in the meantime. My goal is to take all the new vendors from the import_spend table and add them to the tbl_vendor.

Initially, I added them to tbl_vendor and then updated the record type and other attributes. However, in doing this, the data was not correct since to update I was using a between formula with a text field. So now in my import, I created a new field for vendor2 (contains a numeric id) and for the record type. I will run the formula prior to appending new records.

So queries are ran in this order:
qry_format_id
qupd_sp12
qupd_123
Database71.accdb
0
 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41816977
1. Column and table names should not contain embedded spaces or special characters.
2. Adding a unique index on VendorCD (I only use the ID suffix when the field is an autonuber) will allow you to simply append records from the Import table to the client table.  Duplicates will simply be rejected.

Your example is still confused but I changed the tables and queries so you can import new clients without duplicating them assuming that the vendorclientnumbers are unique.

I'm not sure why you are trying to format the numbers but I did that also.
Database71Modified.accdb
0
 

Author Comment

by:kwarden13
ID: 41816984
Thanks for looking Pat. I'm going to check it out when I get home. I was formatting the numbers because they are text when they are imported ( other data starts with letters) so I needed to format to number for the between statement to work for record type field.

Kelly
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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.

860 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