Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Append New Items Query

Posted on 2016-09-19
7
Medium Priority
?
60 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
[X]
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
  • 3
  • 3
7 Comments
 
LVL 39

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 39

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 39

Accepted Solution

by:
PatHartman earned 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

670 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