Null and Not Null

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm new at Access and don't know a lot of the language yet, so please bear
with me. I have been trying to combine an Access database with an Excel
spreadsheet by importing the spreadsheet. Got that done. Then I need to put
all that information together into the one database and was told one good way
was to use the query function, since there's different fields in the Excel
info than in the Access DB. So, I think I did that, but now I'm trying to
run the query and want ALL records to show up, null and not null alike. Can
this be done? If so, how.
 
If you "imported the spreadsheet", why do you (apparently) have different
data in Access than you have in Excel? I don't understand "different
fields"...

If you check Access HELP on "query", you'll get ideas how this tool can be
used.

More specific description (what data, where?) will probably result in more
specific suggestions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
The Access data base has an numeric ID field, for example, whereas the Excel
does not; the database has far more fields than the Excel spreadsheet has
fields of info. They've been used for different things over the years.
We're trying to stop having all these mailing lists all over the place and
want to condense down into just Access. Here's an example. if you were
looking at the headings of the columns:

Access-
ID LAST NAME FIRST NAME TITLE BIZ ADDRESS STATE CITY ZIP PHONE and
many more fields to the right.

Excel-
LAST NAME FIRST NAME TITLE BIZ ADDRESS STATE CITY ZIP

Somehow I need to get the info from Excel into the Access DB and then be
able to show ALL of the info in there. I can populate things later, as
needed.
 
Still not sure I understand, but here's an idea...

Open the Access database to the tables (database window). Click File | Get
External Data | Link... and select the Excel spreadsheet that has your data.

Now you have both sets of data in one place.

Now, what do you mean by "null" and "not null"? The selection criteria
you'd use in your query or queries will determine which of the sets of data
you see.

If you are trying to match up lists in both Access and Excel, it looks like
you might not have any common fields to do that. I mean, yes, you have
LastName and FirstName, but what will you do to make sure you don't have the
wrong Smith, Bill or Jones, Bob linked up?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I had already imported the Excel sheet into the table like that before, and
could not find a way to then "merge" or combine the Excel-related table into
the Access table. So, a co-worker said I could go a back-door-way and
combine them via a query. So I did the query wizard and chose both tables
for the data to populate the query, did the "relations" thing to each area
that both tables contain. Then in the area where you "build" the query, I
pulled down each available field name into a column (so I can draw all that
info into the query) whether there was a relationship between the tables or
not. Then, to define what I wanted the query, I was trying to define that I
want ALL the information to show in the query. I don't want one set of data,
I want BOTH sets to merge together and be one complete set of data.

Geez, I hope I'm explaining it ok!
Beth
 
Beth

I'm having trouble visualizing "both sets". If you join the two tables on
their common fields, you'll get all the records from the combined tables
where there's a match.

Try this ... give an example (abbreviated) of data from the Access table,
and data from the Excel set, then show what you'd want as output, given that
input.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
In an earlier email I showed and example of the headings from the 2 sets of
data, which actually describes what those fields (columns) contain. I just
copied them below again. Note: In the Excel example you will notice that
there are far less headings.

Access-
ID LAST NAME FIRST NAME TITLE BIZ ADDRESS STATE CITY ZIP PHONE
*****and many more fields to the right of these Access headings.

Excel-
LAST NAME FIRST NAME TITLE BIZ ADDRESS STATE CITY ZIP
**** that is all of the Excel fields (columns of info).

What I want is to have the information from Excel dumped into the Access
database, and hopefully with each row Access will assign a number to that
record, which then becomes the ID #, as listed in the heading below.
 
You've (re-)described the structure, not an example of actual data.

If you append all the Excel records into the Access table, having Access
assign an ID to each (newly-added) record, where are you getting "both" sets
of data from?

If you've already imported the Excel data into an Access table, create a new
query, add the "Excel" table, select all the fields, then convert the query
to an Append query, and point to the permanent Access table as the
destination. You will need to indicate the destination field for each field
from the Excel data (although Access 'guesses' if the source and destination
tables have identical field names).

Note: for this to work, your permanent Access table would need to have an
Autonumber data type for the ID field -- that way, any newly-added record
will be assigned an ID.

And I STILL don't see the Null/Not Null angle! What field(s) contain Null,
and if all the data is in one table, what does that matter? I'm not being
facetious, I'm trying to understand what significance this holds for you, so
I can offer better suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Here's a sample of Access data as listed in the tables:
438 Doe, John Supervisor Marion Co PW 1111 OK Street Salem
OR 97000 503 xxx-xxxx X10 503-999-9999 cell 503-888-8888(fax)
djohn@stateemail and then after this are several boxes fields to the right
that we put an X in to signify a yes or no.

Here's a sample of what's in the Excel data:
Doe, John Supervisor Marion Co PW 1111 OK Street Salem OR

If I were looking at the info, each blank between the typewritten entries
above signify a separate field (column).

Both sets of data? One is an existing ACCESS database. The other is the
Excel spreadsheet that I want to assimilate into the Access DB (think
assimilate in Star Trek terms with the Borg).

You may have "hit the nail on the head" by mentioning <If you've already
imported the Excel data into an Access table, create a new query, add the
"Excel" table, select all the fields, then convert the query to an Append
query, and point to the permanent Access table as the destination. You will
need to indicate the destination field for each field from the Excel data
(although Access 'guesses' if the source and destination tables have
identical field names).> I have imported the Excel into an Access table all
by itself. I have tryed to query Excel and the Access table at the same time
by choosing where my data comes from when building the query.... that has
gotten me no where. I think I need to do as you said... just query the Excel
table by itself... but then I don't know how to convert that query into an
"append" query (I don't even know what that is) and I certainly don't have
any clue about indicating "the destination".

I warned you that I'm a fairly new user! :o)
 
Try Access HELP for "append query". It should walk you through how to
convert a select query (the kind you get by default when you create a query)
into an append query.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top