Solved

Access Append New Items Query

Posted on 2016-09-19
7
25 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 34

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 34

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 34

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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 …

744 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

13 Experts available now in Live!

Get 1:1 Help Now